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.

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “Transparent Data Encryption in SQL Server 2008”

  1. 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

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *