注意
本文适用于熟悉获取和读取网络跟踪的人员。
现象
当客户端无法验证服务器证书时，客户端应用程序中会显示以下错误消息：
System.Data.SqlClient.SqlException:
A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
---> System.ComponentModel.Win32Exception: The certificate chain was issued by an authority that is not trusted
网络跟踪示例
Frame Date and Time Source IP Dest IP Description
----- ------------------- ----------- ----------- -----------------------------------------------------------------------------------
590 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=CE....S., SrcPort=56277, DstPort=1433, PayloadLen=0,
593 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=.E.A..S., SrcPort=1433, DstPort=56277, PayloadLen=0,
596 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=...A...., SrcPort=56277, DstPort=1433, PayloadLen=0,
599 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TDS:Prelogin, Flags=...AP..., SrcPort=56277, DstPort=1433, PayloadLen=104,
602 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TDS:Response
605 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TLS:TLS Rec Layer-1 HandShake: Client Hello.
614 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=...A...., SrcPort=1433, DstPort=56277, PayloadLen=0,
617 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate. Server Key Exchange. Server Hello Done.
686 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=...A...F, SrcPort=56277, DstPort=1433, PayloadLen=0,
710 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=...A...., SrcPort=1433, DstPort=56277, PayloadLen=0,
713 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=...A...F, SrcPort=1433, DstPort=56277, PayloadLen=0,
719 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=...A...., SrcPort=56277, DstPort=1433, PayloadLen=0,
包含注释的网络跟踪示例
Frame Date and Time Source IP Dest IP Description
----- ------------------- ----------- ----------- -----------------------------------------------------------------------------------
TCP 3-way handshake establishes a basic TCP connection.
590 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=CE....S., SrcPort=56277, DstPort=1433, PayloadLen=0,
593 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=.E.A..S., SrcPort=1433, DstPort=56277, PayloadLen=0,
596 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=...A...., SrcPort=56277, DstPort=1433, PayloadLen=0,
The PreLogin packet from the client indicates data encryption is required. This also implies the client will try to validate the certificate.
599 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TDS:Prelogin, Flags=...AP..., SrcPort=56277, DstPort=1433, PayloadLen=104,
- Tds: Prelogin
+ PacketHeader: SPID = 0, Size = 104, PacketID = 1, Window = 0
- PreLoginPacketData:
- PreloginOptions:
+ PreloginOptionTokens:
- PreloginOptionData:
+ VersionData:
- EncryptionData:
Encryption: ENCRYPT_ON 1 (0x1) <----- Data Encryption is Requested by the client
+ InstOptData:
+ ThreadIDData:
+ MARSData:
+ TRACEIDLengthData:
+ FederatedLengthData:
602 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TDS:Response
The SSL/TLS handshake results in the server sending a certificate to the client.
For data encryption, the client tries to validate the certificate after receiving frame 617 (Server Hello).
605 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TLS:TLS Rec Layer-1 HandShake: Client Hello.
614 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=...A...., SrcPort=1433, DstPort=56277, PayloadLen=0,
617 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate. Server Key Exchange. Server Hello Done.
- TLS: TLS Rec Layer-1 HandShake: Server Hello. Certificate. Server Key Exchange. Server Hello Done.
- TlsRecordLayer: TLS Rec Layer-1 HandShake:
ContentType: HandShake:
+ Version: TLS 1.2
Length: 847 (0x34F)
- SSLHandshake: SSL HandShake Server Hello Done(0x0E)
HandShakeType: ServerHello(0x02)
Length: 81 (0x51)
+ ServerHello: 0x1
HandShakeType: Certificate(0x0B)
Length: 517 (0x205)
- Cert: 0x1
CertLength: 514 (0x202)
- Certificates:
CertificateLength: 511 (0x1FF)
+ X509Cert: Issuer: SSL_Self_Signed_Fallback, Subject: SSL_Self_Signed_Fallback <---- this is SQL Server's Self-Generated Certificate
HandShakeType: Server Key Exchange(0x0C)
Length: 233 (0xE9)
ServerKeyExchange: Binary Large Object (233 Bytes)
HandShakeType: Server Hello Done(0x0E)
Length: 0 (0x0)
The certificate is an SSL_Self_Signed_Fallback certificate, which means that it cannot be validated.
The client terminates the connection.
686 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=...A...F, SrcPort=56277, DstPort=1433, PayloadLen=0,
710 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=...A...., SrcPort=1433, DstPort=56277, PayloadLen=0,
713 8:34:51 AM 3/9/2022 10.10.10.20 10.10.10.10 TCP:Flags=...A...F, SrcPort=1433, DstPort=56277, PayloadLen=0,
719 8:34:51 AM 3/9/2022 10.10.10.10 10.10.10.20 TCP:Flags=...A...., SrcPort=56277, DstPort=1433, PayloadLen=0,
注意
此示例显示使用自生成证书的 SQL Server，但如果客户端出于某种原因不信任该证书，则任何证书都可以位于 Server Hello 数据包中。
解释
检查网络跟踪，了解客户端和服务器之间的通信流。
- 帧 590 - 599：TCP 握手和 PreLogin 数据包，指示需要数据加密。
- 帧 605 - 617：TLS 握手与服务器 Hello 发送服务器证书。
- 帧 686 - 719：由于证书验证失败而终止连接。
客户端请求数据加密，这会触发服务器证书的验证。 但是，服务器通过发送自签名证书（SSL_Self_Signed_Fallback）进行响应，这会导致验证失败。
当客户端请求数据加密（Encrypt=yes 或）或 Use Encryption for Data=True服务器需要数据加密时（Force Encryption=Yes 仅适用于较新的驱动程序），客户端驱动程序会尝试验证服务器证书。
注意
如果未请求加密，则 PreLogin 数据包仍已加密，但未验证证书。
若要验证服务器证书，客户端的计算机证书存储必须包含服务器证书的副本、受信任的根证书或受信任的中间证书。
从第三方证书颁发机构（CA）购买证书时，Windows 通常预安装了根证书，无需执行任何操作。
如果公司具有 CA，则需要通过组策略推送根证书或中间证书，或手动添加证书。
如果你有自签名证书并且需要它的客户端数很小，则通常需要手动添加它。 但是，如果客户端数很大，则可以使用组策略来分发证书。 从没有私钥的 SQL Server 导出它，以便安全。
如果 SQL Server 不使用证书，它将自行生成一个证书。
缓解措施
短期缓解：在应用程序的连接字符串中设置TrustServerCertificate=Yes。
长期缓解：购买或生成服务器的证书。