When encrypting a database with Transparent Data Encryption (TDE), a vital consideration is to make sure we are prepared for the scenario where something goes wrong. For instance, if the server hosting our SQL instance goes belly-up, can we recover the data that we have encrypted with TDE?

In the ordinary recovery scenario, we would make sure that we have appropriate backups of our database, and that they (or copies of them) are stored off the server itself so that we can access them in case of a failure.

If you have followed the instructions in the previous posts, then you will also have taken a backup of the certificate and private key via the following command:

BACKUP CERTIFICATE MyTDECert  
TO FILE = 'C:\Test\MyTDECert.cer' 
WITH PRIVATE KEY  
( 
    FILE = 'C:\Test\MyTDECert_PrivateKeyFile.pvk', 
    ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7' 
);

You need to make sure that these are also stored securely off the server and that you have kept the password you used somewhere you can access it – but not so accessible that unauthorized users can read it.

In summary you need:

• The database backup file
• The backup of the certificate
• The backup of the private key
• The password used to encrypt the private key

Armed with those objects, you are equipped to restore your database to another SQL Instance. Working on the new SQL instance, the steps are straightforward.

Create a Database Master Key (DMK) if One Doesn’t Exist

The new SQL instance will need a DMK if one doesn’t already exist. You can create one with the following code:

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHere!£$7';

Note that this will be a new and unique DMK, it will not be the same as the one you had on your old instance – and you don’t need to use the same password to protect it.

Restore the Certificate and Private Key

On the new SQL instance, you need to restore the certificate and private key into the master database with the following SQL:

USE master;
CREATE CERTIFICATE MyTDECert
FROM FILE = ‘C:\Test\MyTDECert.cer’
WITH PRIVATE KEY
(
   FILE = ‘C:\Test\MyTDECert_PrivateKeyFile.pvk’,
   DECRYPTION BY PASSWORD = ‘UseAStrongPasswordHereToo!£$7’
);

This will decrypt your key using the password supplied, and then re-encrypt it using the DMK you created. Then the certificate and its key will be stored in the master database on your new SQL instance.

If you’ve done something wrong, it’s entirely possible you may get an error at this stage, commonly:
Msg 15208, Level 16, State 6, Line 56
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

If you’re confident that all details specified are correct, and that the certificate and private key were backed up properly, then the most likely issue is that the current SQL instance doesn’t have access to the file path you’ve placed the files in.

Another possible issue is that you receive an error telling you the certificate already exists. If you have checked and verified that a certificate with the same name doesn’t exist, then the most probable cause is that you have another certificate with the same thumbprint. The thumbprint is the actual identifier for the certificate (the name we give it is more of a friendly name).

Restore the Database

Once you’ve completed the previous steps you are ready to restore the database from the backup. You do that as you would restore any other database. Potentially as simply as with the following command:

RESTORE DATABASE TestTDE FROM DISK = 'C:\Test\TestTDE.bak';

Then you’ll find you can access your database and view data without any issues. At this point you can celebrate – you are done. You only get a problem if you haven’t set up the certificate and key correctly, or you have the wrong one, in which case you get an error like the following:

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ‘0x682C8797633B9AD8875967502861CCAE33ECAD66’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

In the next post we’ll look at one case where you “may” be able to recover your TDE database if you’re in the unfortunate position of not having the certificate and private key from the old SQL Server instance.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.