Skip to content

How to find the TLS used for the SQL Server connection

For a customer, I do some research to find which TLS is used on the SQL Server environment.

The only way is to create an Extended Event.

A big limitation is that the event used is only available on SQL Server 2016 and +.

Before use the Built-In Diagnostics (BID) traces.

After I implanted the first TLS Monitoring on  a SQL Server 2016 with the query:

CREATE EVENT SESSION [TLS_monitoring] 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](,N'%TLS%')))

             ADD TARGET package0.event_file(SET filename=N'TLS_Monitoring')

             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);

It’s running fine and I have the first result with some TLS 1.0 and TLS1.2 connections.

After that, I want to test also on SQL Server 2019 Instance, I got this error:

Msg 25623, Level 16, State 1, Line 1
The event name, “sqlsni.trace”, is invalid, or the object could not be found

After a google search session, I find that the sqlsni.trace is replace by sqlsni.sni_trace

I replace my query by this one for SQL Server 2019:

CREATE EVENT SESSION [TLS_monitoring] 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](,N'%TLS%')))

             ADD TARGET package0.event_file(SET filename=N'TLS_Monitoring')

             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);

 

After discussed with my customer, he asks me to implement it on each SQL Server available for the Extended Event.

I create this query below to go through the CMS (central management server) and install the good version of the sni layer and only after SQL Server 2014:

-- Create SQL Server extended event to monitor TLS

-- Before SQL 2016, the Trace extended event is not implemented for the SNI layer. For SQL Server 2014 or 2012, you must use Built-In Diagnostics (BID) traces

IF (( CAST(SERVERPROPERTY('ProductMajorVersion') AS INT)  < 15) AND (CAST(SERVERPROPERTY('ProductMajorVersion') AS INT)  > 13))

       BEGIN

 -- Before SQL Server 2019 and after SQL Server 2016

             CREATE EVENT SESSION [TLS_monitoring] ON SERVER

             ADD EVENT sqlsni.trace(

             WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') AND [sqlserver].[like_i_sql_unicode_string](,N'%TLS%')))

             ADD TARGET package0.event_file(SET filename=N'TLS_Monitoring')

             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);

       END

ELSE

       BEGIN

 -- SQL Server 2019 and more

      CREATE EVENT SESSION [TLS_monitoring] 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](,N'%TLS%')))

             ADD TARGET package0.event_file(SET filename=N'TLS_Monitoring')

             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);

       END




ALTER EVENT SESSION [TLS_monitoring] ON SERVER  STATE = START ;

GO

I hope that this last script will help you to see the TLS connection type.

 

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Stéphane Haby

Stéphane Haby

Stéphane Haby has more than 20 years of experience in Microsoft solutions, specializing in the installation, migration, best practices, and performance analysis of Microsoft SQL Server, as well as Microsoft Business Intelligence solutions (SharePoint, Office) and .NET development for the banking sector. A former Microsoft Most Valuable Professional (MVP) and early adopter of Microsoft Team System in France, he has authored several technical articles. He holds Microsoft Certified Solutions Associate (MCSA) and Expert (MCSE) for SQL Server, MCTS, MCITP for SQL Server 2008, and ITIL V3 Foundation certifications. His industry experience covers chemicals and pharma, banking, and financial services.View Author posts

182 posts since April 2011

2 thoughts on “How to find the TLS used for the SQL Server connection”

  1. Stéphane Haby:-

    Please correct:-

    from
    ====
    IF (( CAST(SERVERPROPERTY(‘ProductMajorVersion’) AS INT) 13))

    BEGIN

    — Before SQL Server 2019 and after SQL Server 2016

    to
    ==

    IF (( CAST(SERVERPROPERTY(‘ProductMajorVersion’) AS INT) = 13))

    BEGIN

    — Before SQL Server 2019 and Starting from SQL Server 2016

  2. Stéphane Haby:-

    Please correct

    from:-

    WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],’Ssl::Handshake’) AND [sqlserver].[like_i_sql_unicode_string](,N’%TLS%’)))

    to:-

    WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],’Ssl::Handshake’) AND [sqlserver].[like_i_sql_unicode_string]([text],N’%TLS%’)))

    BTW, the attribute name is missing in both the v2106 and v2019 queries.

Leave a Comment

Your email address will not be published. Required fields are marked *