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.  

Using the sqlcmd -N switch will give you an encrypted connection , but it's not necessarily using the certificate configured on a target SQL Server. This command will  result in an entry on sys.dm_exec_connections with encrypt_option = TRUE 

sqlcmd -E -N -S server\myinstance 

 
If you want to check whether the SSL certificate is enabled - check the SQL Server Error Logs.This command will extract the relevant details.
 

EXEC master.dbo.xp_readerrorlog 0, 1, N'cert'

 
You'll see something like 

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

 

A Client machine should trust the applied certificate - the methods used are:

1-  Direct Trust - SQL Server certificate is installed on the client server

2-Chain of trust - The root certificate authority and chain certificates are trusted

 

 

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