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.
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.
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.
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.