Certificate Validation Failure - microsoft/CSS_SQL_Networking_Tools GitHub Wiki
This topic shows the usual pattern of packets when the client fails to validate the server certificate.
In the client application, the error is similar to this:
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
Network Trace
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,
Network Trace With Comments
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 a 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,
Explanation
When the client requests data encryption (Encrypt=yes or Use Encryption for Data=True) or the server requires data encryption (Force Encryption=Yes for newer drivers only), then the client driver will try to validate the server certificate.
Note: If encryption is not requested, then the Login packet is still encrypted but the certificate is not validated.
In order to validate the server certificate, the client's computer (CA) certificate store must contain a copy of the server certificate or a trusted root certificate or a trusted intermediate certificate. When you buy a certificate from a 3rd-party certificate authority, Windows usually comes preinstalled with the root certificate and you don't have to do anything.
If your company has a certificate authority, then you need to push out the root or intermediate certificates via a Group Policy or add them manually.
If you have a self-signed certificate, then you generally need to add that manually if the number of clients needing it are small, but you can use a Group Policy. Export it from the SQL Server without the Private Key for security.
If SQL Server is not using a certificate, then it will generate one for itself. As a short-term mitigation, set TrustServerCertificate=Yes in the applications' connection string. For a long-term mitigation, purchase or generate a certificate for the server.
This example shows SQL Server using a self-generated certificate but any certificate could be in the Server Hello packet if it's not trusted on the client for some reason.