TDE Restore Fails: Cannot Find Server Certificate (Error 33111)

You are restoring a TDE database on a different server, or on a rebuilt one, and the restore stops at Msg 33111. The backup is fine. This server is missing the certificate that protects the database’s encryption key, so it cannot read a single page. Create that certificate in master from its backup files and the same restore goes through.

It is followed by Msg 3013, RESTORE DATABASE is terminating abnormally, which adds nothing. The thumbprint in your message will be different, and it is the one part of the message you need.

Find the Certificate the Backup Needs

A TDE backup names its certificate by thumbprint, not by name. Paste the thumbprint from your message into this query on the server you are restoring to:

-- Paste the thumbprint from the 33111 message, no quotes
SELECT name, subject, thumbprint, pvt_key_encryption_type_desc, expiry_date
FROM master.sys.certificates
WHERE thumbprint = 0x1E42F5974A21BC7B9F5DF393D072C9D5FD61705F;

No row means the certificate is not on this server, which is the usual case. Checking the backup file will not get you further: RESTORE FILELISTONLY and RESTORE VERIFYONLY stop at the same error, because both have to read inside the encrypted backup.

RESTORE HEADERONLY still works, and its ServerName and DatabaseName columns tell you which server to ask for the certificate. Ignore its EncryptorThumbprint column, which read NULL on this TDE backup: it belongs to backup encryption, a separate feature (RESTORE HEADERONLY on MS Docs).

Get the Certificate From the Source Server

If somebody already has the certificate backup, the .cer file, the .pvk private key file and the password that protects it, skip to the next section. If the source server is still running, this shows which certificate protects each TDE database there:

-- Run on the SOURCE server
SELECT DB_NAME(k.database_id) AS database_name,
       k.encryption_state_desc,          -- 2019 and later; use encryption_state on older builds
       c.name AS certificate_name,
       k.encryptor_thumbprint,
       c.pvt_key_last_backup_date
FROM sys.dm_database_encryption_keys AS k
LEFT JOIN master.sys.certificates AS c ON c.thumbprint = k.encryptor_thumbprint
WHERE k.database_id > 4;

Then back the certificate up with its private key. The private key is the part that matters: without it the certificate is only a public key, and the restore still fails (see below).

USE master;
BACKUP CERTIFICATE TDECert
TO FILE = 'D:\CertBackup\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\CertBackup\TDECert.pvk',
    ENCRYPTION BY PASSWORD = '<a strong password you will store safely>'
);

Copy both files to the target server, somewhere the SQL Server service account can read, and keep the password with them. The syntax is on BACKUP CERTIFICATE on MS Docs.

Create the Certificate on This Server, Then Restore

The certificate goes in master on the target, and master needs a database master key to hold its private key. Check for one first:

USE master;
SELECT name FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

-- No row? Create one. This is a NEW password for this server, it does not have to match the source.
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<a strong password you will store safely>';

Then create the certificate from the two files. The password here is the one used when the certificate was backed up, not the master key password:

USE master;
CREATE CERTIFICATE TDECert
FROM FILE = 'D:\CertBackup\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\CertBackup\TDECert.pvk',
    DECRYPTION BY PASSWORD = '<the password used in BACKUP CERTIFICATE>'
);

-- Confirm the thumbprint now matches the one in the 33111 message
SELECT name, thumbprint, pvt_key_encryption_type_desc
FROM sys.certificates
WHERE thumbprint = 0x1E42F5974A21BC7B9F5DF393D072C9D5FD61705F;

pvt_key_encryption_type_desc should read ENCRYPTED_BY_MASTER_KEY. Now run the same RESTORE DATABASE statement that failed, unchanged. Here the certificate was created under a different name from the source, and the restore still worked, because only the thumbprint has to match:

The restored database stays encrypted by that certificate, so back the certificate up on this server too. The full move is on MS Docs as move a TDE protected database to another SQL Server, and the file options are on CREATE CERTIFICATE.

When the Certificate Is There and the Restore Still Fails

A certificate without its private key, or a private key that never loaded, gives a different message. Each of these came from creating the certificate the wrong way:

• Msg 15507, “A key required by this operation appears to be corrupted.” The certificate was created from the .cer file alone. The thumbprint matches, pvt_key_encryption_type_desc reads NO_PRIVATE_KEY, and the restore fails. Drop it and create it again with the .pvk file. Nothing is corrupt.
• Msg 15465, “The private key password is invalid.” The DECRYPTION BY PASSWORD is not the one used in BACKUP CERTIFICATE. The certificate is not created at all, so run it again with the right password.
• Msg 15581, “Please create a master key in the database or open the master key in the session before performing this operation.” There is no database master key where you ran CREATE CERTIFICATE. Create one in master first (CREATE MASTER KEY on MS Docs). Reproduced in a user database with no master key rather than in master, which already had one.

The 15507 case is the one that wastes time, because the thumbprint check passes:

If the master key itself is the problem, a wrong password or a key the service master key no longer opens, the database master keys post covers those messages, including 15466.

Before the Next Restore Needs It

• Back up every TDE certificate with its private key the day TDE goes on, and keep the files and the password off the server they protect. MS Docs on TDE is plain about it: without those backups, a database restored or attached on another server cannot be opened.
• Keep the old certificate after rotating the protector or turning TDE off. MS Docs says parts of the log can stay protected by it, and backups taken before the change still need it.
• Create the certificate on DR and test servers before the day you need them, and do a test restore there.
• Audit which certificate protects which database, and when its private key was last exported, with the certificates, keys and TDE status scripts.

Frequently Asked Questions