Freigeben über

Career Limiting Move, SQL Server 2008 Transparent Data Encryption

  • Artikel

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 're4lly ReAlLyStr0nG P4ssWOrd'

-- 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'

    WITH PRIVATE KEY

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

Technorati Tags: transparent data encryption,SQL Server 2008,security

Comments

  • Anonymous
    August 26, 2008
    Ah - but this DBA is career limited not because of the feature, but because he/she refused to read the documentation. Even in the overview topic for this feature it says, and I quote: "Note:   When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations."

  • Anonymous
    August 27, 2008
    Like real DBA's read documentation ;) You are right, though. I had a friend that did exactly that move with his data partition and Vista - disc encryption, no key backup. Private only, so noone fired him. He called me with exactly that question... and I basicalyl told him to keep the hard disc - as a permanent remainder for all the data he lost access to.

  • Anonymous
    September 17, 2008
    "real" DBAs also test new features before exposing them to the business, regardless of what the docufiction says.  ;)

  • Anonymous
    April 08, 2009
    H1 { FONT-SIZE: medium } Many thanks to everyone who attended the MOSS 2007 and SQL 2008 "Better Together"

  • Anonymous
    April 15, 2009
    (Cross-posted from Vantage Point: Bob German's Weblog ) H1 { FONT-SIZE: medium } Recently Rich Crane