Monday, 26 January 2009

Transparent Data Encryption

I know allot of you are looking to Microsoft SQL 2008 and thinking about the new features such as resource governor and policy's but in my mind the best feature is transparent data encryption or TDE for short.

Encryption on SQL 2005 databases and earlier was something of a nightmare, both hard to enable and losing features such as indexing on encrypted tables made databases slow.

All to often programmers and administrators would leave it off because of this and make data vulnerable data theft, TDE is a big improvement over previous versions, choosing to encrypt the database so that all the processes that access the database need to have the correct encryption key to access it.

This isn't just better for the security it also means that once the system has the encryption key it can then use it to do all the normal services such as indexing query's etc, without programmers changing a thing with the database.

Not only does this mean that you can now encrypt any database you chose well almost any you can't encrypt system databases (tempdb encrypts after you turn TDE on in case you use temp tables) but it also puts the encryption back in the hands of Database Administrator.

Now here is a quick demo on how to turn TDE on.


-- TDE transparent data encryption
-- first you need a master key to safe guard
-- your certificates I like a nice long 40 digits
Use Master
create master key encryption by password = 'zOLLaJyZxKoFGG5jUK4pfoYzW2eSOW6VGAvH4RvJ'


-- create the certificate and give it a name
Create certificate server_one_certificate with subject = 'My company certificate'

-- pick a database to encrypt
Use adventureworks

-- create encryption key AES_128 AES_192
-- or AES_256 based of your certificate
create database encryption key with algorithm = AES_256 encripton by server certificate server_one_certificate
GO


-- now turn the database encryption on
-- this might take some time depending on
-- size of database
alter database adventureworks set encryption on
GO


-- backup certificate public and private keys and
-- again pick a nice password
-- because if you forget it you'll need to create
-- another backup of the certificates
use master
backup certificate server_one_certificate to file = 'c:\backup\mybackupcert.cer
with private key ( file = 'c:\backup\mybackupcert.pfx', encryption by password = 'KHHiWUM8HpH1UqpLGGlvswzRZuVPmiMWeiM7RUOD' )


-- check the status of the encryption to see if its turn on or not
select * from sys.dm_database_encryption_keys


Now there are somethings to remember once you've turn it on.
First your backs of this database are now encrypted so if you want to restore to the same server its fine but if you want to restore to another server you must first import the certificates.

Second thing is that you should expect 5% over head on the CPU but the benifts of having your data secure are worth the CPU load, but remember to check you can take the extra load before turning it on.

Last thing to remember is that certificates can be used on a one to one or one to many so create as many certificates as you need but remember you'll have to back each one up.

Now I'll give you an example on how to import your certificates to another server for restoring your backups too.

-- to restore a certificate first you need to
-- create a master key if you don't have one already
-- doesn't have to be the same as the first server as you can see
use master
create master key encription by password = 'q3j0JFSsoqm2Ptf6LAPvdpxeMM3L9RKKZxD90AaA'


-- import your backed up keys, as you see
-- the certificate name doesn't have to be the same ether.
create certificate server_two_certificate from file = 'c:\backup\mybackupcert.cer' with private key (file =
'c:\backup\mybackupcert.pfx', decryption by password = 'KHHiWUM8HpH1UqpLGGlvswzRZuVPmiMWeiM7RUOD')


Now you can restore your database from the first server without problem as the certificate has been imported even if it does have another name, I didn't have to change the name I just did so to prove you don't have to have the same name.

One last thing if you are going to create this from script and there is nothing wrong with that but make sure to put the script in a safe place if you leave the password in, I found one the other day on a system that the DBA had forgotten to move after executing it.

No comments: