Checking Whether Connections to SQL Server are Encrypted

Category : Tips Published : September 11, 2018 User Rating : 4.5 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.


This article outlines how to check whether connections to a SQL Server instance are encrypted. Ideally all connections should be encrypted (using TLS/SSL), so that data transfers between a SQL Server instance and a client application are secure. However sometimes this isn’t possible or hasn’t been set up (a default installation of SQL Server will not normally include connection encryption). This article outlines some queries that can be run to check which current connections are encrypted.

If you don't have connection encryption and want to set it up on your instance then Microsoft have documented the process here :


For all new SQL Server instances our cyber security team has a policy that all data traffic to and from the server should be encrypted, with encryption forced so that all connections use TLS/SSL (TLS is a more recent and more secure version of SSL). Obviously this is a sensible precaution for data security purposes, especially as (for us) all our data centres are cloud based. However, for older applications there’s a practical consideration that not all our applications support encrypted connections. In practice that means we can’t force encryption on legacy instances, so we often have a mixture of encrypted and unencrypted connections on these servers.

Eventually we expect to force encryption on all connections, but whilst we still have older applications we have a mixture of encrypted and unencrypted connections and we regularly monitor how many unencrypted connections we have. To keep track of this, we grade each SQL instance with a RAG status (red, amber or green) as follows :

RED :No connections are encrypted.
AMBER :Some connections are encrypted (usually because a certificate has been installed, but
‘Force Encryption’ has not been set in SQL Server Network Configuration).
GREEN :All connections are encrypted (usually because a certificate has been installed and
‘Force Encryption’ has been set in SQL Server Network Configuration).

Checking Whether Connections are Encrypted

Although sp_who2 doesn’t include information about whether each connection is encrypted, there is a DMV that does do this. The DMV is sys.dm_exec_connections, this has a row for each connection to the instance, and the column encrypt_option is a boolean value which describes whether the connection is encrypted. The following query will list all connections and whether they are encrypted :
-- list connections and encryption status
SELECT  session_id,encrypt_option
FROM    sys.dm_exec_connections
This query can then be extended to list the number of encrypted and the number of unencrypted connections :
-- Summarise number of connections of each type
  SUM(CASE WHEN encrypt_option='FALSE' THEN 1 ELSE 0 END) AS [Number of unencrypted connections]
 ,SUM(CASE WHEN encrypt_option='TRUE' THEN 1 ELSE 0 END) AS [Number of encrypted connections]
FROM   sys.dm_exec_connections
I ran this on one of my production servers and got the following result :
SQL Encryption Number of Encrypted Connections
Finally I’ve adapted the query again to give me the RAG (Red, Amber, Green) status that I mentioned above :
-- Get RAG status for encrypted connections
;WITH CTE_Summary AS
SELECT    encrypt_option
         ,COUNT(*) cnt
FROM      sys.dm_exec_connections
GROUP BY  encrypt_option
SELECT @@ServerName AS [Server Name]
    COUNT(*)=1 AND MIN(encrypt_option) = 'FALSE' THEN 'RED - no connections are encrypted'
    WHEN COUNT(*)=1 AND MIN(encrypt_option) = 'TRUE' THEN 'GREEN - all connections are encrypted'
    ELSE 'AMBER - some connections are encrypted'
  END AS [Connection Encryption RAG Status]
FROM  CTE_Summary
When I run that against the same production server as above I get the result (as I have a mixture of encrypted and unencrypted connections) :
SQL Encryption RAG Status
Link back to this article : Whether Connections to SQL Server are Encrypted.aspx


SQL 2017, 2016, 2014, AWS, RDS, Encryption, Security, Certificate, SSL, TLS

Post by Steve Lipson on Wed 13 Mar 2019 14:39. Report Inappropriate Post

I'm using SQL 2017 and i noticed in the properties on my current connection it shows the connection encryption is not encrypted but I have force encryption on and all of the scripts listed in this article show that all of my connections are encrypted. Can you explain why the properties show this connection is not. If I explicitly chick the encrypt connection option in SSMS then it does show encrypted in the parameter section.
Post by AWS training in hyderabad on Mon 23 Nov 2020 07:13. Report Inappropriate Post
Website :
The information provided on the site is informative. Looking forward more such blogs. Thanks for sharing.
Post by AWS training in hyderabad on Wed 10 Feb 2021 09:09. Report Inappropriate Post
Website :
Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
Post by dbainkc on Mon 21 Jun 2021 16:47. Report Inappropriate Post

I have a Always On Group, and the Database Mirroring connections show 'encrypt_option' = false (even though its set to force encryption). Any thoughts?
Post by Prem on Thu 12 May 2022 01:35. Report Inappropriate Post
Website :

Did you notice any performance issues once TLS encryption was enabled? Did your team have to make any updates at a hardware or software level for it function adequately?


Post a comment   No login required !

Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered