We’ve previously looked at how to implement transparent database encryption and then crucially how we can back up the certificate we’re using to apply the encryption. This time we’ll look at how we restore the certificate and a database backup which has been encrypted with Transparent Database Encryption (TDE).
When we implement TDE on a SQL Server database this will automatically encrypt any future backups with the same certificate. This isn’t an issue restoring into the same environment, however if you attempt to restore onto another server (which doesn’t have that certificate) then you’ll run into issues.
Lets try a restore
We’ll use a backup of the database we created in a previous post and try to restore that onto a different instance and let’s see what happens:
RESTORE DATABASE Blog
FROM DISK = 'D:\SQLServer\Backup\Blog.bak'
WITH RECOVERY;
Cannot find server certificate with thumbprint ‘0xC1BF38E683E0F560167FB2A4D45E0473BF322308’.
RESTORE DATABASE is terminating abnormally.
Ugh, that’s a bit of an issue. Not to worry, this is what we’d expect. We don’t want anyone to be restoring encrypted databases on any old instance right?
Restoring the certificate
So without that certificate we’re not making any progress, firstly we need to get that restored so we can use the backup.
Just like creating a new certificate we’ll also need to have a Database Master Key before we can restore an existing one. Lets go and add one of those as we did previously:
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '0th3rStr0ngPassw0rdHere';
GO
You’ll see that this isn’t necessarily the same password which we used before. As this key is only used to encrypt the certificates it doesn’t matter what it is, just that it’s present in the instance.
Once that’s created we can then move on to restoring the certificate. We’ll need to provide the file path to the certificate, private key and password – all of which were generated in the previous post. With those to hand we’ll restore the certificate as follows:
CREATE CERTIFICATE [MyFirstCertificateRestore]
FROM FILE = 'D:\KeysAndCertificates\MyFirstCertificate.cer'
WITH PRIVATE KEY (FILE = 'D:\KeysAndCertificates\MyFirstCertificate.pvk',
DECRYPTION BY PASSWORD = '<UseStrongPasswordHere>');
You’ll see that although we’re using the same certificate files here the name of the certificate is irrelevant for restoring a database. So long as its the same certificate that was used to encrypt the backup all that’s needed is a matching thumbprint on the environment.
Restore redux
With the certificate in place we can loop around to the backup file which we started with.
As I’m using multiple instances on the same machine I’ll be using the MOVE option here to put the database files into a different location than where they originated, but otherwise it’s a straightforward restore:
RESTORE DATABASE Blog
FROM DISK = 'D:\_Temp\Blog.bak'
WITH RECOVERY,
MOVE 'Blog' TO 'D:\SQLServer\SQL2017\Data\Blog.mdf',
MOVE 'Blog_Log' TO 'D:\SQLServer\SQL2017\Data\Blog_log.ldf';
This time when executing the restore it’s successful and we’ve got the database restored into the new environment ready to use.
Wrap up
After we’d previously looked at how the encryption works, implemented TDE and then backed up the certificate it was natural to follow up seeing how we go about restoring that data.
As we’ve seen, so long as the certificate is common between the backup and restore environments there’s no issue in getting the data back into place, however if anyone takes our backups without access to the relevant certificate the file is all but useless to them.
I think it’s worth pointing out that although this may be a solution to help restore production backups into a development or test environment you’ll likely want to change the certificate used for encryption (so we can drop the production certificate) and may want to look at anonymising or removing any sensitive data before handing over to your dev teams (there was a reason it’s encrypted, right?).
Andy Brownsword 