4

Is there a way to monitor for encrypted connections through Extended Events? I've monitored for TLS 1.0, 1.1 and 1.2 handshakes in the past using sqlsni.TRACE but cannot find something along the same lines for encrypted connections themselves. Unless "encrypt=yes" is not added to the connection string only the handshake will be encrypted and nothing else.

I can use sys.dm_exec_connections to capture this but it is not real time and I'd have to run it very frequently (every minute or less) to find what I'm looking for.

Improve this question

1 Answer 1

Reset to default
5

Yes, this can be done using the below trace event,

" You can find more details of the actual negotiated TLS protocol and cypher from this Extended Event trace but this requires SQL Server 2016 SP1 or later:"

CREATE EVENT SESSION [tls] ON SERVER 
ADD EVENT sqlsni.trace(
    WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') 
AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%')))
ADD TARGET package0.event_file(SET filename=N'tls_trace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

" The original answer is here by Dan Guzman

Also note, if this needs to be done in 2019 change the event to sqlsni.sni_trace

and the code will be like

CREATE EVENT SESSION [tls_new] ON SERVER 
ADD EVENT sqlsni.sni_trace(
    WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') 
AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%')))
ADD TARGET package0.event_file(SET filename=N'tls_trace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

EDIT After the OP's comment, the requirement is to find the connection is encrypted or not and not TLS.

I don't know an option in Extended event for this, but to acheive this you can leverage logon triggers in SQL server.

Below is an attempt to capture the sys.dm_exec_connections after each login.

DROP TABLE IF EXISTS test.dbo.tmp_conn
SELECT * INTO test.dbo.tmp_conn FROM sys.dm_exec_connections dec
WHERE 1=0
GO
CREATE TRIGGER connection_encrypt
ON ALL SERVER WITH EXECUTE AS N'sa'  
FOR LOGON  
AS  
BEGIN  
INSERT INTO test.dbo.tmp_conn
SELECT * FROM sys.dm_exec_connections dec
WHERE dec.session_id=@@spid
END;

Please change the code as per your need for the audit.

Improve this answer
4
  • Hi @Biju, I've used this before while tracking down TLS 1.0 and 1.1 connections during a TLS 1.2 upgrade. The handshake is always encrypted, so this will tell me that the handshake is encrypted using TLS 1.x, It will not tell me if the remainder of the connection (e.g., post handshake) is encrypted by ";encrypt=yes" in the connection string.
    – PseudoToad
    Commented Jan 12, 2022 at 12:35
  • 1
    @PseudoToad, aah gotcha, not sure about an option in Extended event is there, but will a logon trigger which capture sys.dm_exec_connections and stores it to a table will help you?
    – Biju jose
    Commented Jan 12, 2022 at 13:53
  • @Biju....that is a great idea...I'll test that out!
    – PseudoToad
    Commented Jan 12, 2022 at 15:31
  • @BIju...thanks! This did the trick.
    – PseudoToad
    Commented Jan 13, 2022 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.