If you don’t have the backups of the certificate and private key from the old server, as well as the password used to encrypt the private key backup then you could be in a lot of trouble. There is one scenario where you have a way out. I’m going to assume you don’t have the possibility to recover your old server from a complete file system backup – if you do then you can do that and access all the keys you require. If the two following things are true though then you can still recover your database:

• You have a backup of the master database from the previous instance.
• The previous instance used a domain account as its service account.

The reason you are going to be okay is that all the objects in the SQL Server Encryption Hierarchy that sit above the Database Encryption Key (that exists in your TDE database) are stored in the master database. That includes the certificate and associated keys, the Database Master Key (DMK) and the Service Master Key (SMK). There are two copies of the SMK:

• One encrypted by the keys associated with the machine account.
• Once encrypted by the keys associated with the SQL Server service account.

The first copy is only going to be of any use to us if we can recover the old machine (and its account) directly from backups, but we’ve already ruled that out.

If the service account is a domain account though then we should be able to use it. The method is going to involve:

• Setting up a new SQL instance using the same service account as the old instance.
• Restore your backup of master from the old instance onto the new instance.
• Reboot your new server – that’s the whole server, not just SQL.
• Backup your certificate and private key – and don’t lose them this time!

My personal opinion is that it’s not the greatest of ideas to restore the master database from one instance onto a new one and expect everything to work okay. So, I’m only suggesting you use this so you can recover the certificate. Once you’ve got that, I would go back to the steps in the previous post on recovering your TDE protected database(s).

Let’s go into each of these steps in a little more detail.

Setting up a new SQL instance using the same service account as the old instance

What this obviously means is that your server must be on the same domain as the old server (or at least another domain that is trusted). You also must have the credentials for the service account.

You can’t fake this, for example setting up a new account on another domain called the same thing as the old one. The new account won’t have the same keys associated with it as the ones used to encrypt your SMK, so you will achieve nothing.

Restore your backup of master from the old instance onto the new instance

There are a lot of resources available on the internet that tell you how to do this in detail and will give you a number of methods you can use. In short you need to first stop your new SQL Server instance and then from a command prompt start it in single user mode with the following command:

sqlservr.exe -c -m -s {InstanceName}

Then you need to (again from a command line) issue the command to restore/overwrite the master database. First start SQLCMD with this command:

sqlcmd -s {InstanceName}

Then at the prompt that opens up within your command window you can execute the following SQL:

RESTORE DATABASE master FROM DISK = ‘C:\Test\master.bak’ WITH REPLACE;
GO

Reboot your new server – the whole server, not just SQL

If you restart the SQL Server service, rather than the machine itself, you can still go in and everything looks okay. You can even restore a TDE database from your old instance and you’ll find you can access the data.

Everything is not okay though, and if you tried to backup your certificate and private key you would get an error like the following:

Msg 15151, Level 16, State 1, Line 7
Cannot find the certificate ‘MyTDECert’, because it does not exist or you do not have permission.

The reason for this error is that the SMK isn’t in the correct state. The copy that is encrypted by the service account is fine, but the copy that is encrypted by the machine account is currently using the wrong machine account. You need to reboot the whole server to fix this, just restarting SQL doesn’t do it. On a full restart the SMK is retrieved from the copy encrypted by the service account, and then encrypted with the current machine account. That version then replaces the one using the wrong machine account.

Once that’s done the encryption hierarchy is fully fixed, and the certificate becomes accessible for a backup command.

Backup your certificate and private key – and don’t lose them this time

I’ve given the command to backup these a few times, but here it is again:

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

You can now take those backup files and use them to restore the certificate and key to the SQL Server instance of your choice, and then restore the backups of your TDE protected database(s).

This has been mentioned a few times, but I’ll re-iterate once more as it is the most important issue to consider when managing TDE. Making sure you don’t lose these backups – or the password – is a vital consideration. If you’re responsible for setting up any form of encryption you need to think about the process that’s going to manage the objects used to protect your data. People move from one role to another, from one company to another, and often things tick along happily for many years before a failure happens. You need to be confident that come next year, or in five or ten years, whoever is responsible for the data will be able to recover it if the worst happens.

This post is part of a comprehensive series on encryption in SQL Server. If you want it all in one go you can buy my book:

http://www.dpbolvw.net/click-100730415-12898971?url=https%3A%2F%2Flink.springer.com%2Fbook%2F10.1007%2F978-1-4842-8664-7

If this post has helped you, consider buying me a coffee to say thanks.