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.
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)
- 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)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<UseStrongPasswordHere>’;
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate’;
CREATE DATABASE ENCRYPTION KEY
ALTER DATABASE AdventureWorks2008R2
/* The value 3 represents an encrypted state
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
BACKUP CERTIFICATE MyServerCert
To Remove Encryption from the server
|ALTER DATABASE AdventureWorks2008R2
SET ENCRYPTION OFF
ALTER MASTER KEY
DROP CERTIFICATE MyServerCert
DROP DATABASE ENCRYPTION KEY
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.
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.