E
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 https://dba.stackexchange.com/a/189626/10267 by https://dba.stackexchange.com/users/44434/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 https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver15 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.