I got my first real world experience with a TDE (transparent data encryption) database recently. For those who don’t know TDE encrypts all of the data at rest. So the database files are encrypted and the backup is encrypted. The data is automatically decrypted as you read it and encrypted as it’s written back to disk. This of course requires a certificate to do the encryption. Setting up the encryption is actually pretty easy. If you look at the link above you will find a very simple set of code to use to encrypt the database.

TL;DR; – The final scripts to copy a TDE database from one instance to another are at the bottom.

If you want to follow along here is a slightly modified (I used a different database name) version of that code.

USE master;
GO
CREATE DATABASE [MyTDETest]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE [MyTDETest];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE [MyTDETest]
SET ENCRYPTION ON;
GO

I followed this code in a development environment and it worked perfectly. The developer who need the database used it and was ready to move the entire database up to the next environment. My plan was to back the database up and restore it in the test environment. This had two major benefits.

1. It copied his development environment entirely, schema and data. The only changes needed were security and once it was set up I could easily modify that.
2. I got to make sure that I could restore this database in case of a disaster.

 
I did a bit of research and came across this msdn article Move a TDE Protected Database to Another SQL Server and this one by Steve Jones (b/t) Restoring a TDE Database on a New Instance.

So I backed up the certificate, backed up the database, moved them both and started the process of restoring everything.

On the initial server:

USE master
GO
BACKUP DATABASE MyTDETest TO DISK = 'C:\temp\MyTDETest.bak'
BACKUP CERTIFICATE MyServerCert
	TO FILE = 'C:\temp\MyServerCert'

On the new server:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord';

CREATE CERTIFICATE MyServerCert  
FROM FILE = 'C:\temp\MyServerCert'

When testing this at home on a SQL 2014 box I get the following error:

Msg 15208, Level 16, State 19, Line 5
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Well that’s a problem. I just took the backup so one would assume the certificate file is valid (just to be sure I tried the backup several times) and I was backing up to my temp directory so you would think I had permissions. Turns out I didn’t.

Once I granted the appropriate permissions my certificate was created but when I tried to restore the database I got the following error.

Msg 15507, Level 16, State 1, Line 2
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Now the key is corrupted? Just what I needed. I tried re-creating it several times just to be sure but had the same result. The only thing I could think of was that I was missing something when I created the certificate. Probably the private key. Which of course is a problem since I didn’t use one in the first place. So back to the internet and BOL I go.

One of the things I found was that in the CREATE CERTIFICATE entry of BOL it says:

The ENCRYPTION BY PASSWORD option is not required when the private key will be encrypted with the database master key. Use this option only when the private key will be encrypted with a password. If no password is specified, the private key of the certificate will be encrypted using the database master key. Omitting this clause will cause an error if the master key of the database cannot be opened.

So the private key was encrypted using the database master key. We can confirm this by looking in sys.certificates.

SELECT * FROM sys.certificates WHERE name = 'MyServerCert'

Note the pvt_key_encrytion_type is ENCRYPTED_BY_MASTER_KEY. This does imply that there is a private key somewhere, but I had no idea where. Unfortunately, while interesting that didn’t really buy me much. I’d now spent several hours on Google and BOL and I decided at this point I’d done enough research to know I was missing something and that I wasn’t going to find it easily on my own. I now asked for help.

Using #sqlhelp on twitter
Using dba.stackexchange

And you will note that in both places I got help. I do want to point out that I don’t usually do this kind of shotgun approach. I put my question on #sqlhelp and then realized that I really needed more than 140 characters to describe the problem. Hence the forum question. Both answers lead me to the same thing. No, I didn’t have a private key when I created my certificate but one had been created for me! I needed to back up the private key when I backed up the certificate!

So here are my final scripts:

On the source server:

USE master
GO
BACKUP DATABASE MyTDETest TO DISK = 'C:\temp\MyTDETest.bak'
BACKUP CERTIFICATE MyServerCert
	TO FILE = 'C:\temp\MyServerCert'
	WITH PRIVATE KEY
       ( 
        FILE = 'C:\temp\MyServerCert.pvk' ,
        ENCRYPTION BY PASSWORD = 'Password' 
      )

On the destination server:

USE master
GO

-- If the MASTER KEY doesn't already exist.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord';

IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'MyServerCert')
	DROP CERTIFICATE MyServerCert

CREATE CERTIFICATE MyServerCert  
	FROM FILE = 'C:\temp\MyServerCert'
     WITH PRIVATE KEY 
      ( 
        FILE = 'C:\temp\MyServerCert.pvk' ,
        DECRYPTION BY PASSWORD = 'Password' 
      ) 
GO

RESTORE DATABASE [MyTDETest] 
	FROM  DISK = N'C:\temp\MyTDETest.bak' 
	WITH  FILE = 1,  NOUNLOAD,  STATS = 5, 
	MOVE N'MyTDETest' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CI\MSSQL\DATA\MyTDETest.mdf',  
	MOVE N'MyTDETest_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CI\MSSQL\DATA\MyTDETest_log.ldf'

GO

For the purists out there I want to point out when I did this at work I used MUCH better passwords and my backup was a COPY ONLY backup so as to avoid messing up the regular backup schedule.