注意
本文適用於熟悉擷取和讀取網路追蹤的人員。
徵兆
當客戶端無法驗證伺服器憑證時，您會在用戶端應用程式中看到下列錯誤訊息：
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 封包中。
說明
請檢查網路追蹤，以瞭解客戶端與伺服器之間的通訊流程。
- Frame 590 - 599：TCP 交握和 PreLogin 封包指出需要數據加密。
- Frame 605 - 617：TLS 信號交換與伺服器 Hello 傳送伺服器證書。
- 畫面 686 - 719：因為憑證驗證失敗而終止連線。
用戶端會要求數據加密，這會觸發伺服器證書的驗證。 不過，伺服器會傳送自我簽署憑證來SSL_Self_Signed_Fallback回應 ，這會導致驗證失敗。
當用戶端要求數據加密 （或） 或Use Encryption for Data=True伺服器需要數據加密時（Encrypt=yesForce Encryption=Yes僅適用於較新的驅動程式），用戶端驅動程式會嘗試驗證伺服器證書。
注意
如果未要求加密，則 PreLogin 封包仍會加密，但不會驗證憑證。
若要驗證伺服器證書，用戶端計算機證書存儲必須包含伺服器證書、受信任的跟證書或受信任的中繼憑證複本。
當您從第三方證書頒發機構單位 （CA） 購買憑證時，Windows 通常會預安裝跟證書，您不需要執行任何動作。
如果您的公司有 CA，您必須透過組策略推送根或中繼憑證，或手動新增它們。
如果您有自我簽署憑證，且需要它的用戶端數目很小，您通常需要手動新增它。 不過，如果客戶端數目很大，您可以使用組策略來散發憑證。 從沒有私鑰的 SQL Server 匯出它，以取得安全性。
如果 SQL Server 未使用憑證，它就會自行產生一個憑證。
風險降低
短期風險降低：在應用程式的 連接字串 中設定TrustServerCertificate=Yes。
長期風險降低：購買或產生伺服器的憑證。