Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Career Limiting Move, SQL Server 2008 Transparent Data Encryption

Transparent data encryption (TDE) in SQL Server 2008 enterprise edition is a great tool for protecting your data ‘at rest’ , but you need to be careful when you use it.

TDE only really comes into play when you move the database to another location (hence the transparent in the name). When you encrypt a database you do so by making a key and using that. If you move the database to another location e.g. with detach-> copy -> attach or backup –> restore then you must have that key at the new location.   Bear in mind that  a new location could just be the original server which has had to be totally rebuilt as the key won’t be there anymore. 

Here’s an example of how to use TDE in a career limiting situation:

DBA: I need to protect my data from unauthorised access

Microsoft chap: Well, you could use TDE this cool new feature in SQL Server 2008

DBA: Thanks Microsoft bloke

time passes..



DBA: Hey Microsoft chap I used that TDE thing but the server crashed and I can’t get my backup to restore.

Microsoft chap: Ah OK you need to restore the key you used to encrypt the database and her’s some sample code:

— if there is no master key in the master database, create one (don’t do this if there is already one existing)

use master


— restore the certificate including private key

CREATE CERTIFICATE MyDBCert FROM FILE = ‘c:\SQLBackup\MyDBCert.cer’     WITH PRIVATE KEY ( FILE = ‘c:\SQLBackup\MyDBCert.pvk’,

— You need the password used to backup the key

        DECRYPTION BY PASSWORD = ‘P4ssw0rd Us3D 2 BKup Th3 KEy’ )

— now you can restore the database

DBA: Where do I get that private key? on TechNet?

Microsoft chap: I ‘m afraid we can’t really help unless you have the key that you used to originally encrypt the database.

DBA: Surely you have a workaround, backdoor thingy?

Microsoft chap: Afraid not, because that would mean that we could look at the very data you were trying to protect from unauthorised access.

exit ex-DBA

If you are using TDE the first thin you should do once you have made your key is backup the key like this

BACKUP CERTIFICATE MyDBCert TO FILE = ‘c:\sqlbackup\MyDBCert.cer’


      ( FILE = ‘c:\sqlbackup\MyDBCert.pvk’,

        ENCRYPTION BY PASSWORD = ‘P4ssw0rd Us3D 2 BKup Th3 KEy’


Use the key to encrypt a copy of your database and try to use the key to restore it to another location (which could just be another instance on your test server). Once you have got your head round that then you are ready to try it on the production database.

Remember to keep that key safe, like you would your backups, but NOT in the same location as that will again mean that your use of TDE is pointless as anyone with the key and the data has your data.