SQL SERVER 2017 HOSTING – How to Use SQL Server Encryption with Symmetric Keys?

You can quickly and securely encrypt data in SQL Server 2005+ by using the native Symmetric Keys functionality. The most common encryption algorithms symmetric key encryption supports are Des, Triple Des, RC4 128bit, AES 128bit and AES 256bit.

Setup your database

To create a symmetric key, we first need to setup our database with a master key and a certificate, which act as protectors of our symmetric key store.

Create a Database Master Key CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’

Create a Certificate

Create your Symmetric Key

Now that we have setup our database, we can add the symmetric key to our certificate. To ensure we can replicate the key on another server, or rebuild the key if it is corrupted, you must very safely keep note of the KEY_SOURCE and IDENTITY_VALUE parameters, as these are what is used to create the key.

Create a Symmetric Key

The IDENTITY_VALUE parameter is used to generate the guid for the key and the KEY_SOURCE is used to generate the actual key. This allows you to run the above code on any server as many times as you like, with the same values, to generate the exact same key.

I’ve chosen AES_256, but you can choose from the following encryption algorithms: DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

Encrypting and Decrypting Data

Finally, we can encrypt and decrypt data using the key we have just created by using the following snippets of code.

Open the Key

Before you can start encrypting or decrypting data, you must first initialize the key. This is done with the following piece of code.

Encrypting data

You can encrypt data by using the EncryptByKey function, like so:

Note that the result of the above encryption is of type varbinary(256), and if you would like to store the value in a column to use this type.

Decrypting Data

You can decrypt data by using the DecryptByKey function, like so:

Make sure you decrypt to the same type that you encrypted in the first place. In my example I encrypted a varchar(max), so I also decrypted to a varchar(max).

Using Symmetric Keys in a Function

Because symmetric keys use time based sessions, you cannot open them inside a function, however you can get around this by opening them first with a Stored Procedure, and then calling the function. Here’s an Example of the setup I have going.

The OpenKeys Stored Procedure

The Encrypt Function

The Decrypt Function

An Example of How to Use Symmetric Keys in a Function

As long as you call the OpenKeys stored procedure in the same query as the function, it will work.