Part of the DBA-Tools Project.
The Encryption Layer Nobody Monitors Until It Expires
Certificates created for TDE, backup encryption, or an Availability Group encrypted endpoint get created once and then quietly forgotten. An expired certificate doesn’t break TDE in memory on the server that has it, but it prevents restoring that TDE-encrypted database anywhere else, the exact moment you’d discover the problem is a disaster recovery drill or a real outage, the worst possible time.
Get-CertificateExpiryWarnings scans every certificate across server and user databases with days-until-expiry. Get-CertificatesAndKeys goes further, identifying what each certificate and key is actually used for (TDE, Service Broker, column encryption) with lifecycle risk flags. Get-TdeStatus shows the encryption state of every database, including the tempdb side-effect that surprises people who didn’t expect it.
Why Certificates, Keys, and TDE Status Matter
- An expired TDE certificate blocks restoring that database to a different server entirely, backups remain unreadable without the exact certificate and private key that encrypted them
- Enabling TDE on any user database encrypts tempdb automatically, since tempdb holds working data from every database on the instance, a real and expected side effect worth knowing about before it looks like a mystery
- Certificates back Service Broker dialogs and column-level encryption too, not just TDE, an expired cert here breaks message exchange or decryption silently
- Open symmetric keys and asymmetric keys without a clear owner are worth investigating, an unidentified encryption key in production is a real audit gap
When to Run These Scripts
- Routine security and encryption reviews, ideally scheduled well before any certificate’s expiry date, not triggered by one
- Before any migration or DR failover involving a TDE-encrypted database, to confirm the certificate is valid and its private key is backed up
- When investigating unexpected tempdb encryption or a Service Broker/column-encryption failure
- Alongside Edition Feature Usage, since TDE is one of the Enterprise-dependent features that check flags for downgrade risk
The Scripts
1. Get-CertificateExpiryWarnings — Every Certificate, Server and Database Scope
/*
Script Name : Get-CertificateExpiryWarnings
Category : security
Purpose : All user-managed certificates across server and user databases with days until expiry.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-certificates-keys-and-tde-status/)
Requires : VIEW ANY DATABASE, VIEW DATABASE STATE
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-certificates-keys-and-tde-status/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#CertInfo') IS NOT NULL DROP TABLE #CertInfo;
CREATE TABLE #CertInfo (
cert_scope NVARCHAR(128) NOT NULL,
cert_name NVARCHAR(128) NOT NULL,
subject NVARCHAR(500),
issuer_name NVARCHAR(500),
start_date DATETIME,
expiry_date DATETIME,
days_until_expiry INT,
expiry_status VARCHAR(10) NOT NULL,
pvt_key_type NVARCHAR(60),
is_service_broker BIT,
pvt_key_last_backup DATETIME
);
/* ── Server-level certs in master (TDE, backup encryption, SB endpoint) ─── */
INSERT INTO #CertInfo
SELECT
N'master' AS cert_scope,
name AS cert_name,
subject,
issuer_name,
start_date,
expiry_date,
DATEDIFF(DAY, GETDATE(), expiry_date),
CASE
WHEN expiry_date < GETDATE() THEN 'EXPIRED'
WHEN DATEDIFF(DAY, GETDATE(), expiry_date) <= 30 THEN 'CRITICAL'
WHEN DATEDIFF(DAY, GETDATE(), expiry_date) <= 90 THEN 'WARNING'
ELSE 'OK'
END,
pvt_key_encryption_type_desc,
is_active_for_begin_dialog,
pvt_key_last_backup_date
FROM sys.certificates
WHERE name NOT LIKE '##%';
/* ── Per-database certs (Service Broker, column encryption) ─────────────── */
DECLARE @db NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE cert_cur CURSOR FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE'
AND database_id > 4
AND HAS_DBACCESS(name) = 1
ORDER BY name;
OPEN cert_cur; FETCH NEXT FROM cert_cur INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ' + QUOTENAME(@db) + N';
INSERT INTO #CertInfo
SELECT
DB_NAME(), name, subject, issuer_name, start_date, expiry_date,
DATEDIFF(DAY, GETDATE(), expiry_date),
CASE
WHEN expiry_date < GETDATE() THEN ''EXPIRED''
WHEN DATEDIFF(DAY, GETDATE(), expiry_date) <= 30 THEN ''CRITICAL''
WHEN DATEDIFF(DAY, GETDATE(), expiry_date) <= 90 THEN ''WARNING''
ELSE ''OK''
END,
pvt_key_encryption_type_desc, is_active_for_begin_dialog, pvt_key_last_backup_date
FROM sys.certificates
WHERE name NOT LIKE ''##%'';';
BEGIN TRY EXEC sp_executesql @sql; END TRY BEGIN CATCH END CATCH;
FETCH NEXT FROM cert_cur INTO @db;
END;
CLOSE cert_cur; DEALLOCATE cert_cur;
SELECT
cert_scope,
cert_name,
subject,
issuer_name,
expiry_date,
days_until_expiry,
expiry_status,
pvt_key_type,
is_service_broker,
pvt_key_last_backup
FROM #CertInfo
ORDER BY days_until_expiry ASC, cert_scope, cert_name;
DROP TABLE #CertInfo;
2. Get-CertificatesAndKeys — Usage Detection and Lifecycle Risk
/*
Script Name : Get-CertificatesAndKeys
Category : security
Purpose : Server-level certificates and asymmetric keys with expiry, usage detection,
and lifecycle risk flags. Certificates created for TDE, AG encrypted endpoints,
or linked server auth are commonly created and never monitored. An expired cert
doesn't break TDE in memory but prevents restoring the database on another server.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-certificates-keys-and-tde-status/)
Requires : VIEW ANY DATABASE, VIEW ANY DEFINITION
*/
-- Blog: https://sqldba.blog/dba-scripts-get-certificates-keys-and-tde-status/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT *
FROM (
-- Server-level certificates
SELECT
CAST('CERTIFICATE' AS NVARCHAR(30)) AS object_type,
c.name COLLATE DATABASE_DEFAULT AS cert_or_key_name,
DB_NAME() AS database_context,
c.pvt_key_encryption_type_desc COLLATE DATABASE_DEFAULT AS key_protection,
CONVERT(NVARCHAR(128), c.thumbprint, 1) AS thumbprint,
c.start_date,
c.expiry_date,
DATEDIFF(DAY, GETDATE(), c.expiry_date) AS days_until_expiry,
c.subject COLLATE DATABASE_DEFAULT AS subject_or_algorithm,
CASE
WHEN EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys ek
WHERE ek.encryptor_thumbprint = c.thumbprint)
THEN 'TDE — protects database encryption key'
WHEN c.pvt_key_encryption_type_desc = 'NO_PRIVATE_KEY'
THEN 'Public cert only (no private key — cannot sign or decrypt)'
ELSE 'Unidentified — review usage manually'
END AS used_for,
ISNULL(
STUFF((
SELECT ', ' + DB_NAME(ek2.database_id)
FROM sys.dm_database_encryption_keys AS ek2
WHERE ek2.encryptor_thumbprint = c.thumbprint
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(500)'), 1, 2, ''),
NULL) AS tde_databases,
CASE
WHEN c.expiry_date < GETDATE()
THEN 'CRITICAL — EXPIRED; TDE databases cannot be restored elsewhere with this cert'
WHEN DATEDIFF(DAY, GETDATE(), c.expiry_date) < 30
THEN 'CRITICAL — expires in ' + CAST(DATEDIFF(DAY, GETDATE(), c.expiry_date) AS VARCHAR) + ' days'
WHEN DATEDIFF(DAY, GETDATE(), c.expiry_date) < 90
THEN 'WARN — expires in ' + CAST(DATEDIFF(DAY, GETDATE(), c.expiry_date) AS VARCHAR) + ' days'
WHEN c.pvt_key_encryption_type_desc = 'NO_PRIVATE_KEY'
THEN 'INFO — no private key; verify this is intentional'
ELSE 'OK'
END AS status
FROM sys.certificates AS c
WHERE c.name NOT LIKE '##%'
UNION ALL
-- Asymmetric keys
SELECT
CAST('ASYMMETRIC_KEY' AS NVARCHAR(30)),
ak.name COLLATE DATABASE_DEFAULT,
DB_NAME(),
ak.pvt_key_encryption_type_desc COLLATE DATABASE_DEFAULT,
CONVERT(NVARCHAR(128), ak.thumbprint, 1),
NULL,
NULL,
NULL,
(ak.algorithm_desc COLLATE DATABASE_DEFAULT)
+ ' / ' + CAST(ak.key_length AS VARCHAR) + '-bit',
'Asymmetric key — check if used for column encryption or EKM',
NULL,
'INFO — verify usage and that the key is backed up'
FROM sys.asymmetric_keys AS ak
WHERE ak.name NOT LIKE '##%'
UNION ALL
-- Open symmetric keys in current session
SELECT
CAST('OPEN_SYMMETRIC_KEY' AS NVARCHAR(30)),
ok.key_name COLLATE DATABASE_DEFAULT,
ok.database_name COLLATE DATABASE_DEFAULT,
'OPEN_IN_SESSION',
NULL,
NULL,
NULL,
NULL,
NULL,
'Symmetric key currently open in session',
NULL,
CASE WHEN ok.key_name LIKE '##%'
THEN 'INFO — internal system key'
ELSE 'WARN — symmetric key is open; verify it is required for current operation'
END
FROM sys.openkeys AS ok
) AS all_keys
ORDER BY
CASE WHEN status LIKE 'CRITICAL%' THEN 1
WHEN status LIKE 'WARN%' THEN 2
WHEN status LIKE 'INFO%' THEN 3
ELSE 4 END,
days_until_expiry,
cert_or_key_name;
3. Get-TdeStatus — Encryption State Across Every Database
/*
Script Name : Get-TdeStatus
Category : security
Purpose : Transparent Data Encryption (TDE) status across all databases. Includes
encryption state, key algorithm, encryptor type, and tempdb encryption
side-effect awareness.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-certificates-keys-and-tde-status/)
Requires : VIEW ANY DATABASE, VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-certificates-keys-and-tde-status/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
d.name AS database_name,
d.is_encrypted AS tde_enabled,
ISNULL(ek.encryption_state_desc, 'UNENCRYPTED') AS encryption_state,
ek.key_algorithm,
ek.key_length,
ek.encryptor_type,
ek.encryptor_thumbprint,
ek.percent_complete,
ek.create_date AS key_create_date,
ek.set_date AS key_set_date,
ek.regenerate_date AS key_regenerate_date,
c.name AS certificate_name,
c.expiry_date AS certificate_expiry,
CASE
WHEN d.database_id = 2 AND d.is_encrypted = 1
THEN 'INFO — TempDB is encrypted because at least one user database uses TDE'
WHEN ek.encryption_state = 3 AND d.is_encrypted = 1
THEN 'OK — encrypted'
WHEN ek.encryption_state IN (2, 4)
THEN 'INFO — encryption/key-change in progress (' + CAST(ek.percent_complete AS VARCHAR) + '% complete)'
WHEN ek.encryption_state = 5
THEN 'INFO — decryption in progress'
WHEN d.is_encrypted = 0 AND d.database_id NOT IN (1,2,3,4)
THEN 'INFO — not encrypted'
ELSE 'OK'
END AS status
FROM sys.databases AS d
LEFT JOIN sys.dm_database_encryption_keys AS ek
ON ek.database_id = d.database_id
LEFT JOIN master.sys.certificates AS c
ON c.thumbprint = ek.encryptor_thumbprint
WHERE d.database_id NOT IN (3) -- exclude model; include tempdb to show side-effect
ORDER BY
d.is_encrypted DESC,
d.name;
How To Run From The Repo
Clone DBA Tools, initialize and run whichever script answers your question:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Every certificate across server and user databases, with expiry:
.\run.ps1 Get-CertificateExpiryWarnings
# Certificates and keys with usage detection and risk flags:
.\run.ps1 Get-CertificatesAndKeys
# TDE encryption state across every database:
.\run.ps1 Get-TdeStatus
# Any of these against a remote sql server:
.\run.ps1 Get-TdeStatus -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/security/encryption/Get-CertificateExpiryWarnings.sqlsql/security/encryption/Get-CertificatesAndKeys.sqlsql/security/encryption/Get-TdeStatus.sql
Example Output
Real output from this lab instance, not staged, and it lines up exactly with the TDE finding from Edition Feature Usage earlier in this series.
Get-CertificateExpiryWarnings (2 rows, both healthy):
| cert_scope | cert_name | subject |
|---|---|---|
| master | TdeDemoCert | TDE demo cert for blog post (sqldba.blog) |
| master | BackupEncryptionDemoCert | Backup encryption demo cert for blog post (sqldba.blog) |
Get-CertificatesAndKeys (2 rows):
| object_type | cert_or_key_name | key_protection |
|---|---|---|
| CERTIFICATE | TdeDemoCert | ENCRYPTED_BY_MASTER_KEY |
| CERTIFICATE | BackupEncryptionDemoCert | ENCRYPTED_BY_MASTER_KEY |
Get-TdeStatus (3 encrypted databases out of 23 total):
| database_name | tde_enabled | encryption_state | certificate_name | certificate_expiry | status |
|---|---|---|---|---|---|
| GrowthLab | True | ENCRYPTED | TdeDemoCert | 25/07/2027 | OK — encrypted |
| tempdb | True | ENCRYPTED | INFO — TempDB is encrypted because at least one user database uses TDE | ||
| WatchtowerMetrics | True | ENCRYPTED | TdeDemoCert | 25/07/2027 | OK — encrypted |
Understanding the Results
- expiry_status = CRITICAL or EXPIRED — the highest-priority finding either script can return, an expired TDE certificate blocks restoring that database anywhere else; back up the certificate and its private key immediately if you haven’t already, before renewing
- tempdb showing ENCRYPTED with no certificate_name — expected, tempdb’s encryption is a side effect of any user database using TDE on the instance, not something configured on tempdb directly
- used_for = “Unidentified — review usage manually” — worth chasing down specifically; an unattributed certificate in production is a genuine audit gap
- pvt_key_last_backup NULL on a TDE certificate — a real risk, the certificate’s private key has apparently never been backed up, meaning the TDE database can only ever be restored on this exact server
Best Practices
- Back up every certificate and its private key immediately after creating it (
BACKUP CERTIFICATE ... WITH PRIVATE KEY), store the backup somewhere other than the server it protects - Set a calendar reminder well ahead of any TDE or Service Broker certificate’s expiry, don’t rely on discovering it during a DR drill
- Investigate every “Unidentified” or unattributed key rather than leaving it as a permanent open question
- Cross-reference TDE findings here with Edition Feature Usage before any edition downgrade, TDE is Enterprise-only and will block a downgrade outright
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
Does an expired TDE certificate stop the database from working?
Not on the server that currently holds it, TDE keeps working using the encryption key already in memory and cached locally. The real risk is restoring that database anywhere else (a new server, a DR failover, a migration), which requires the exact certificate and private key that encrypted it, expired or not.
Why is tempdb encrypted when I never touched it directly?
Enabling TDE on any single user database encrypts tempdb automatically, since tempdb temporarily holds working data from every database on the instance during query execution. This is expected SQL Server behavior, not a separate configuration step or a bug.
Summary
Certificates and keys back TDE, Service Broker, and column encryption, and they all share the same failure mode: nobody notices until something expires or a restore fails on the wrong server. These three scripts turn that blind spot into a routine, readable check, what exists, what it’s for, how long until it expires, and whether the private key is actually backed up.
Run them as part of any encryption or security review, and treat an expiring TDE certificate as urgent, the failure only shows up somewhere else, at the worst possible time.
Leave a Reply