HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How can I check if connection to Sql Server is encrypted?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cansqlencryptedhowservercheckconnection

Problem

In SQL Server I can "Force Encryption" in protocols settings. If I leave that set to "No" any client can still require encryption.

Can I somehow see what connections to the database are using encryption?

Solution

From TechNet sys.dm_exec_connections DMV: the encrypt_option will display FALSE if the connection is not encrypted.

Here is an example TSQL statement that can be run by administrators (VIEW SERVER STATE is the required privilege):

SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address 
FROM sys.dm_exec_connections

Code Snippets

SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address 
FROM sys.dm_exec_connections

Context

StackExchange Database Administrators Q#117873, answer score: 24

Revisions (0)

No revisions yet.