Avoid This Mistake When Restoring a TDE Protected Database to a New Server

While working on a proof-of-concept implementation of Transparent Data Encryption (TDE), 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:

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:

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:

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:

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.