4

I've been looking into using TDE in our SQL Server 2014 environment and have a few questions. I think I have a fairly solid grasp on the numerous "how-to's" that are out there on initially implementing TDE. One thing I'm not finding is what state the DB is left in if you restore it to another instance.

For example, I encrypt a DB on one instance, then restore it to another after creating a master key and TDE certificate with the following commands on instance B:

/* Create key in master on second server */ USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' /* Re-create server certificate in master database by using the original server certificate backup file. The password must be the same as was used when the certificate backup was created. */ CREATE CERTIFICATE TDE_Dev_Cert WITH SUBJECT = 'TDE Certificate' FROM FILE = 'C:\certfile' WITH PRIVATE KEY (FILE = 'C:\privatekeyfile' DECRYPTION BY PASSWORD = 'password';

After a database that has been encrypted with TDE on instance A is restored to instance B, is the database fully decrypted at that point and no longer using TDE, or is the certificate simply allowing TDE to continue to function normally? The Encryption/Decryption flag on the certificate is what is confusing me. Thanks in advance.

Improve this question

1 Answer 1

Reset to default
1

Restoring the database to another server will not result in the data being unencrypted. However, you do need to:

...move the certificate or asymmetric key that is used to open the DEK. The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files.

You must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key does not have to be the same as the database master key password.

The following link provides you with the details:

https://msdn.microsoft.com/en-us/library/ff773063(v=sql.120).aspx

Once you have everything set up on the restored database, you can (of course) turn off TDE and let the server eventually de-encrypt the database, if that is your intention.

There is a Very Important Note on removing encryption from a database that is to be restored from SQL Server 2008 or 2008 R2 to another server at:

Link

(This may not matter to you, depending on the source of the restored database.)

Improve this answer
2
  • De-encrypted, oops. Thanks that answered everything if I'm understanding your reply and the articles correctly. The database won't be decrypted after the restore and any updates/inserts on the DB should be encrypted. Commented Jul 21, 2015 at 20:03
  • Correct, the data will not be unencrypted by the restore. Commented Jul 21, 2015 at 20:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.