Author: way0utwest

Symmetric keys in SQL Server are recommended for encrypting data in columns. They are a good balance of security and resource usage, much better than asymmetric keys. Creating a symmetric key is fairly simple, using DDL that’s easy to understand.

One note before I show this is that symmetric keys are deterministic when created, meaning that the same parameters run in different databases will result in the same key. That means that the same key in a different database (or instance) can decrypt data that was encrypted in your production instance. Keep control of the parameters used to create symmetric keys and secure them. That means watch out for storage of these items in source control, in installation files, upgrade scripts, etc.

Creating a Key

The creation DDL used is the CREATE SYMMETRIC KEY statement. This command has a number of parameters that you can change. The important ones for most people are:

• the algorithm
• the encryption mechanism
• the key source
• the identity value

You should try to use the most secure algorithm you can, which is AES_256 in SQL Server 2012. It’s the same back to SQL Server 2005. You should avoid the RC4 algorithms, since they are not terribly secure. Even the DES ones you might avoid, but do some research to understand if you have a need to use anything less than AES_256.

The encryption mechanism provides protection for the key. You can use a password (secure it) or you can use another key. The common way to secure the symmetric key is with an asymmetric key (or a certificate). However if you have the option to use a hardware module with the extensible key management (EKM) system, use that. You can use multiple encryption mechanisms if needed, which might be useful for separating the access to this key for different users.

The key source provides a way to seed the key. This is a parameter you need to regenerate the key.

The identity value is optional, but provides more a passphrase to tag a key. Useful for temporary keys.

To actually build a key, let’s create one here using a few parameters, and securing it with a password:

-- create a symmetric key
create symmetric key MySalaryProtector
 WITH ALGORITHM=AES_256
    , IDENTITY_VALUE = 'Salary Protection Key'
    , Key_SOURCE = N'Keep this phrase a secr#t'
  ENCRYPTION BY PASSWORD = 'Us#aStrongP2ssword';
go

That’s it, once you have executed this, you have created a key. You can see your symmetric keys by querying sys.symmetric_keys

SELECT * FROM sys.symmetric_keys

That’s all you need to do. There’s not backup or restore of a key; if you need to recreate it, supply the same parameters and you’ll get the same key. The keys are stored in the backup of a database, so if you restore from backup, you’ll have them back as well.

In another post, I’ll look at actually encrypting data with a key.