是否有DMV可查询当前数据库连接的证书类型及实际使用情况

SQL Server没有直接的动态管理视图（DMV）可以直接查询每个当前连接所使用的证书类型，但可以通过以下几种方法实现你的目标：

1. 使用扩展事件（Extended Events）捕获TLS握手细节 #

扩展事件的tls_handshake_completed事件会记录TLS连接的关键信息，包括证书的哈希算法、指纹等，能精准识别每个连接使用的证书。

创建事件会话 #

CREATE EVENT SESSION [Track_TLS_Cert_Usage] ON SERVER 
ADD EVENT sqlsni.tls_handshake_completed(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id)
    WHERE (sqlserver.session_id > 50) -- 排除系统内部会话
)
ADD TARGET package0.event_file(SET filename=N'C:\SQLLogs\Track_TLS_Certs.xel') -- 指定存储路径
WITH (STARTUP_STATE=ON); -- 服务重启后自动启动会话

启动会话并查询数据 #

启动会话：

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

查询捕获的证书信息：

SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS HandshakeTime,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(100)') AS ClientApp,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS ClientHost,
    event_data.value('(event/data[@name="tls_version"]/value)[1]', 'varchar(50)') AS TLSVersion,
    event_data.value('(event/data[@name="cert_hash_algorithm"]/value)[1]', 'varchar(50)') AS CertHashAlgorithm,
    event_data.value('(event/data[@name="cert_fingerprint"]/value)[1]', 'varchar(200)') AS CertFingerprint
FROM 
    (SELECT CAST(event_data AS XML) AS event_data
     FROM sys.fn_xe_file_target_read_file('C:\SQLLogs\Track_TLS_Certs*.xel', NULL, NULL, NULL)) AS xe_data;

通过CertHashAlgorithm可以判断是否为SHA1，CertFingerprint可与你部署的外部证书指纹对比，确认连接是否使用了正确的证书。

2. 从SQL Server错误日志追溯证书使用 #

当SQL Server建立TLS连接时，会在错误日志中记录证书的指纹和哈希算法。可以通过以下命令查询：

EXEC sys.xp_readerrorlog 0, 1, 'Certificate used', 'SHA1';

该方式适合追溯历史连接记录，但实时性不如扩展事件。

3. 强制SQL Server使用指定证书（根源解决） #

直接在SQL Server配置管理器中指定服务仅使用你部署的外部签名证书，禁用自动生成的SHA1自签名证书：

• 打开SQL Server配置管理器，找到目标SQL Server服务，右键选择「属性」。
• 切换到「证书」选项卡，选中你部署的外部证书，点击「确定」。
• 重启SQL Server服务使配置生效。

配置完成后，使用SHA1自签名证书的连接会失败，你可以通过错误日志或扩展事件捕获这些失败请求，进而定位未使用正确证书的应用。

内容的提问来源于stack exchange，提问作者Owen McGee