query to determine forceencryption state

Question

I'm trying to confirm whether or not force encryption is currently set and/or if the current connection is secure or not.

Is there a SQL query I can run that will give me that information or do I need to log onto the machine and interrogate it from the OS?

Answer 1

To determine the setting you need to read from the registry. Replace your version/instancename in the query below:

EXEC [master].[dbo].[xp_regread]    @rootkey='HKEY_LOCAL_MACHINE',
                                    @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<version>.<instancename>\MSSQLServer\SuperSocketNetLib',
                                    @value_name='ForceEncryption'   

You should be able to get the correct path from dm_server_registry which unfortunately doesn't give me the ForceEncryption property.

SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry

To determine the current connections encryption state you can just select them from sys.dm_exec_connections

SELECT encrypt_option, *
FROM sys.dm_exec_connections

Answer 2

To follow on from Tom V: To determine the setting you need to read from the registry without having to insert your version/instancename in a query use:

    EXEC [master].[dbo].[xp_instance_regread]
       @rootkey = 'HKEY_LOCAL_MACHINE',
       @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
       @value_name = 'ForceEncryption';

xp_instance_regread takes the generic key and transforms it for the current instance.