"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").

Search results matching tags 'SQL Server 2008', 'T-SQL', and 'encryption'

"Cloning" Symmetric Keys