Question
Choosing between CA-issued certificate validation and TrustServerCertificate for SQL Server TLS in a mixed DNS environment
Ash Vale
0 reputation · 20 Mar 2021, 14:28 UTC
91.1K views1
Loading…
0 reputation · 20 Mar 2021, 14:28 UTC
Our SQL Server 2022 instances serve applications that connect using both fully qualified domain names (FQDNs) and short hostnames, depending on network zone and legacy configuration. We want to enforce encryption with certificate validation (Encrypt=true, TrustServerCertificate=false) in production, but the current self-signed certificate only contains the FQDN in its SAN. Re-issuing a CA-signed certificate with every short name as a SAN entry is operationally cumbersome, while setting TrustServerCertificate=true removes hostname verification entirely and introduces spoofing risk.
The core constraint is that client drivers (Microsoft.Data.SqlClient 5.x, ODBC 18) will reject connections when the connection string hostname does not match the certificate SAN, yet we cannot immediately standardize all connection strings to the FQDN. We need a documented approach that maintains validation for FQDN connections while allowing a controlled transition for short-name clients.
Is there a supported configuration — such as multiple certificates per instance, a wildcard SAN, or a fallback validation path — that lets us keep TrustServerCertificate=false for FQDN clients while safely accommodating short-name clients during migration? What does Microsoft's documentation say about certificate selection when multiple hostnames are in use?
35275 reputation · 21 Mar 2021, 01:41 UTC
Microsoft does not provide a supported fallback validation path, certificate aliasing, or staged hostname migration mechanism for SQL Server TLS. When TrustServerCertificate=false, the driver must match the connection string hostname exactly to a SAN entry in the presented certificate. No configuration validates FQDN connections strictly while silently accepting short-name mismatches on the same instance.
microsoftsqlserverdNSName SAN entry exactly. Multiple certificates per instance are supported via SQL Server Configuration Manager, but the server presents the certificate whose SAN aligns with the requested hostname.Server value against the certificate's SAN entries. Single-label hostnames (e.g., sql01) receive no wildcard or suffix-matching logic.TrustServerCertificate=true disables all hostname verification. It does not create a partial trust mode; it simply skips the check, which carries the documented MITM risk.dNSName entries. Automate re-issuance via your internal CA to reduce operational overhead.*.corp.example.com) if all short hostnames share a single DNS suffix. Note: wildcards do not match bare single-label hostnames, and a compromised private key affects all covered hosts.Server=sql01 resolves to sql01.corp.example.com. The driver then sends the FQDN in SNI, matches the existing FQDN certificate, and validates correctly. This requires no certificate changes but depends on controlling client DNS configuration.One diagnostic detail needed: Do the short hostnames used by legacy clients resolve via a consistent DNS suffix (e.g., all are *.corp.example.com), or are they bare single-label names with no domain component? This determines whether a wildcard certificate (Path 2) or DNS search suffix standardization (Path 3) is feasible.
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.