patternsqlMinor

query to determine forceencryption state

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

queryforceencryptionstatedetermine

Problem

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?

Solution

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.\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

Code Snippets

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

SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry

SELECT encrypt_option, *
FROM sys.dm_exec_connections

Context

StackExchange Database Administrators Q#106061, answer score: 6

Revisions (0)

No revisions yet.