Sev1: apps can't connect to SQL Server after a driver upgrade — 'certificate chain was issued by an authority that is not trusted'

#Symptoms

• After an application or container image update, connections fail with:
  • The certificate chain was issued by an authority that is not trusted. (SqlClient)
  • SSL Provider: [error:0A000086:SSL routines::certificate verify failed] (ODBC 18 on Linux)
  • The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption (JDBC)
• The database server didn't change.

#1. Why it happens

Newer drivers default to Encrypt=true (ODBC 18 and SqlClient 4.0: mandatory; JDBC 10.2+: true) and validate the server certificate. Many SQL Servers use the self-signed certificate SQL Server generates at startup, which no client trusts. Old drivers didn't check, so nobody noticed.

#2. Confirm on the server

SELECT session_id, encrypt_option, auth_scheme, client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Check which certificate SQL Server uses in the error log:

EXEC sys.xp_readerrorlog 0, 1, N'certificate';

The certificate [Cert Hash(sha1) ...] was successfully loaded for encryption with a self-generated certificate means clients can't validate it.

#3. Restore service now (temporary)

Set TrustServerCertificate in the connection string so the client encrypts but skips validation:

Server=sql-prod.corp.example.com;Database=Orders;Integrated Security=true;Encrypt=true;TrustServerCertificate=true

ODBC:

Driver={ODBC Driver 18 for SQL Server};Server=sql-prod.corp.example.com;Database=Orders;Encrypt=yes;TrustServerCertificate=yes;

Warning

TrustServerCertificate=true keeps traffic encrypted but allows man-in-the-middle attacks. Treat it as a short-term workaround with an end date, not the fix. Avoid Encrypt=false entirely.

#4. Proper fix: a trusted certificate on SQL Server

Requirements for the certificate:

• Issued by a CA the clients trust (internal enterprise CA is fine).
• Subject or SAN includes the name clients connect with (server FQDN, AG listener name, alias).
• Enhanced Key Usage: Server Authentication. Key exchange key (not signature-only).
• Installed in the Local Computer Personal store, with the SQL Server service account granted read access to the private key.

Grant private key access (PowerShell, adjust thumbprint and account):

$cert = Get-ChildItem Cert:\LocalMachine\My | Where-Object Thumbprint -eq '<THUMBPRINT>'
$keyName = $cert.PrivateKey.Key.UniqueName
$keyPath = "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$keyName"
icacls $keyPath /grant "NT Service\MSSQLSERVER:R"

(For CNG keys the file may live under Crypto\Keys; check both locations.)

Select the certificate in SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER > Certificate (SQL Server 2019+ can also import it there), then restart the SQL Server service in a maintenance window.

For an Availability Group, install a certificate on every replica that includes the listener name and each node's name.

#5. Verify from a client

Invoke-Sqlcmd -ServerInstance "sql-prod.corp.example.com" -Database master `
  -Query "SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id=@@SPID" `
  -Encrypt Mandatory

(The -Encrypt parameter exists in SqlServer module 22+.)

Then remove TrustServerCertificate=true from application connection strings, one app at a time.

#Prevent it next time

• Inventory SQL Server certificates and expiry dates; renew from your CA before expiry.
• Test driver upgrades against production-like SQL Servers in staging.
• Make "trusted certificate, connect by FQDN" part of the SQL Server build standard.
• Alert on certificate expiry 30 days ahead.