Restoring Encrypted SQL Server DB Backup on different Server

Last week, when I was tasked with moving old server to new server, I was faced with an error that I wasn't familiar. I got this error while I was trying to restore a database backup from old SQL server to new SQL server. Error message told me that database is encrypted and there fore I cannot restore on the new server.

Further analysis showed me that on the old server database was encrypted using TDE.

As a side note, TDE is a security feature in SQL Server that encrypts the database at rest (when it saved on disk). It protects data files by encrypting them on disk, ensuring that even if someone gains access to the database files, they cannot read the data without the proper decryption keys. When a database use TDE, backup files also encrypted.

Above illustration from Microsoft Learn website shows the architecture of the TDE.

Therefore, the first thing we need when restoring this encrypted database on the new server is Database Master Key (DMK).

You can do this my running following TSQL on the new server:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword';

Next, we need certificates. However, creating new certificate on new server won't allow us to decrypt the backup from old server. There fore we need certificate from old server. To be precise, you need backups of the certificate that encrypted the Database Encryption Key of the database, so you can restore it on new server. 

That is why it is vital that you backup your certificates in a safe place (e.g. online data store or vault) right after you create them. Because if your server get crashed, you will never able to restore databases even you have backups of them, without the certificate (if they are TDE encrypted).

Assume you don't have the certificate backed up (or you don't know where they are because some one else has done them and that knowledge is not available to you now). In that case if you still have access to the old server (like in my case above), you can still generate the certificate backup.

First, you need to know the name of the certificate. To get the name of the certificate you can run following TSQL:

SELECT db_name(database_id) AS DatabaseName, c.name AS CertificateName

FROM sys.dm_database_encryption_keys dek

JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint;

Now you know the name, you can back it up:

USE master;

BACKUP CERTIFICATE MyBackupCert 

TO FILE = 'C:\Backup\MyBackupCert.cer'  

WITH PRIVATE KEY (

    FILE = 'C:\Backup\MyBackupCert.pvk',

    ENCRYPTION BY PASSWORD = 'StrongPassword123'

);

Note that the password you use to generate the master key and this doesn't need to match.

Above query will generate two files:

• Certificate file (with cer extension)
• Private Key file (with pvk extension)

Move them to the new server, and also make sure you have noted down the password you used.

Then, create the certificate on the new server:

USE master;

CREATE CERTIFICATE MyBackupCert

FROM FILE = 'C:\Backup\MyBackupCert.cer'

WITH PRIVATE KEY (

    FILE = 'C:\Backup\MyBackupCert.pvk',

    DECRYPTION BY PASSWORD = 'StrongPassword123'

);

Note that you need to use the same password you use to backup the certificate and private key file.

When you restore the certificate from above TSQL, it get encrypted with the master database key of the new server, there fore complete the chain that showed in the TDE architecture diagram.

Now you are ready to restore your database to new server.