Encryption and Key Management

Wow, I'm getting to post two days in a row again. I'm finally getting into the right frame of mind I think.
Today I'd like to look at a couple of new features that SQL Server 2008 introduces. Transparent Data Encryption (TDE) and Extensible Key Management (EKM). These subjects are closely related as we will see further on.
Transparent Data Encryption gives us the ability to encrypt our database objects without the need for changes to the client-side application. In other words, there is no need for us to provide decryption in our database applications. When enabled, data is encrypted prior to being written to the data file. This means that the data in the data file is encrypted, not just the access to it. With all the security concerns around identity theft and lost information, companies now have one less worry. Let's say that a company is running an application entirely on a laptop and storing the data in a local instance of SQL Server 2008. Yeah, I know, why would anyone do that, but for arguments sake, let's say they did. Now if the the laptop is stolen, and the data is not encrypted, the thief could very easily break into the data files, even without the SQL Server login info. But, if that data is encrypted with TDE, then that problem no longer exists. The data file can't be broken simply by attaching it to a new SQL Server. In fact TDE also encrypts backups so that the backup files are also useless to a thief. No more restoring the backup on another server.
TDE encryption is accomplished at the page level. The data is encrypted when it is written to disk and automatically decrypted when it is read from disk. The encryption will be based on a key of course, a database encryption key (DEK) that is protected with a certificate stored in the master database of the server.
As you may know, SQL Server 2005 offered column encryption, but this had its drawbacks. TDE is fully searchable. Column encryption is still part of SQL Server 2008, as it adds a finer control over what is encrypted. If only a few columns in a database need to be encrypted, such as SSN's, salary info, privacy data, then column encryption may be a better choice. TDE encrypts all files of a database, including backups. If you use TDE for a database that is being mirrored, then the mirroring database will have the same encryption. One thing to note is that if any of the databases on a server use TDE, the tempdb database also gets encrypted and can harm performance.
Let's look at enabling TDE for a database.

USE master;
GO

--We need a master key for our database
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='p@ssw0rd';
GO

--Now we need a certificate to protect our key (DEK)
CREATE CERTIFICATE encryptCert
WITH SUBJECT='Certificate to protect DEK';
GO

--Now we change databases (certs and keys are created in the master db)
USE AdventureWorks;
GO

--Let's create our Database Encryption Key (DEK)
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM=AES_128
ENCRYPTION BY SERVER CERTIFICATE encryptCert;
GO

--Now all we have to do is enable TDE
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON;
GO

Now, we can protect our DEK in a different way if we choose. We can use the Extensible Key Management (EKM) feature instead. This method will allow us to use an asymmetric key stored on a Hardware Security Module (HSM).It is a good idea, a best practice in fact, to store the keys used in encryption separately from the encrypted data. EKM allows us to follow this. EKM exposes encryption functionality to the vendors who make HSM devices. You can store encryption keys on devices such as USB devices and smartcards. HSM provides: Authentication with an EKM device, Separation of duties by additional authorization checks, Better performance for encrypt/decrypt operations, External generation of keys, storage, retrieval, distribution and disposal of keys, Key rotation.
Since EKM is disabled by default, here is how we enable it:

USE master;
GO

--Enabling is an advanced option
--so let's configure advanced options to show up
EXECUTE sp_configure 'show advanced option', '1';
RECONFIGURE;
EXECUTE sp_configure 'EKM provider enabled', '1';
RECONFIGURE;

--Here is how to protect an EKM module
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM=AES_256
ENCRYPTION BY EKM_SomeModuleDEK;
GO

And that's all there is to it. For now. One thing to note is that TDE and EKM are both based on the Microsoft Cryptographic API (MSCAPI) provider for encryption and key generation. For more information see the SQL Server 2008 BOL and MSDN.