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:

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 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.

 

Post Views: 12,334
Thumbnail [60x60]
by
Stéphane Haby