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.