Copying a TDE database from one instance to another

9

June 22, 2015 by Kenneth Fisher

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'

MoveTDE1

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.

9 thoughts on “Copying a TDE database from one instance to another

  1. Great post, this has really helped me get over one hurdle in encrypting our ERP system DB. One other hurdle I have to clear is how to get log shipping to work from the TDE DB. Would I basically use the same query to add the key/cert on my target server for log shipping?

    • I would assume so. I’ve never actually done it. Once the key/cert is available on the target server your restores should work without a problem. Of course if the key/cert change for any reason on the source server you’ll have to push it across again to the target.

      Glad the post helped otherwise!

  2. lakshmi saripalli says:

    Hi , I came across your blog post in search of a problem I am facing . I have TDE enabled on Server 1 and server 2(dev and test). Now I need to copy a database which has TDE on server 1 onto server 2. It would be easy if there wasn’t TDE on server 2.
    I get error messages when I try to use the master key because there is already another one and I cannot drop that . Were you ever in this situation?
    DO you have any ideas on how to solve this?
    Would really appreciate your thoughts.
    Thank you ,
    Lakshmi

    • Have you tried restoring the master key from server1 to server2? If I remember correctly when you do the restore it will re-encrypt the DBs on server2 but now allow server1 to be restored.

      • lakshmi saripalli says:

        Thank you for such a prompt response! I m confused.So If I copy the server master key over to server 2 ,are you saying that the restore will work ,but the db’s already encrypted on server 2 will be unencrypted?(or the other way , the server master key will change but all will now be encrypted using the new key). Will try all this right now.

        • If I remember correctly .. and I am NOT an expert in encryption .. the key will be restored and each of the encrypted DBs will be re-encrypted with the new key. You might put this up on a forum though just to be certain.

      • lakshmi saripalli says:

        Thank you will post on stackexchange,but here’s what I did. I used your scripts to save the certificate with password for db1 on server 1 and restored it with your script onto server 2.
        when I checked encryption on the db’s all my original db’s on server 2 are still encrypted and db1 is restored now and encrypted too and i now have 2 certificates in my sys.certificates table,but the sys.symmetric keys shows only key . so turns out I ended up with one master key and 2 certificates .
        Thank you very much for the conversation today 🙂

  3. lakshmi saripalli says:

    HI,
    I have a situation where there is TDE on server 1 and 2. I need to restore a database from server 1 to 2 and keep getting errors. Do you know of any solutions that can help .
    Thank you very much.
    Lakshmi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: