Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Recovering a SQL Server TDE Encrypted Database Successfully


By:   |   Last Updated: 2015-04-01   |   Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Restore

Problem

I have been tasked with setting up Transparent Data Encryption (TDE) on a SQL Server database with sensitive information. In the examples I've seen, I know I need a database master key in the master database and then a certificate that's encrypted with that master key. A question that comes to mind is that if I choose to restore the database to a different server, does my database master key have to have the same password? Every example I've seen includes the same password. What else do I need to plan for in order to have a successful recovery?

Solution

Configuring a SQL Server database for TDE is a straight-forward process. It consists of:

  1. Creating the database master key in the master database.
  2. Creating a certificate encrypted by that key.
  3. Backing up the certificate and the certificate's private key. While this isn't required to encrypt the database, you want to do this immediately.
  4. Creating a database encryption key in the database that's encrypted by the certificate.
  5. Altering the database to turn encryption on.

If you're reading this tip and aren't familiar with the edition requirements, TDE is only available on Enterprise Edition versions of Microsoft SQL Server.

Create a New SQL Server Database using TDE

Let's set up an example database with the following code:

USE [master];
GO 

-- Create the database master key
-- to encrypt the certificate
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!';
GO 

-- Create the certificate we're going to use for TDE
CREATE CERTIFICATE TDECert
  WITH SUBJECT = 'TDE Cert for Test';
GO 

-- Back up the certificate and its private key
-- Remember the password!
BACKUP CERTIFICATE TDECert
  TO FILE = N'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

-- Create our test database
CREATE DATABASE [RecoveryWithTDE];
GO 

-- Create the DEK so we can turn on encryption
USE [RecoveryWithTDE];
GO 

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO 

-- Exit out of the database. If we have an active 
-- connection, encryption won't complete.
USE [master];
GO 

-- Turn on TDE
ALTER DATABASE [RecoveryWithTDE]
  SET ENCRYPTION ON;
GO 

This starts the encryption process on the database. Note the password I specified for the database master key. As is implied, when we go to do the restore on the second server, I'm going to use a different password. Having the same password is not required, but having the same certificate is. We'll get to that as we look at the "gotchas" in the restore process.

Even on databases that are basically empty, it does take a few seconds to encrypt the database. You can check the status of the encryption with the following query:

-- We're looking for encryption_state = 3
-- Query periodically until you see that state
-- It shouldn't take long
SELECT DB_Name(database_id) AS 'Database', encryption_state 
FROM sys.dm_database_encryption_keys;

As the comments indicate, we're looking for our database to show a state of 3, meaning the encryption is finished. Here's an example of what you should see:

Recovering a TDE Encypted Database Successfully

When the encryption_state shows as 3, you should take a backup of the database, because we'll need it for the restore to the second server (your path may vary):

-- Now backup the database so we can restore it
-- Onto a second server
BACKUP DATABASE [RecoveryWithTDE]
TO DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak';
GO 

Now that we have the backup, let's restore this backup to a different instance of SQL Server.

Failed Restore - No Key, No Certificate

The first scenario for restoring a TDE protected database is the case where we try to do the restore and we have none of the encryption pieces in place. We don't have the database master key and we certainly don't have the certificate. This is why TDE is great. If you don't have these pieces, the restore simply won't work. Let's attempt the restore (note: your paths may be different):

-- Attempt the restore without the certificate installed
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

This will fail. Here's what you should see if you attempt the restore:

Failed Restore - No Key, No Certificate

When SQL Server attempts the restore, it recognizes it needs a certificate, a specific certificate at that. Since the certificate isn't present, the restore fails.

Failed Restore - The Same Certificate Name, But Not the Same Certificate

The second scenario is where the database master key is present and there's a certificate with the same name as the first server (even the same subject), but it wasn't the certificate from the first server. Let's set that up and attempt the restore:

-- Let's create the database master key and a certificate with the same name
-- But not from the files. Note the difference in passwords
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'SecondServerPassw0rd!';
GO 

-- Though this certificate has the same name, the restore won't work
CREATE CERTIFICATE TDECert
  WITH SUBJECT = 'TDE Cert for Test';
GO 

-- Since we don't have the corrected certificate, this will fail, too.
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

Note the difference in the password for the database master key. It's different, but that's not the reason we'll fail with respect to the restore. It's the same problem as the previous case: we don't have the correct certificate. As a result, you'll get the same error as in the previous case.

Failed Restore - The Right Certificate, but Without the Private Key

The next scenario is where you do accomplish the certificate restore, but you don't have the private key. This, too, will fail. However, before you attempt the T-SQL code, you may have to fix the file permissions on the certificate file and the private key file. This is likely if you are attempting to do the restore on a SQL Server instance that's on the same system as the first instance. To ensure your second instance can access the files, go to the location of the files and bring up the file properties (right-click on the file and then select Properties from the pop-up menu).

Click on the Security tab and then click on the Advanced button. When SQL Server wrote these files, it probably broke the inheritance of permissions, and we're going to fix that.

Failed Restore - The Right Certificate, but Without the Private Key

This brings up a different interface and you should be looking at the Permissions tab. If you see it, click on the Continue button. You'll see it if UAC is on.

This brings up a different interface and you should be looking at the Permissions tab.

Now you'll turn on inheritance. Note what I've circled. If the box is unchecked, as it is in following image, that means inheritance is off. If that's the case with your file(s), click to check the box and click OK at every interface to turn inheritance back on.

f that's the case with your file(s), click to check the box and click OK at every interface to turn inheritance back on.

Now let's try to recover the certificate, but intentionally forget to restore with the private key. Before we can create the certificate from the file, we'll have to drop the certificate we just created.

-- Let's drop the certificate and do the restore of it...
-- But without the private key
DROP CERTIFICATE TDECert;
GO 

-- Restoring the certificate, but without the private key.
CREATE CERTIFICATE TDECert
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
GO

-- We have the correct certificate, but not the private key.
-- This should fail as well.
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

We have the right certificate, but without the private key, SQL Server can't use it to decrypt the database. As a result, we get a different error, telling us that there's a problem with the key. The error says the key appears to be corrupt, but we know the real issue: we didn't restore the key.

The error says the key appears to be corrupt, but we know the real issue: we didn't restore the key.

The Successful Restore

In order to perform a successful restore, we'll need the database master key in the master database in place and we'll need to restore the certificate used to encrypt the database, but we'll need to make sure we restore it with the private key. In checklist form:

  • There's a database master key in the master database.
  • The certificate used to encrypt the database is restored along with its private key.
  • The database is restored.

Since we have the database master key, let's do the final two steps. Of course, since we have to clean up the previous certificate, we'll have a drop certificate in the commands we issue:

-- Let's do this one more time. This time, with everything,
-- Including the private key.
DROP CERTIFICATE TDECert;
GO 

-- Restoring the certificate, but without the private key.
CREATE CERTIFICATE TDECert
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

-- We have the correct certificate and we've also restored the 
-- private key. Now everything should work. Finally!
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

With everything in place, we are finally successful!

There's a database master key in the master database.

Don't be alarmed if you don't see the upgrade step messages when you do the restore. For this example I created the initial database on a SQL Server 2008 R2 instance and I did the recovery onto a SQL Server 2014 instance. Therefore, if you had a question about whether you could take a TDE protected database and recover it to a different version of SQL Server, the answer is yes, as long as the new instance is running Enterprise Edition and that new instance has the same or higher version of SQL Server (to include service packs, cumulative updates, and any hotfixes/patches). The second requirement shouldn't be a surprise, as that's the standard requirement for restoring a database to a different server.

Next Steps


Last Updated: 2015-04-01


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 13, 2018 - 5:49:37 PM - Anne Back To Top

I have the same question as the top comment posted by Sydney.

Basically after I restore the database to another instance, does the database still TDE enabled?

Because we restore the certificate using Deencyption as below: I guess the database is not encrypted.  How can we make it TDE again?

-- Restoring the certificate, but without the private key.
CREATE CERTIFICATE TDECert
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );

Tuesday, August 21, 2018 - 3:24:54 PM - Sydney Back To Top

Thank you for this great article!!

I have a question.

After you've restored a TDE database onto a second server, is the restored database also TDE enabled?

Here's what I'm confused with.

I have successfully restored a TDE DB on my second server.  When I run "sys.dm_database_encryption_keys", I only see the "tempdb" and no "Test" db.  So  I don't see the database I've just restored on the second server.  However, if I

Right click on my Test db -> Properties -> Options -> scroll down and I see this Encryption Enabled = True.

Also if I try to run

ALTER DATABASE Test SET ENCRYPTION ON,

I get this error: Msg 33107, Level 16, State 1, Line 36 Cannot enable database encryption because it is already enabled. Msg 5069, Level 16, State 1, Line 36 ALTER DATABASE statement failed. 

My question is why I don't see my "Test" db when I examine run this DMV?

sys.dm_database_encryption_keys

Hope you can shed some light on this.

 

 

 


Friday, April 13, 2018 - 7:06:44 PM - Ron Back To Top

 Great article and thank you. One question: when I backup the database from the restored server, and use it elsewhere, will I still need to use the original privatekey and password, or will I generate a new private key and password from the restored server?

 


Monday, July 17, 2017 - 5:54:30 AM - Dave Back To Top

>> "TDE is only available on Enterprise Edition versions of Microsoft SQL Server."

I am using 2012 Standard and I have all the TDE options.  MSDN [https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-tde] no longer makes any mention of Enterprise being required, so I don't believe this is the case any more (a good thing!).  Perhaps worth an update, as this is a really nice article, thank you.


Friday, April 03, 2015 - 11:26:01 AM - Don Schaeffer Back To Top

Thanks especially for showing that the master keys need not use the same passphrase on different servers for this to work.
A question:  I've read that for security purposes you should not keep backups of the certificates on the server.  Do you agree with this and why?  The file is useless without the password.


Wednesday, April 01, 2015 - 9:55:22 AM - Karl Lambert Back To Top

Thanks for this article, Brian. I am in the process of enabling TDE on all of our production databases. We have a policy that we are creating that will put Production, QA, and DEV certificates (via restore) on QA and DEV boxes, where Production will only hold Production certificates. This is to enable refreshes post-deployment. Your article will help me explain this process, and why, to upper management. I greatly appreciate the information.

Also, as a side note, one nasty mess that I found was that TDE does not play well with a SNAPSHOT ISOLATION setting of READ_COMMITTED, in early versions of 2008 and 2008R2. I found out the hard way that 2008 SP2 CU1 fixes the bug, as well as 2008R2 CU4. Long story short, we will be installing SP4 on all of our 2008 & R2 boxes now. We will also be implementing policy on SP levels. Thank goodness for that, but it is a shame it took this to bring us to that conclusion.


Learn more about SQL Server tools