6

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?

4

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
  • I like the answer, however, when I executed it I got a number of errors. I seem to need elevated permissions. – Richard Jul 7 '15 at 15:35
  • @Richard what errors are you getting? – Tom V Jul 7 '15 at 18:20
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.