Question
SQL Server and Client-Side DNS: Certificate Subject Name Validation
Cig Atlas
0 reputation · 05 Feb 2023, 18:13 UTC
62.9K views1
Loading…
0 reputation · 05 Feb 2023, 18:13 UTC
When establishing a secure connection to Microsoft SQL Server using Encrypt=True and TrustServerCertificate=False, the client must validate that the server's identity matches the certificate provided during the TLS handshake.
The validation process typically relies on the hostname specified in the connection string. If the client uses a DNS alias (CNAME) or a load balancer virtual IP to reach the server, the underlying Windows DNS resolution may return a name that differs from the Common Name (CN) or Subject Alternative Name (SAN) embedded in the server's certificate.
Given these requirements, how does SQL Server handle validation when the DNS-resolved name differs from the connection string's target? What are the specific requirements for the Subject Alternative Name (SAN) field to support multiple DNS aliases in a high-availability environment?
When a client connects to SQL Server with Encrypt=True and TrustServerCertificate=False, the client driver performs a strict identity check. It compares the hostname provided in the connection string against the identities listed in the server's certificate. If the DNS-resolved name does not exactly match an entry in the certificate, the TLS handshake is aborted with a certificate validation error.
SQL Server itself does not "reverse-lookup" the certificate to validate the connection; rather, the client driver uses the string in the connection property as the expected identity. If you use a CNAME or a load balancer VIP, the driver expects that specific alias or VIP to be present in the certificate's Common Name (CN) or Subject Alternative Name (SAN) fields.
microsoftsqlserverdb-alias.com to 10.0.0.5, the driver checks if db-alias.com is valid in the certificate.physical-server-name and the client connects via db-alias.com, validation will fail despite the DNS resolution being successful.To support multiple DNS aliases, CNAMEs, or virtual IPs in an HA environment, the certificate must utilize the Subject Alternative Name (SAN) extension. The Common Name (CN) is deprecated in favor of SAN for modern TLS validation.
Specific requirements for a robust SAN configuration include:
DNS Name entry within the SAN field.IP Address entry.*.example.com) are supported, they only cover one level of subdomains and will not match the root domain or nested levels.To verify that your certificate supports your DNS aliases, use OpenSSL to simulate the handshake and inspect the returned certificate:
openssl s_client -connect your-server-fqdn:1433 -servername your-dns-alias.com -showout
Look for the Subject Alternative Name section in the output to ensure your aliases are listed correctly.
Use comments to ask for clarification. Post a solution as an answer.
32,775 reputation · 05 Feb 2023, 19:17 UTC
One case worth adding: connecting by IP address rather than a hostname. When the connection string's Server value is an IP (a literal load balancer VIP, for example), hostname validation looks for an iPAddress SAN entry — a dNSName entry for the same address will not satisfy the check. A DNS-name-only certificate fails in that setup even though alias resolution itself works.
Two related caveats:
Encrypt to on by default, so connections that previously ignored certificates can start failing after a driver upgrade with no server-side change.Practical check: connect with Encrypt=True;TrustServerCertificate=False through every alias and IP in use, confirm a deliberately mismatched name fails, and note that exact matching rules vary by driver and version — verify against your actual client.