Sev1: restore fails with 'Cannot find server certificate with thumbprint'. Recovering TDE databases

#Symptom

Msg 33111, Level 16, State 3
Cannot find server certificate with thumbprint '0x8A5B...'.
RESTORE DATABASE is terminating abnormally.

Or after rebuilding a server, attaching the data files fails the same way. The database encryption key (DEK) is protected by a certificate in the source server's master database, and the target server doesn't have it.

#Step 1: which certificate is needed?

From the backup file:

RESTORE FILELISTONLY FROM DISK = N'B:\Backup\SalesDB.bak';   -- fails, but the error shows the thumbprint

If the source server is still up:

SELECT DB_NAME(k.database_id) AS db, c.name AS cert_name, c.thumbprint, c.expiry_date
FROM sys.dm_database_encryption_keys k
JOIN master.sys.certificates c ON c.thumbprint = k.encryptor_thumbprint;

#Step 2: get the certificate and private key

You need the .cer and private key .pvk files plus the password used to encrypt the private key backup. Look in:

• The certificate backup location in your runbook or password vault.
• The source server, if it's still available. Back the certificate up now:

USE master;
BACKUP CERTIFICATE TDE_Cert TO FILE = N'C:\Secure\TDE_Cert.cer'
  WITH PRIVATE KEY (FILE = N'C:\Secure\TDE_Cert.pvk', ENCRYPTION BY PASSWORD = N'<strong password>');

• Old server backups of master: restoring master from the old server on a scratch instance brings the certificate back, and you can back it up from there.

Caution

If no copy of the certificate with its private key exists anywhere, the TDE-encrypted data in those backups cannot be recovered. There is no back door. Stop and check every possible location (old VMs, snapshots, old master backups) before declaring it lost.

#Step 3: create it on the target server

USE master;
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<target master key password>';

CREATE CERTIFICATE TDE_Cert
  FROM FILE = N'C:\Secure\TDE_Cert.cer'
  WITH PRIVATE KEY (FILE = N'C:\Secure\TDE_Cert.pvk', DECRYPTION BY PASSWORD = N'<password used at backup>');

The SQL Server service account needs read access to the files. Verify the thumbprint matches the one in the error:

SELECT name, thumbprint, expiry_date FROM master.sys.certificates WHERE name = 'TDE_Cert';

#Step 4: restore

RESTORE DATABASE SalesDB FROM DISK = N'B:\Backup\SalesDB.bak'
  WITH MOVE N'SalesDB' TO N'D:\Data\SalesDB.mdf', MOVE N'SalesDB_log' TO N'L:\Log\SalesDB_log.ldf', RECOVERY;

Log backups need the certificate too. If the certificate was rotated during the backup chain, you need every certificate used across the chain.

#Special cases

#Prevent it next time

• Back up every TDE certificate with its private key at creation and after every rotation, to at least two secure places (vault + offline).
• Store the private-key password in the password vault next to the file reference.
• Quarterly test: restore a TDE database on a scratch server using only the stored backups.
• Inventory query in your health check:

SELECT c.name, c.thumbprint, c.pvt_key_last_backup_date, c.expiry_date
FROM master.sys.certificates c
WHERE c.thumbprint IN (SELECT encryptor_thumbprint FROM sys.dm_database_encryption_keys);

pvt_key_last_backup_date NULL means the private key has never been backed up. Fix that today.