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 :
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) :
