TDE restored DB encryption state

Question

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.

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