SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server 2008 Encryption - Extensible Key Management

One of the new features with SQL Server 2008 is the addition of extensible key management, allowing hardware devices to be integrated into the encryption/decryption process for the keys securing your data. There are various vendors that are building Hardware Security Modules (HSMs) that can connect to your SQL Server instance.

The author of the book I'm tech editing set up a SafeNet Luna HSM device on a machine he has and gave me access through a VM. It's an interesting device, and while I haven't seen it, it allows you to offload some of the encryption/decryption processing from your SQL Server to the device. This should speed up your encryption, though I haven't had a real world workload to really test it. And I hate running things over a VM remotely.

The architecture allows for these HSM devices to plug right into the SQL Server encryption stack. You create asymmetric and symmetric keys (depending on which your device supports) the same way, adding a "WITH PROVIDER" clause to your CREATE statements.

The provider is how an EKM system hooks into SQL Server. Essentially you are registering your device within SQL Server as a way to handle cryptographic functions, kind of like you used to register DLLs so you could implement an extended stored procedure. In this case there is a new CREATE statement, the CREATE CRYPTOGRAPHIC PROVIDER which registers the DLL for your EKM system. This is provided by the manufacturer and doesn't get you access to the device. There will be a device specific procedure for logging into the device. Once that is complete, you'll have to set up a credential and grant a login rights to the credentials for other individual logins to use the HSM device.

Why go through this?

Speed is one since these devices are optimized for encryption/decryption routines, but also because your server's CPU isn't being spent with these math-intensive operations. These devices also can have their own backup routines for keys, so you can possibly prevent sysadmins from being able to access data.

One thing I'd say is to go slowly and carefully if you implement one of these devices. Actually, you would never implement one of these devices. The chance of failure or problems is too great. You would always want two of these, keeping one in another location for DR purposes, but perhaps somewhat close in the event of routine errors.

This is an interesting addition to the encryption and security framework of SQL Server. I expect to see more vendors build HSM devices in the near future.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Rowland Gosling on 31 July 2009

I'm looking at the same device for our needs. Interesting to see what you find.



Posted by luke.probasco on 21 June 2016

I also would like to add that Townsend Security's Alliance Key Manager is another option.  It is available in the cloud (AWS, Azure), VMware, and as an HSM.  It has also been validated to meet PCI DSS in VMware environments.

In addition to managing TDE and cell level encryption keys, Alliance Key Manager offers an on-board encryption servers - the key never leaves the server. Further, Townsend Security enables customers to encrypt SQL Server databases—including 2008, 2012, and 2014 releases—as well as Oracle, IBM DB2/400, MySQL, MongoDB, and more.

Leave a Comment

Please register or log in to leave a comment.