Andrew Fryer's Blog

SQL Server 2008 - Transparent Data Encryption

One of the new things you can do in CTP5 of SQL Server 2008 is to encrypt your databases so that they are protected at rest and so are any backups made from them.  So this prevents anybody from accessing a database without going through the server it belongs to.

To move an encrypted database from one server to another you would need to move the key that encrypted it as well. For example you might send the key be e-mail and then send the database on CD's in the post.

First you need a master key and then a certificate:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseStrongPassword1!'; --SMK????
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate for Sensitive Data' GO

Then you can use this to encrypt the database with this:

USE Retail_DWH
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyDBCert

The encryption process runs as a background task and the database is available during the process.

It's a really good idea to back up the certificate as without it you can't get the database or backups back - whihc of course is the whole objective! 

This script backs up the certificate to a temp folder as an example:

BACKUP CERTIFICATE MyServerCert
TO FILE = 'c:\temp\MyServerCert'
WITH PRIVATE KEY (file='c:\temp\MyServerCertKey',
ENCRYPTION BY PASSWORD='UseStrongPassword1!')

To move this to another instance or server the first step is to create a master key on the new server:

create master key encryption by password = 'UseDifferentStrongPassword1!'

The certificate can then be restored like this:

create certificate MyServerCert
from file='c:\temp\MyServerCert'
with private key (
file = 'c:\temp\MyServerCertKey',
decryption by password='UseStrongPassword1!')

So very simple to setup, easy to use