Transparent Data Encryption in SQL Server 2008

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.