Home » Blog » How to Create a Symmetric Key

How to Create a Symmetric Key

2014-06-14 - Cryptography, Encryption Hierarchy, General, Security, Series

Introduction

Asymmetric key encryption has many advantages over symmetric key encryption. However, there is one significant disadvantage. The mathematical calculations required to encrypt and decrypt data are a lot more complex and resource intensive. That means that asymmetric encryption is slow. For that reason, it is recommended to use symmetric key encryption when it comes to encrypting a lot of data. To use symmetric key encryption in SQL Server you first need to create a symmetric key.

The CREATE SYMMETRIC KEY Statement

To create a symmetric key in SQL Server you can use the CREATE SYMMETRIC KEY statement. In its simples form it looks like this:

[sql] CREATE SYMMETRIC KEY ASymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE ACertificate;
[/sql]

The above statement has three parts. The first part specifies the name of the symmetric key we want to create. That name has to be unique within the current database.

The second part, the WITH ALGORITHM clause specifies the algorithm with which you want to use the key. You can choose between AES_128, AES_192 and AES_256. There are also a bunch of older algorithms available, but they are not secure anymore and hence their use for new development is not recommended.

The third part is the ENCRYPTION BY clause. In the CREATE CERTIFICATE and CREATE ASYMMETRIC KEY statements that clause was optional. Omitting it causes the new key to be protected by the database master key. With symmetric keys, that is not an option. A symmetric key has to be protected by either a password, a certificate, an asymmetric key or another symmetric key. That means that the ENCRYPTION BY clause has to always be present. The above example uses an existing certificate for this purpose.

Finding Existing Symmetric Keys with sys.symmetric_keys

To see if the creation was successful, a query agains the sys.symmetric_keys catalog view can be used:

[sql] SELECT SK.name,SK.algorithm_desc,SK.create_date,SK.modify_date,SK.key_guid
FROM sys.symmetric_keys AS SK
WHERE name = 'ASymmetricKey';
[/sql]

The output of that query will look similar to this:

Besides of the name, the above query also returns the key algorithm and a few more interesting properties.

Summary

When performance is important, for example when encryption large amounts of data, it is recommended to use symmetric key encryption over other encryption types. The symmetric key required for this can be created with the CREATE SYMMETRIC KEY T-SQL statement.