If you’ve been careful and done everything right when you’re setting up TDE then you shouldn’t run into this problem.
We all make mistakes though, and we’ve all been asked to deal with environments that haven’t been so carefully managed.
But what if you do? You have access to the backups for one or more TDE protected databases, but you don’t have the certificate and private key backups – or you don’t have the password to decrypt them.
I’m assuming here that you also can’t simply recover your old server from a complete file system backup. Obviously if you can do that then you are going to be fine.
If the two following things are true, 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 is until we get right to the top, the Service Master Key (SMK) which exists in the master database is itself encrypted. There are two copies of it:
- One encrypted by the machine account
- Once encrypted by 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) direct 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
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 simple 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 for recovering your TDE protected database(s).
- 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!
That’s fairly straightforward, but let’s just go into 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 one’s 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 out there that that tell you how to do this in detail such as Thomas LaRock’s post here:
https://thomaslarock.com/2014/01/restore-the-master-database-in-sql-server-2012/
In short you need to first stop your new SQL Server instance and then from a command prompt start it in single user mode e.g.
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:
C:\> sqlcmd -s {InstanceName}
Then at the prompt that opens up within your command window:
1> RESTORE DATABASE master FROM DISK = ‘C:\Test\master.bak’ WITH REPLACE;
2> GO
Reboot your new server –the whole server, not just SQL
If you restart SQL before doing this, 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:
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' WITH PRIVATE KEY ( FILE = 'C:\Test\MyTDECert_PrivateKeyFile', 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).
Not losing these backups – or the password – is a serious issue. 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.
Other articles about TDE:
What is Transparent Data Encryption?
Setting up Transparent Data Encryption (TDE)
Encrypting an existing database with TDE
Understanding Keys and Certificates with Transparent Data Encryption (TDE)
How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking
