Hi Friends,
Many of you have seen some sample codes from Books online tht shows u how u can implement TDE. wht books online does not show u is (at least i cud not find an example), as to how to restore a DB tht has been encrypted with TDE on another instance. here is an example..
– step 1, on instance 1, we first set up the keys and certificates and then enable TDE and then take a manual backup (code for backup and restore is not given, u can do it from GUI)
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd’; go CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate’ go USE AdventureWorks GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert GO ALTER DATABASE AdventureWorks SET ENCRYPTION ON GO
if we take a backup if the above database, we will not be able to restore it on another instance becuase TDE is enabled and the DB is encrypted. We can only restore it if we have the same key and certificate on the 2nd instance tht we used to encrypt the above DB on the 1st instance.
–step 2, on instance 1, backup the certificate along with the private key
BACKUP CERTIFICATE MyServerCert TO FILE = ‘c:\MS.cer’ WITH PRIVATE KEY ( FILE = ‘c:\MS.pvk’ , ENCRYPTION BY PASSWORD = ‘amit’ ); GO
– step 3, on instance 2, create the master key if it is not already created and then restore the certificate with the private key. after this resotre the DB and it will be successful
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd’; CREATE CERTIFICATE MyServerCert FROM FILE = ‘c:\MS.cer’ WITH PRIVATE KEY (FILE = ‘c:\MS.pvk’, DECRYPTION BY PASSWORD = ‘amit’); GO
Hope this helps.
Is it me or do you have to have the same password for the master key on both instances for this to work?
Should also mention that backing up your master database is now a must since your Server’s master key is stored
Amit,
Nice, concise article. Encryption keys always seem harder to deal with than they actually are. One thing, however, that anyone considering TDE needs to consider is the complications in removing it. This involves rebuilding the database log file. After removing TDE the log will still have a portion of it encrypted and database errors will occur.
I’ve also written a blog post on TDE. Have a look and let me know what you think. http://myhumblesqltips.blogspot.co.uk/2012/11/data-encryption-and-protection.html
Tom