How to check a SQL Server connection is encrypted with SSL

01 June,2021 by Tom Collins

Question: I'm completing some security reviews to develop a profile of the SQL Server environment. I want to  assess the amount of encrypted connections active on SQL Servers & specifically if a connection is using an SSL certificate

 

Answer:According to the Microsoft documentation for sys.dm_exec_connections  encrypt_option  definition is : "Boolean value to describe whether encryption is enabled for this connection".

This example query extracts the current connections and identifying - via the encrypt_option column whether a secured connection is used.   It won't tell you which cipher suite or protocol is used

SELECT session_id, encrypt_option FROM sys.dm_exec_connections
 

Using the encrypt_option value for a connection will give you the VALUE of 1 or 0 - checking if the connection is encrypted,

 
If you want to check whether the SSL certificate is enabled - check the SQL Server Error Logs. You'll see something like 

The certificate [Cert Hash(sha1) "A2374234234AXXXXXXvNMLP0987645"  was successfully loaded for encryption.

 

SSL_sql_server


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to check a SQL Server connection is encrypted with SSL


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer