Printed 2017/08/16 02:09PM

SQL Server 2008 Encryption - Extensible Key Management

By Steve Jones, 2009/06/24

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.