SQL Server does not alert when a certificate is about to expire. There is no performance counter, no error log entry at expiry time, and no DMV flag that flips the moment the date passes. The engine keeps using the certificate silently until something forces a re-evaluation, and at that point the failure mode depends entirely on what the certificate was protecting.

Three consumers of the same self-signed certificate pattern behave in three different ways:

  • TDE protector certificates are not enforcement-checked at expiry, so an expired TDE cert keeps the database working.
  • Backup encryption certificates fail the next backup immediately with Msg 3096 / 3013.
  • AG / database mirroring endpoint certificates keep working until SQL Server restarts or the endpoint is stopped and started, at which point the endpoint refuses to authenticate and every secondary disconnects.

None of these are surfaced as runtime metrics. All of them are detected by an explicit query against sys.certificates.

The dangerous scenario is not the expiry itself but the cascade that follows a restart, a failover, or a backup rotation that happens to land in the expiry window. AG endpoint certificate expiry has been observed to take down distributed AGs after a routine Windows patching cycle, with error log messages that point at networking rather than certificates. Backup encryption expiry silently breaks the restore chain. TDE cert expiry is operationally silent but becomes a problem during key rotation or restore.

What this means

Certificates in SQL Server back three independent subsystems:

  • TDE protector certificates encrypt the database encryption key (DEK) for databases with TDE enabled. sys.dm_database_encryption_keys.encryption_state reflects whether encryption is in progress (2), encrypted (3), key change in progress (4), decryption in progress (5), or protection change in progress (6).
  • AG / database mirroring endpoint certificates authenticate the HADR endpoint, common on domain-less or cross-domain setups where Windows Negotiate auth is not available.
  • Backup encryption certificates encrypt the backup media when WITH ENCRYPTION is specified on BACKUP DATABASE.

The default expiry window for self-signed certificates created without EXPIRY_DATE is one year from creation . Most operators hit their first rotation cycle inside the first 12 months after deployment, and most have not built alerting for it because the engine does not surface it.

A single expired certificate can manifest in multiple subsystems at once if it is overloaded, but in practice the failure surfaces wherever a consumer first re-evaluates:

  • New encrypted backups fail immediately (Msg 3096, Msg 3013 terminating the BACKUP).
  • AG endpoints keep working until the next endpoint stop/start or SQL Server restart.
  • TDE keeps working indefinitely for encrypt/decrypt, but key rotation is blocked and restores that need the original cert may fail.
flowchart TD
    Cert[SQL Server certificate expires]
    Cert --> TDE[TDE protector: silent, no enforcement]
    Cert --> AG[AG endpoint: silent until restart or endpoint stop/start]
    Cert --> BU[Backup encryption: fails immediately on next backup]
    TDE --> TDERestore[Restores needing original cert fail if cert dropped]
    AG --> AGDown[All secondaries disconnect after restart]
    BU --> BUDown[Backup jobs fail with Msg 3096 + 3013]

Common causes

CauseWhat it looks likeFirst thing to check
TDE protector certificate expiredDatabase still online; encryption_state still 3; no errors; key rotation blockedsys.certificates.expiry_date joined to DEK via thumbprint
AG endpoint certificate expiredReplicas worked until restart or Windows patching; secondaries now DISCONNECTED; error log mentions “connection timeout” or “availability replica”sys.database_mirroring_endpoints joined to sys.certificates on certificate_id
Backup encryption certificate expiredBACKUP DATABASE ... WITH ENCRYPTION fails with Msg 3096 / 3013msdb.dbo.backupset.encryptor_type + sys.certificates.expiry_date
Old TDE cert dropped after rotationRestore of older backup fails with “Cannot find server certificate”sys.certificates for the missing thumbprint; check old cert backup exists
Endpoint certificate-to-login mapping lost during rotationEndpoint auth fails after cert replacement even with new cert in placeEndpoint certificate_id and login-to-certificate mapping

Quick checks

Run these in the master database context. All are read-only.

-- Check 1: List certificates expiring within 90 days, ordered by expiry
SELECT name, subject, start_date, expiry_date,
       DATEDIFF(DAY, GETDATE(), expiry_date) AS days_until_expiry
FROM sys.certificates
WHERE expiry_date < DATEADD(DAY, 90, GETDATE())
ORDER BY expiry_date;

-- Check 2: Map TDE protector certificates to databases and their encryption state
SELECT d.name AS database_name,
       c.name AS certificate_name,
       c.expiry_date,
       dek.encryption_state,
       CASE dek.encryption_state
            WHEN 0 THEN 'No database encryption key'
            WHEN 1 THEN 'Unencrypted'
            WHEN 2 THEN 'Encryption in progress'
            WHEN 3 THEN 'Encrypted'
            WHEN 4 THEN 'Key change in progress'
            WHEN 5 THEN 'Decryption in progress'
            WHEN 6 THEN 'Protection change in progress'
       END AS encryption_state_desc
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases d ON dek.database_id = d.database_id
LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint;

-- Check 3: AG / database mirroring endpoint authentication method and bound cert
SELECT name, state_desc, protocol_desc,
       connection_auth_desc, certificate_id
FROM sys.database_mirroring_endpoints;
-- If connection_auth_desc contains CERTIFICATE, join certificate_id to sys.certificates

-- Check 4: Join endpoint certificate to its expiry
SELECT dme.name AS endpoint_name, dme.connection_auth_desc,
       c.name AS certificate_name, c.expiry_date
FROM sys.database_mirroring_endpoints dme
LEFT JOIN sys.certificates c ON dme.certificate_id = c.certificate_id;

-- Check 5: Recent backup encryption usage and which certificate thumbprint was used
SELECT database_name, encryptor_type, encryptor_thumbprint,
       backup_start_date, type AS backup_type
FROM msdb.dbo.backupset
WHERE encryptor_type IS NOT NULL
ORDER BY backup_start_date DESC;

-- Check 6: AG replica state and connectedness (correlate with endpoint cert expiry)
SELECT ag.name AS ag_name,
       ar.replica_server_name,
       ars.role_desc,
       ars.connected_state_desc,
       ars.synchronization_health_desc,
       ars.last_connect_error_description
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;

-- Check 7: Stalled TDE encryption state (2/4/5/6 stuck beyond expected scan duration)
SELECT d.name AS database_name, dek.encryption_state,
       dek.encryption_scan_modify_date
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases d ON dek.database_id = d.database_id
WHERE dek.encryption_state IN (2, 4, 5, 6);

-- Check 8: Error log for the misleading "availability replica" pattern after restart
EXEC sp_readerrorlog 0, 1, 'availability replica';

How to diagnose it

  1. Inventory every certificate and its consumer. Run checks 1 through 4. Confirm which certificates are bound to TDE, which to AG endpoints, and which to backup encryption. Treat each binding independently: a TDE-only certificate does not carry endpoint-style rotation urgency, and an endpoint certificate that is also a TDE protector is two problems at once.
  2. Confirm the failure mode with the error log. After a restart with an expired endpoint certificate, the error log typically reports connection timeouts to the availability replica, not “certificate expired”. This is misleading. If check 6 shows DISCONNECTED secondaries and check 4 shows an expired endpoint certificate, the certificate is the root cause even though the error message reads like a network problem.
  3. Distinguish expiry from other failure causes. A failed encrypted backup could be certificate expiry (Msg 3096), a missing certificate, or a permissions issue on the certificate private key. The Msg 3096 / 3013 pair specifically indicates expiry. Use check 5 to confirm which cert the backup job is referencing.
  4. Check whether old certificates still exist. If a TDE rotation was done recently and older backups fail to restore, the cause is the dropped original certificate. SQL Server needs every TDE protector that ever encrypted a log block you are trying to restore. KB4534430 documents a related case where dropping the original cert after rotation breaks log backups taken with COMPRESSION and MAXTRANSFERSIZE .
  5. Cross-check required_synchronized_secondaries_to_commit. If the AG has required_synchronized_secondaries_to_commit > 0 and the endpoint cert has expired, commits on the primary will block once secondaries disconnect after a restart.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
sys.certificates.expiry_date per certificatePrimary signal; SQL Server does not expose this as a metric< 90 days to expiry: ticket. < 7 days: page.
sys.dm_database_encryption_keys.encryption_state per databaseStuck 2/4/5/6 indicates a stalled TDE operation that may relate to a cert problemStays in 2/4/5/6 longer than the expected scan duration
sys.dm_hadr_availability_replica_states.connected_state_descDISCONNECTED secondaries after a restart with an expired endpoint certDISCONNECTED on synchronous replica sustained > 120 seconds
msdb.dbo.backupset.encryptor_thumbprintTies backups to specific certificates; flags when one cert is the only restore path for many backupsA single cert protecting more backups than your retention can survive losing
Error log entries referencing “availability replica” or “connection timeout” following a restartMost reliable in-engine indicator that endpoint auth is brokenCluster of these entries within minutes of instance startup
sys.database_mirroring_endpoints.connection_auth_desc containing CERTIFICATEFlags endpoints that will break on cert expiryCombined with cert expiry < 90 days

SQL Server does not surface certificate expiry as a runtime metric. Whatever monitoring platform you use, this needs an explicit query job that runs at least daily, persists the result, and alerts on a threshold rather than waiting for the engine to complain.

Fixes

The fix differs by consumer. Treat them separately even when they share a certificate.

Replace an expired TDE protector certificate

  1. Back up the existing certificate (private key included) before doing anything: BACKUP CERTIFICATE <old> TO FILE = ... WITH PRIVATE KEY .... Keep this backup for the lifetime of any backup that depends on it.
  2. Create or restore the new certificate on the primary and on every AG secondary that hosts the database.
  3. Rotate the DEK protector: ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE <new>;.
  4. Wait for sys.dm_database_encryption_keys.encryption_state to return to 3 (Encrypted). Expect brief transitions through 4 (key change in progress) and 6 (protection change in progress).
  5. Do not drop the old certificate yet. Drop it only after confirming every restore path that depends on it has been migrated, and after taking at least one full backup under the new cert.

Replace an expired AG endpoint certificate

  1. Create the new certificate on the primary, back it up, and restore it on every secondary that authenticates via this endpoint.
  2. Create or confirm the corresponding login on each partner that maps to the certificate.
  3. Alter the endpoint to use the new certificate: ALTER ENDPOINT ... FOR DATA_MIRRORING AUTHENTICATION = CERTIFICATE [<new>] ....
  4. Stop and restart the endpoint with ALTER ENDPOINT ... STATE = STOPPED followed by STATE = STARTED. The endpoint will not pick up the new cert without this. This disconnects replicas briefly; do it inside a planned window.
  5. Verify sys.dm_hadr_availability_replica_states.connected_state_desc returns to CONNECTED on all replicas.

If the endpoint was configured with CERTIFICATE, NEGOTIATE and the certificate side has expired but Windows auth is available, a temporary mitigation is to drop certificate auth and rely on NEGOTIATE only . This is a workaround, not a fix. Bring certificate auth back once the new certificate is in place.

Replace an expired backup encryption certificate

  1. Create the new certificate, or restore it from a known-good backup.
  2. Update the backup job, maintenance plan, or Ola Hallengren job to reference the new certificate by name in the WITH ENCRYPTION (SERVER CERTIFICATE = ...) clause.
  3. Re-run the failed backup. Msg 3096 should disappear.
  4. Keep the old certificate installed in master until every backup encrypted with it has been overwritten or has reached end of retention. Restore of an existing encrypted backup still works with an expired certificate as long as the certificate is present .

Prevention

  • Build an external expiry check. Schedule a daily job that runs check 1 and persists the output. Alert at 90 days out (ticket), 30 days (warning), and 7 days (page). Do not rely on the SQL Server engine to tell you.
  • Track certificates by consumer, not by name. A single expiry query without context will not tell you which certificates are AG-critical. Join to sys.dm_database_encryption_keys, sys.database_mirroring_endpoints, and msdb.dbo.backupset.encryptor_thumbprint.
  • Standardize on a longer EXPIRY_DATE for self-signed certs. The default one-year window is appropriate for some compliance regimes but operationally short. Pick a window that matches your patching and rotation cadence.
  • Keep every retired TDE protector until its backups age out of recovery requirements. Treat dropped TDE certs as equivalent to dropping the ability to restore.
  • Document restart-dependent failures in your runbooks. An endpoint cert that expired six months ago can sit silent through many failover drills until a restart exposes it. Add a pre-restart certificate check to the patching runbook.
  • Test AG endpoint auth rotation in non-production. If your AG endpoint uses certificate auth, validate the full rotation procedure (create, back up, restore, alter endpoint, stop/start endpoint) on a non-production AG at least once before doing it under pressure.

How Netdata helps

  • Netdata collects per-second SQL Server metrics including AG replica state (connected_state_desc, synchronization_health_desc), so a DISCONNECTED secondary after a restart shows up immediately and correlates with the restart event itself.
  • Netdata can run custom SQL queries against sys.certificates and sys.dm_database_encryption_keys on a schedule, persist the expiry window, and alert on the 90/30/7 day thresholds without depending on the engine to surface expiry as a counter.
  • Correlating a sudden AG disconnection with a recent SQL Server service restart is the fastest way to separate a network issue from an endpoint auth issue. The two failure modes look identical in the error log.
  • TDE encryption_state transitions (2/4/5/6) and their duration are visible as a state timeline alongside AG and backup freshness signals, which lets you see a stalled rotation as it happens rather than after the fact.
  • ML-based anomaly detection on the AG send queue, redo queue, and HADR_SYNC_COMMIT wait time flags the secondary-side impact of an endpoint auth failure before the operator has to read the error log.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.