Checking Whether Connections to SQL Server are Encrypted

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




Introduction

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 :
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-2017

Encryption

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
SELECT
  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]
 ,CASE WHEN
    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 : https://www.sqlmatters.com/Articles/Checking Whether Connections to SQL Server are Encrypted.aspx

Keywords

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


Comments

Post a comment   No login required !

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