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

Creating a Symmetric Key in SQL Server

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
    , IDENTITY_VALUE = 'Salary Protection Key'
    , Key_SOURCE = N'Keep this phrase a secr#t'
  ENCRYPTION BY PASSWORD = 'Us#aStrongP2ssword';

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.

Filed under: Blog Tagged: encryption, security, sql server, syndicated

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


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...