SQL Server TDE (Transparent Data Encryption)

A recent requirement on a project was to protect sensitive data while it was being shipped as a backup from the UK to the US.

The requirement was to ensure that if the data fell into the wrong hands that it would be unfeasible for the data to be reconstructed.

One approach for consideration was a new feature in SQL Server 2008 call transparent data encryption.  To evaluate this, I decided that I would have to learn how to get it up and running and try and discover any pro’s and con’s before actually advising that we use it.

As you may have guessed from the title, TDE is transparent.  That means that a user of the database should have no idea that the data on the disc is actually encrypted.

Considerations

Volume of data to be encrypted

• The larger the volume the longer it will take to do the initial encryption (this is only the case if you already have the data and need to apply encryption to it)

Table Compression

• There are some huge benefits, particularly with storage requirements to apply row/page compression to the data tables.  In my instance the compressed table was 30% the size.    So instead of using 16 TB of space we used 4.8 TB. The cost of this saving as you can imagine is significant!
• As encrypted data tends to be passed through an algorithm where every output is close to being unique this will cause the data to be much less compressible, so the savings you get for using row/page compression are minimal CPU
• Encrypting data requires each data item to be passed through an algorithm where the output is encrypted.  This requires some CPU.  I have noticed that for smaller databases this is almost immaterial Backup compression
• Similar to table compression problems. Encrypted data does not compress well

Ok, so how does it work.

So the encryption works on the basis of having an master key that is stored in the Master database. A named certificate that can be applied to a database, and an encryption algorithm,

REMEMBER TO BACKUP THE KEY

So below is a script on setting up TDE for the AdventureWorks2008R2 database

To Encrypt a database

(Insert stolen scripts from BOL here) (it did take time to get there btw) (and BOL was actually wrong in a few places)

USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<UseStrongPasswordHere>’; go CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate’; go USE AdventureWorks2008R2; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert; GO ALTER DATABASE AdventureWorks2008R2 SET ENCRYPTION ON; GO USE AdventureWorks2008R2; GO /* The value 3 represents an encrypted state    on the database and transaction logs. */ SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3; GO

To Backup the key

It is important that this happens. I believe there is a way if you know the passwords and certificate names, but I wasn’t brave enough to try it.

The key is backed up to the SQL data folder

USE master; GO BACKUP CERTIFICATE MyServerCert O FILE = ‘MyServerCert’ WITH PRIVATE KEY ( FILE = ‘MyServerCertSQLPrivateKeyFile’, ENCRYPTION BY PASSWORD = ‘<UseDifferentStrongPasswordHere>’ ); GO

To Remove Encryption from the server

ALTER DATABASE AdventureWorks2008R2  SET ENCRYPTION OFF USE master; ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY GO DROP CERTIFICATE MyServerCert GO USE AdventureWorks2008R2; DROP DATABASE ENCRYPTION KEY GO USE master; DROP MASTER KEY;

To complete the removal restart SQL Server service 

You should also execute the following to confirm that certificate and keys are removed

select * from master.sys.certificates select * from sys.dm_database_encryption_keys

It is also possible to move a TDE encrypted database.  This is fairly straight forward if you managed to get this far, so pls see BOL.

 Chris

In Addition to above, I have been pointed in the direction of the “SERVER MASTER KEY” by Andrew C.  More info on this can be found here. It can be used to encrypt the database master key, so that everything is now encrypted.

About these ads