Symmetric Key Encryption with SQL Server 2008


Author : Ratheesh K Nair, SQL Server DBA, Cochin , Kerala

In a symmetric key algorithm, there is but one key. That same key is used to encrypt the data and decrypt, the data. If someone were to get possession of the key, that person could take anything you’ve encrypted, and decrypt it immediately.

The above figure shows the data encryption used by Symmetric keys and the one below shows how data decryption happens in Symmetric key Algorithm.

Advantages

Symmetric key encryption is known to be faster and stronger than their asymmetric counterpart. It uses less overhead on system resources. For simple encryption this is the best way.

When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. The key can have more than one encryption of each type. In other words, a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time

Here I am going to explain the simplest way of encrypting data in a database, i.e. Symmetric Key Encryption with Password

Symmetric Key Encryption with Password

Using this method is very simple and can be used if we want to encrypt data in whole column.

The simple example below will help you understand.

USE master

GO

CREATE DATABASE RKN_Test

ON PRIMARY ( NAME = N’RKN_Test’, FILENAME = N’D:RKN_Test.mdf’)—Any Path you prefer

LOG ON ( NAME = N’RKN_Test_log’, FILENAME = N’D:RKN_Test_log.ldf’)

GO

Master key is used to encrypt the Certificates and Keys in a database.

Passwords should meet windows password policy if you have set any.

USE RKN_Test

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = ‘RKN@123′

GO

We can use many types of algorithm while creating Symmetric keys like DES, TRIPLE_DES, TRIPLE_DES_3KEY, AES_128, AES_192, AES_256 etc.Please be careful while choosing the encryption algorithm.

USE RKN_Test

GO

CREATE SYMMETRIC KEY RKN_TableKey

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY PASSWORD=’Passw0rd1′

USE RKN_Test

GO

CREATE TABLE TestTable (FirstCol INT, SecondCol VARBINARY(256))

GO

SELECT * FROM TestTable

USE RKN_Test

GO

OPEN SYMMETRIC KEY RKN_TableKey DECRYPTION

BY PASSWORD=’Passw0rd1’

INSERT INTO TestTable VALUES (1,

ENCRYPTBYKEY(KEY_GUID(‘RKN_TableKey’),’Testing’))

GO

CLOSE SYMMETRIC KEY RKN_TableKey

USE RKN_Test

GO

OPEN SYMMETRIC KEY RKN_TableKey DECRYPTION

BY PASSWORD=’Passw0rd1′

SELECT FirstCol,CONVERT(VARCHAR(50),DECRYPTBYKEY(SecondCol)) AS DecryptedValue

FROM TestTable

GO

Advertisements

One thought on “Symmetric Key Encryption with SQL Server 2008

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s