pointers, solutions and toolbox 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.

CREATE EVENT SESSION [tls] ON SERVER

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.

9 comments:

Ray said...

Wish there was a method for those of us stuck with SQL 2008R2 :(

Володимир Лихачов said...

The event name, "sqlsni.trace", is invalid, or the object could not be found.
It could be that event name was renamed. Do you know new event name?

w said...

Володимир Лихачов-
Confirmed it works in SQL 2017. What version of SQL Server are you using?

Володимир Лихачов said...

I use SQL 2016 and 2014 with latest updates

Володимир Лихачов said...

Somehow I added session with trace event and here is script for it:
CREATE EVENT SESSION [tls] ON SERVER
ADD EVENT sqlsni.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)
GO

w said...

Interesting. I've also confirmed it on SQL 2016 SP1 that it works.

Glad you got it working. That session is on the right event but is missing the filter. Is the WHERE clause of the CREATE EVENT statement the issue for you?

Володимир Лихачов said...

Problem in syntax. There is extra new line symbol after opening parenthesis in copy-paste from your SQL section, so code should look like
CREATE EVENT SESSION [tls] ON SERVER
ADD EVENT sqlsni.trace(WHERE ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%')))

w said...

It must be a character encoding issue with the copy/paste from the browser perhaps. I've seen odd linebreak/carriagereturn characters cause weird issues before. Introducing line breaks in SSMS in any combination before/after the parenthesis or keywords won't make a difference. Glad we figured it out, and thanks for sharing for others to reference in the future!

-William

Anonymous said...

2014 latest patches, sqlsni.trace not valid and not in drop drops for "trace" how did you fix?