Avoid This Mistake When Restoring a TDE Protected Database to a New Server
Tags: sql, tde, restore, backup
Published: 2020-05-03 13:59:50

While working on a proof-of-concept implementation of [Transparent Data Encryption (TDE)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption), I discovered my SQL Server database would be unavailable and go into "Recover Pending" status after a server restart. I learned the reason for this behavior was due to a misunderstanding I had about the prerequisites for restoring a TDE encrypted database. This post focuses on my missed step and describes the correct way to restore a TDE protected database to a new server. 

### Some Background

A prescribed best practice when using TDE is to back up the master key and certificate used to encrypt the database and store them in a safe location. Encrypted databases cannot be recovered to a different server without the necessary keys. I wanted to be sure I understood how this recovery process worked and created a POC for testing. 

I dutifully followed this best practice and backed up my database master key and certificate. I created a separate SQL Server instance where I would restore my POC database. In order to restore the database to a new instance, the destination SQL Server had to have a master key and the certificate. In my new instance, I restored the master key and certificate from the backups I took from my source instance:
```sql
RESTORE MASTER KEY   
    FROM FILE = 'c:\POCMasterKey.key'   
    DECRYPTION BY PASSWORD = 'password'
	ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE POCServerCert 
   FROM FILE = 'C:\POCServerCert.cer'
   WITH PRIVATE KEY(
      FILE ='C:\POCServerCertKey.key', 
      DECRYPTION BY PASSWORD='password'
   )
```
This step created a new master key and installed my certificate on the destination instance. The next step was to restore the database. I did so by issuing the following SQL statements:
```sql
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'  

RESTORE DATABASE <poc_dbname>
   FROM DISK = N'poc_dbname_backup.bak' 
```
A few minutes later and the database was restored on the destination instance. I executed some queries to ensure I had access to the data. After a few tests, I was satisfied that the restoration process was a success.

### Trouble Starts
After restarting the server, I noticed that the POC database would go into Recovery Pending status and was unavailable. I surmised the issue was something related to TDE. I found a workaround where I could bring the database back online if I opened the master key and executed an `ALTER DATABASE` statement, like so:
```sql
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'  

ALTER DATABASE <poc_dbname> SET ONLINE
```
I didn't want to have to do this every time the database was restarted. I knew I missed a crucial step somewhere in the restoration process. I quickly came to realize the issue was with the master key.

### Where I Went Wrong (and how you can avoid it)
When I created the master key on the destination instance I did so by using the source instance's key backup. What I didn't realize was the master key should not be created from the source instance's backup master key. Instead, it must be created new on the destination instance. Once I figured this out, I started over. I deleted the certificate and master key in the destination instance. Then I created a new master key using:
```sql
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
```
I restored my certificate from the source's backup file, opened the master key in the destination instance, and restored the database from backup. Once the database was restored, I restarted the server to ensure the database would not go into recovery status. I am delighted to report that it didn't, and I no longer need to manually set the database to online status after server restarts.

The step I missed was subtle but crucial. Using the source instance's master key led to issues accessing my destination instance. Now I know the proper way to restore a TDE protected database to a new instance. I believe my POC saved me from a potential disaster in a production environment.