本文是How to find the TLS used for the SQL Server connection这篇英语文章的翻译,此文出处请见于文章底部链接:原文出处[1]
对于客户,我做了一些研究,如何找出SQL Server数据库会话连接使用了哪一种TLS协议。唯一的方式就是创建一个扩展事件,这个扩展事件有一个很大的限制就是只有SQL Server 2016或以后的版本才有。之前的版本必须使用内置诊断(BID)跟踪。
使用SQL Server 2016的查询窗口创建一个TLS监控事件之后
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);
这个扩展事件运行的很正常,我得到了一些使用TLS 1.0和TLS 1.2的会话连接结果。
之后,我想在SQL Server 2019实例上也测试验证一下,我得到了下面错误:
Msg 25623, Level 16, State 1, Line 1 The event name, “sqlsni.trace”, is invalid, or the object could not be found
经过google搜索后,我发现,在SQL Server 2019中,扩展事件sqlsni.trace已经被sqlsni.sni_trace给替换了。 我修改了我的脚本,下面是在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);
在我和客户讨论过后,他要求我在每个可用扩展事件(准确的表达应该是可以使用sqlsni.trace扩展事件)的SQL Server服务器上部署这个脚本,以便在每个SQL Server上都可以用。我编写了下面这个SQL脚本,可以通过CMS在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
我希望最后这个脚本可以帮助你找出TLS连接类型。
参考资料
原文出处: https://www.dbi-services.com/blog/how-to-find-the-tls-used-for-the-sql-server-connection/
- connection the Server find usedconnection the server find the series find sum daemon docker the connect connection following the listener connection the localhost actions payload request server using nxdomain server find dns connection the localhost refused the maven persists connect docker daemon the connect