Monitoring SQL Server Encryption With Extended Events

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.

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.

StackExchange Database Administrators Q#305966, answer score: 5