pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Tuesday, January 09, 2018

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

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.


ADD EVENT sqlsni.trace(

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

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

No comments: