Quick Scan Report – TDE Certificate Backups

What this check looks for

Certificates in master.sys.certificates that are joined to a database encryption key in sys.dm_database_encryption_keys, meaning they are protecting a TDE encrypted database, whose pvt_key_last_backup_date is null or is old.

A null means the private key has never been backed up.

This page also covers issue 232, Missed DB TDE encryption, which is the related finding about encryption coverage.

Why it matters

Without the certificate, a backup of a TDE encrypted database is an encrypted file nobody can open. Including you.

That is the whole of it, and it is worth stating plainly because the failure mode is so complete. Transparent Data Encryption encrypts the database files and, critically, the backups. Restoring an encrypted backup onto any instance requires the certificate that protected it. Not a copy of the database, not a password: that certificate and its private key.

So the situation this check describes is:

• The backups exist. Every backup report says the database is protected.
• They restore fine on this server, because the certificate is here, which is what makes routine restore tests pass and hide the problem.
• They restore nowhere else. Not on a DR server, not on a rebuilt server, not on a new server after the old one is gone.

Which means the one scenario TDE backups have to survive is the one they do not. If the server is lost, the certificate in its master database is lost with it, and every backup of every encrypted database on it becomes permanently unreadable. There is no recovery path, no support case and no tool. The data is gone.

This is one of the few findings in this report where the consequence is total and irreversible, and the fix takes about two minutes.

How to confirm it yourself

USE [master];
GO

SELECT c.[name]                        AS [certificate],
       c.[certificate_id],
       c.[subject],
       c.[expiry_date],
       c.[pvt_key_last_backup_date],
       DB_NAME(dek.[database_id])      AS [encrypted_database],
       dek.[encryption_state],
       CASE dek.[encryption_state]
            WHEN 0 THEN 'no 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'
            ELSE 'unknown' END         AS [state]
  FROM sys.certificates AS c WITH (NOLOCK)
  LEFT JOIN sys.dm_database_encryption_keys AS dek WITH (NOLOCK)
         ON dek.[encryptor_thumbprint] = c.[thumbprint]
 ORDER BY c.[pvt_key_last_backup_date];

A null pvt_key_last_backup_date on a row with an encrypted database is the finding.

Check the service master key and database master key too, because the chain needs all of them:

SELECT [name], [create_date], [modify_date]
  FROM master.sys.symmetric_keys WITH (NOLOCK)
 WHERE [name] = '##MS_DatabaseMasterKey##';

And confirm which databases are actually encrypted:

SELECT DB_NAME([database_id]) AS [database_name], [encryption_state], [percent_complete]
  FROM sys.dm_database_encryption_keys WITH (NOLOCK);

Note that tempdb appears here as encrypted whenever any database on the instance is, which is expected rather than a finding.

How to fix it

Back up the certificate and its private key, now. Two minutes, no downtime, no locks:

USE [master];
GO

BACKUP CERTIFICATE [YourTDECert]
    TO FILE = N'\\secure-location\certs\YourTDECert.cer'
    WITH PRIVATE KEY (
        FILE = N'\\secure-location\certs\YourTDECert.pvk',
        ENCRYPTION BY PASSWORD = 'a strong password you will not lose'
    );

Back up the database master key as well, because restoring the certificate needs it:

USE [master];
GO
BACKUP MASTER KEY
    TO FILE = N'\\secure-location\certs\master_key.key'
    ENCRYPTION BY PASSWORD = 'a different strong password';

Then the part people get wrong. Store the files and the passwords properly:

• Not on the server being protected. A certificate backup on the machine whose loss it guards against is not a backup.
• Not next to the database backups either, because whoever gets the backups then has the key as well, which defeats the encryption.
• In your password manager or key vault, with the passwords, and with a note saying what they unlock. A certificate file whose password nobody remembers is as lost as no file at all.
• Tested. Restore the certificate onto a different instance and restore one encrypted database backup there. This is the only thing that proves the whole chain works, and it is the step almost nobody does.

Re-run the backup whenever the certificate changes, and note that pvt_key_last_backup_date updates when you do, so this check clears itself.

If the certificate is approaching its expiry date, plan the rotation. An expired TDE certificate does not stop the database working, and it does complicate the situation, so it is better handled deliberately.

How long it takes

About half an hour, including storing the files somewhere sensible. The restore test on another instance is worth another hour and is the part that turns this from a task into an assurance.

Related reports

Related checks

Frequently asked questions

We back up master. Is that enough? It contains the certificate, and restoring master onto different hardware is a far more awkward operation than restoring a certificate file. Back up the certificate explicitly; it takes two minutes.

Can we restore an encrypted backup without the certificate? No. There is no recovery path. That is the point of the encryption.

We have not enabled TDE. Then this check does not fire, because it only looks at certificates joined to a database encryption key.

How often should we back it up? Once, and again whenever it changes. The check reports “not recently” because a very old backup is worth confirming still exists and still has a known password.