SQL Server and Certificates: TLS, TrustServerCertificate, Renewal, TDE and AG Endpoints

Certificates show up in SQL Server in two very different places. One kind lives in the Windows certificate store and protects the network connection between client and server (TLS). The other kind lives inside SQL Server, in sys.certificates, and protects keys: the database encryption key of TDE, encrypted backups, and the authentication between availability group replicas. Both kinds expire, both kinds break things when they do, and the tools to look at them are completely different.

This post goes through both, with the sqmSQLTool functions that cover each step. Every function description below is based on what the code actually does, including the parts it does not do. The background on choosing an encryption feature in the first place is in Encrypting SQL Server: Options and Prerequisites.

1. TLS for client connections: what really happens

SQL Server always encrypts the login packet, so passwords of SQL logins never travel in clear text. If no certificate is configured, the service generates a self-signed certificate at every start and uses it for that. Whether the rest of the session is encrypted depends on two switches:

• Server side: Force Encryption. The value ForceEncryption under the instance's SuperSocketNetLib registry key (set in SQL Server Configuration Manager). With 1, the server encrypts every connection, whether the client asked for it or not.
• Client side: Encrypt. The connection string asks for encryption, and, unless TrustServerCertificate is set, the driver validates the server certificate: issued by a trusted CA, not expired, and the server name in the connection string matches the certificate.

The difference matters. Force Encryption protects against clients that forget to ask for encryption. Only the client-side validation protects against a machine that pretends to be your SQL Server. You want both, and the second one needs a real certificate.

2. Why everything broke: ODBC 18, Microsoft.Data.SqlClient and friends

For many years drivers defaulted to Encrypt=false, so the auto-generated certificate never bothered anyone. That changed with one generation of client libraries:

• Microsoft.Data.SqlClient 4.0 changed the default to Encrypt=true (in 5.0 and later: Mandatory).
• ODBC Driver 18 defaults to Encrypt=yes, the OLE DB Driver 19 to Mandatory.
• Everything built on them followed: SSMS 20 and later, the SqlServer PowerShell module 22 (Invoke-Sqlcmd), the new go-based sqlcmd, and dbatools 2.x.

The result is the error message every DBA has seen since then:

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.)

The server did not change. The client now validates a certificate that was never meant to be validated: the self-signed one from the last service start.

What TrustServerCertificate really switches off

TrustServerCertificate=true makes the error go away, and that is why it is in half the connection strings on the internet. The connection is still encrypted, but the client no longer checks who it encrypts to. Anyone who can sit between client and server (a DNS entry, an ARP spoof, a rogue host with the old server name) can present any certificate and read along. For a test box that is fine. For production it is the opposite of what the new defaults were meant to achieve.

Two driver settings help on the way to doing it properly:

• HostNameInCertificate (Microsoft.Data.SqlClient 5.0+, ODBC 18): the name the certificate must contain, when you connect by IP address, a DNS alias or a name that is not on the certificate.
• Encrypt=Strict: TDS 8.0 (SQL Server 2022+), where the TLS handshake happens before any TDS traffic, like HTTPS. It is also the mode that allows TLS 1.3. In Strict mode, TrustServerCertificate is ignored, so you need a valid certificate first.

Connection string details are in SQL Server Connection Strings.

A note on sqmSQLTool itself

sqmSQLTool talks to SQL Server through dbatools, and in real environments many instances still run with the auto-generated certificate. So the module sets the dbatools option sql.connection.trustcert to true when it is imported. That is a deliberate trade-off for an admin tool, and you can turn it off once your servers have proper certificates:

Set-sqmConfig -TrustServerCertificate $false

The setting applies immediately and is saved for future sessions (since 1.9.179.0). From then on every dbatools connection of the module validates the server certificate, and an instance that still runs with the self-signed certificate fails with exactly the error above. The functions in this post are a good way to get there.

3. What a SQL Server TLS certificate needs

A certificate that SQL Server will load and that clients will accept has to meet all of these:

4. Audit first: Get-sqmTlsStatus

Get-sqmTlsStatus answers "what is the TLS state of my servers" without a SQL connection. It reads the registry and the certificate store, locally or through PowerShell remoting:

Get-sqmTlsStatus -ComputerName SQL01, SQL02 | Where-Object Status -ne 'OK'

Per instance it reports the bound thumbprint (empty means the auto-generated certificate), ForceEncryption, and for the bound certificate the subject, the SAN entries, the expiry date and the days left, whether the private key is present, and whether the chain can be built to a trusted root (revocation is not checked). Per computer it reads the SCHANNEL registry state of TLS 1.0, 1.1, 1.2 and 1.3: Enabled, Disabled or NotConfigured (the operating system default applies).

• Critical: the bound certificate is missing from the store, the chain is not trusted, or it is expired.
• Warning: it expires within -WarnDaysBeforeExpiry days (default 60), Force Encryption is off, TLS 1.0 or 1.1 is enabled, or no certificate is bound at all.

The results come back as objects and as CSV, TXT and an HTML report. What it does not check: whether the service account can actually read the private key, the key usage and EKU, and whether the SAN matches the names your clients use. Those are on you, or on the next function.

5. Get a proper certificate: New-sqmCertificateRequest

If your company has an internal CA, the fastest way to a correct certificate is a correct request. New-sqmCertificateRequest writes a certreq.exe INF file and runs certreq -new, which produces the CSR and an order sheet with all the details the CA team asks for:

New-sqmCertificateRequest -SqlInstance SQL01 -Purpose SSL `
    -Subject 'CN=sql01.corp.example,O=Example,C=DE' `
    -SubjectAlternativeNames 'sql01', 'sqlag-listener.corp.example'

The INF asks for an exportable machine key (Microsoft RSA SChannel provider, KeySpec 1, 4096 bit by default), Key Usage Digital Signature and Key Encipherment, and for -Purpose SSL the EKUs Server and Client Authentication. The SAN list is built from the CN, the FQDN and the short name of the host, plus your -SubjectAlternativeNames. If you pass -CertificateName to use an existing certificate from sys.certificates as template, the function also adds the DNS names of all availability group listeners on the instance.

One thing to know: certreq -new creates the key pair in the machine store of the computer where you run it. For a TLS certificate, run the function on the SQL Server host. When the CA sends the certificate back, certreq -accept on the same host joins it with the waiting private key in LocalMachine\My.

6. Distribute what clients need to trust: Install-sqmCertificateToStore

Validation only works if the clients trust the issuer. Install-sqmCertificateToStore puts a .cer, .crt or .pfx file into a LocalMachine store (Root, CA, My or TrustedPeople) on one or many computers. For remote targets the certificate bytes travel through PowerShell remoting, so no file share is needed:

# Root CA to the application servers
Install-sqmCertificateToStore -CertFile C:\Certs\CorpRootCA.cer -StoreName Root `
    -ComputerName APP01, APP02, APP03

# Server certificate with private key, delivered by the CA as PFX
Install-sqmCertificateToStore -CertFile C:\Certs\sql01.pfx -StoreName My `
    -ComputerName SQL01 -CertPassword (Read-Host -AsSecureString)

A certificate with the same thumbprint that is already there is skipped and reported as AlreadyPresent.

7. Bind it: Set-sqmSqlTlsCertificate

Set-sqmSqlTlsCertificate does the steps that Configuration Manager hides behind a dropdown, plus the one it often forgets:

1. Finds the certificate in LocalMachine\My by thumbprint, refuses it if it is expired or has no private key, warns if it expires within 30 days.
2. Reads the service account of the instance and grants it Read on the private key file, for classic CSP keys (MachineKeys) as well as CNG keys (Crypto\Keys).
3. Writes the thumbprint to SuperSocketNetLib\Certificate, optionally sets ForceEncryption = 1, and optionally restarts the service.

# Dry run first, on the SQL Server host
Set-sqmSqlTlsCertificate -SqlInstance SQL01 -Thumbprint 'A1B2...' -ForceEncryption -WhatIf

# Then for real, in the maintenance window
Set-sqmSqlTlsCertificate -SqlInstance SQL01 -Thumbprint 'A1B2...' -ForceEncryption -Restart

If the CA delivers a PFX file, Install-sqmCertificate -Purpose SSL -SetSqlServerSslCert does both steps in one call: it imports the PFX into LocalMachine\My and hands the binding to Set-sqmSqlTlsCertificate.

Install-sqmCertificate -SqlInstance SQL01 -CertFile C:\Certs\sql01.pfx `
    -CertPassword (Read-Host -AsSecureString) -Purpose SSL -SetSqlServerSslCert

Both change the local registry and file system, so run them on the SQL Server host itself; they warn if the instance name points somewhere else. Check PrivateKeyGranted in the result: if the function cannot find the key file, it says so and you have to grant the permission by hand (certlm.msc, "Manage Private Keys"). Without that, the restart is exactly the moment the service fails to come up. The new certificate is only used after a restart; afterwards, check that SQL Server Agent is running again and run Get-sqmTlsStatus once more.

For Reporting Services and Power BI Report Server the same certificate topic is a web server binding. Set-sqmSsrsHttpsCertificate removes the old HTTPS URL reservations and SSL bindings through the SSRS WMI provider, reserves the HTTPS URLs for the web service and the web portal, binds the certificate and, with -RequireSSL, sets SecureConnectionLevel to 1. If your reports complain about certificates in shared data sources instead, that is a client-side problem: see this post.

8. Renewal of the TLS certificate

A renewed certificate is a new certificate with a new thumbprint. SQL Server does not pick it up on its own. So renewal is the same chain as the first installation: request (or let auto-enrollment do it), import into LocalMachine\My, Set-sqmSqlTlsCertificate with the new thumbprint, restart in a maintenance window. The old certificate can stay in the store until the restart has gone through.

The part that goes wrong in practice is not the procedure, it is noticing in time. Get-sqmTlsStatus as a scheduled job with the default 60-day warning gives enough lead time for a CA order and a change ticket.

9. Certificates inside SQL Server: Get-sqmCertificateReport

The second family of certificates never appears in the Windows store. Get-sqmCertificateReport reads them per instance:

Get-sqmCertificateReport -SqlInstance SQL01, SQL02 -WarningThresholdDays 180

• Whether a database master key exists in master, and whether it is encrypted by the service master key (needed so the instance can open it at startup without a password).
• Every certificate in master with expiry date, days left, status (OK, Warning under 90 days, Critical under 30 days, Expired), private key state and thumbprint.
• The purpose: AlwaysOn or Service Broker if an endpoint uses the certificate, TDE if a database encryption key is protected by it (with the database and its encryption state). Certificates for backup encryption cannot be detected from the catalog; the report recognizes them by name, so a naming convention with "backup" in the name pays off here.
• With -IncludeUserDatabases, the certificates in user databases as well (signing, cell encryption).

Besides the objects you get HTML, TXT and CSV, plus a separate CSV with only the expiring and expired certificates, which is a convenient input for a ticket or a monitoring check.

10. Renewing TDE and endpoint certificates: New-sqmSqlCertificate

New-sqmSqlCertificate renews a certificate inside SQL Server:

1. It reads the old certificate and finds out which database mirroring / AG or Service Broker endpoint and which TDE databases use it.
2. It backs up the old certificate to .cer, and the private key to .pvk encrypted with -BackupEncryptionPassword (mandatory when there is a private key).
3. It creates a new self-signed certificate with the same subject and -ValidityYears (default 5), named <old name>_<year> unless you choose a name, and backs that one up right away as well (.cer + .pvk, same folder, same password).
4. Only if you ask for it: -BindEndpoint switches the endpoint, -BindTde re-encrypts the database encryption keys with the new certificate.
5. It writes a renewal protocol. The old certificate is not dropped; that stays a manual step after you have verified everything.

The BACKUP CERTIFICATE runs in SQL Server, so the backup path is a path on the SQL Server host, written by the service account; the function creates the folder there. The new .cer in that folder is the public key the AG partners need, and .cer plus .pvk are what TDE secondaries and any later restore need.

TDE

New-sqmSqlCertificate -SqlInstance SQL01 -CertificateName TDE_PROD -BindTde `
    -BackupEncryptionPassword (Read-Host -AsSecureString 'Backup password')

Switching the TDE certificate only re-encrypts the database encryption key, not the data. It is quick and online. SQL Server keeps using a TDE certificate after its expiry date, so the rotation is a policy decision, not an outage. Three rules still apply:

• Store the backup of the new certificate away from the server. The function writes it next to the old one (since 1.9.179.0); the .pvk and its password are the only way back if master is lost.
• Keep the old certificate and its backup as long as any backup that was taken under it may need to be restored.
• In an availability group, the new certificate with its private key must exist on every secondary before you switch on the primary. Otherwise the secondaries cannot read the database encryption key anymore. Run the function on the primary without -BindTde, import the new .cer + .pvk from the backup folder on all secondaries, then switch. More on the order of operations in Enable TDE in an AlwaysOn Availability Group and in TDE explained.

For the import on the secondaries, Install-sqmCertificate runs CREATE CERTIFICATE ... FROM FILE ... WITH PRIVATE KEY for a .cer plus .pvk pair (all versions) or a PFX file (SQL Server 2022 and later). It checks beforehand that a database master key exists, skips the import if a certificate with the same name and thumbprint already exists, refuses if the name exists with a different thumbprint, and writes an installation protocol:

Install-sqmCertificate -SqlInstance SQL02 -CertFile \\SQL01\CertBackup\TDE_PROD_2031.cer `
    -PrivateKeyFile \\SQL01\CertBackup\TDE_PROD_2031.pvk `
    -CertPassword (Read-Host -AsSecureString 'PVK password')

The files are read twice: by PowerShell for the checks and by the SQL Server service account for the import. A UNC path that both can reach is the practical choice. On older SQL Server versions a PFX file is refused with a clear message: convert it to .cer + .pvk first.

AG endpoints

Availability groups between servers without a common domain (or with a policy against service account authentication) authenticate the database mirroring endpoints with certificates. Each replica has its own certificate with private key on its endpoint; every other replica has the public key of that certificate, owned by a user whose login has CONNECT on the endpoint.

That makes the order during renewal critical. If replica A switches its endpoint to the new certificate before B knows the new public key, B rejects A and the replicas disconnect. The safe sequence:

1. On A: New-sqmSqlCertificate -CertificateName AG_CERT without -BindEndpoint. The new public key lands in the backup folder as AG_CERT_2031.cer.
2. On every partner: import the public key, owned by the database user of A's endpoint login:

   Install-sqmCertificate -SqlInstance B `
       -CertFile \\fs01\certs\AG_CERT_2031.cer -CertificateName AG_CERT_2031 `
       -Purpose AlwaysOn -AuthorizationUser ag_user_A

3. On A: ALTER ENDPOINT [Hadr_endpoint] FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [AG_CERT_2031]); and check the AG state.

Without a private key, -Purpose AlwaysOn never touches the partner's own endpoint; it only imports the key with AUTHORIZATION, which is what makes SQL Server accept replica A. With a certificate that has its private key (CER + PVK, or PFX on SQL Server 2022+), the same mode switches the local endpoint, on the replica that owns the certificate. New-sqmSqlCertificate -BindEndpoint does step 3 for you, but only run it once step 2 is done everywhere. All of this was tested against SQL Server 2022 under Windows PowerShell 5.1 with a stopped throwaway endpoint; a real multi-replica AG renewal is the part a single lab instance cannot show.

Which function for which job

The Bottom Line

The new driver defaults did not make SQL Server less secure; they made visible that most installations never had a certificate a client could check. TrustServerCertificate=true hides that again. The way out is not complicated: a certificate with the right names, the private key readable by the service account, a trusted issuer on the clients, and someone who notices the expiry date two months ahead. Inside SQL Server, the rule is simpler still: back up every certificate with its private key the moment you create it, and change endpoints and TDE keys in the order that keeps every replica able to read them.

All functions are part of sqmSQLTool, source on GitHub.