Blog | Townsend Security

Encryption and Key Management on Microsoft SQL Server 2008 – Part 1

Written by Luke Probasco | Nov 10, 2011 5:03:00 PM

I was recently able to sit down with Patrick Townsend, our Founder and CTO, to discuss Microsoft SQL Server 2008 and what Microsoft customers should be thinking about when using Transparent Data Encryption (TDE) and Extensible Key Management (EKM).  Additionally, we talked about the role of an encryption key management appliance in regards to meeting compliance regulations such as PCI DSS, HIPAA/HITECH, etc. and what to look for when selecting one for your organization.  Here are a few questions from our conversation:

What is Extensible Key Management (EKM) on Microsoft SQL Server 2008?

Download Podcast Now

Microsoft introduced a new architecture for database encryption with SQL Server 2008.  It was a new architecture and a new way of approaching integrated database encryption. SQL Server 2008 had the first implementation of Extensible Key Management (EKM), and it is implemented in SQL Server 2008 R2, and the just announced SQL Server 2012. 

The real significant part of this is that Microsoft understood that there was a need for a new architecture for encryption, that it had to be standardized, and that it had to integrate third-party Hardware Security Modules (HSM) for encryption key management and protection.  So that is what EKM is.  It really has two primary components.  The first one is Transparent Data Encryption (TDE) and the second component is Cell Level Encryption. 

So, TDE is a brand new type of implementation under EKM.  TDE automatically encrypts the entire table space in a SQL Server database and it doesn’t require any application modifications.  It is a really great and easy way to implement encryption in your database.  Cell Level Encryption or Column Level Encryption is the second component.  It gives you the ability to pick a particular column in a SQL Server database and encrypt that column.  Cell Level Encryption does require modifications to your SQL applications, so it has a little more of an impact from the point of view that it takes a developers time to implement it.

Both TDE and Cell Level Encryption, under EKM, give you the ability to plug in a Hardware Security Module (HSM) to protect the encryption keysMicrosoft also recognized very clearly that proper encryption of a database requires protection of the encryption keys in an appliance or HSM. 

Now that we know what EKM is, how does it work? 

It is a facility within SQL Server 2008, Enterprise Edition and above.  It isn’t activated until you explicitly activate it.  When you decide that you want to place your database under encryption control you go into the standard database administrators console for managing SQL Server and you define that you want to implement encryption through an EKM provider and specify how you want to manage your encryption keys.  You then place the database under encryption control.  It is really a simple process for implementing TDE.  In fact, we recently created a video titled “Setting Up TDE & EKM on SQL Server 2008” showing how easy it is.

Cell Level Encryption requires a couple extra steps to modify your SQL code, but the basic architecture is pretty simple.  Once you decide how you want to do it, you create encryption keys and you turn on EKM through an EKM provider.

To listen to our conversation in its entirety, download our podcast “Encryption Key Management with Microsoft SQL Server 2008” and learn how easy it is for your organization to start encrypting sensitive data on your SQL Server.