SQL Server on TLS 1.2: XEvent session to catch TLS in use

How can you tell what version of TLS is currently used for client connections? Unfortunately there isn't a handy queryable column in sys.dm_exec_connections, which would be ideal, though there is a Connect item requesting this there is a feedback item requesting this.

For now, you can detect the TLS version with an Extended Events session starting with Service Pack 1 for SQL Server 2016 and Service Pack 4 for SQL Server 2012. You'll see the event "trace" in the channel Debug, which returns information from the SQL Network Interface (SNI) layer. (Note that the Debug Channel is not checked/displayed by default in the SSMS Extended Events search dialogue.)

See example below from SSMS.

Start your new XEvents session, then look for captured SSL Handshake events (function_name = Ssl:Handshake), which will contain the protocol, cipher, cipher strength, hash algorithm, and hash strength.

See example below.

Unfortunately, this event does not capture any other information about the login except for the Peer IP Address at the end of the "text" field, what you see in the screenshot above. Application and client information hasn't been exchanged yet with the SQL Server, so other fields are not populated. You can filter out the other SQL Network Interface (SNI) noise with a filter on your Extended Events session, to find successful and failed handshakes and their protocol.

See sample TSQL script to create the session below.

CREATE EVENT SESSION [tls] ON SERVER

ADD EVENT sqlsni.trace(

    WHERE ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%')))

You could for example set up a lightweight xevent session to look for TLS 1.0 and TLS 1.1 activity:

CREATE EVENT SESSION [tls] ON SERVER
ADD EVENT sqlsni.trace(
    WHERE (([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.0%'))
 OR ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.1%'))
--OR ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.2%'))
))
ALTER EVENT SESSION [tls] ON SERVER
ADD TARGET package0.ring_buffer(SET max_events_limit=(100000),max_memory=(10240))
 WITH (MAX_MEMORY=10240 KB,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION tls ON SERVER STATE = START;

What should I do to get my SQL Server only using TLS 1.2, and why? See my companion blog post on this topic.