Encryption in SQL Server using symmeteric key

Today I learnt a very exciting feature encryption mechanism of SQL server 2005/2008.

SQL Server provides the following method to encrypt your data…

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric key
• ENCRYPTION by asymmetric key
• ENCRYPTION by certificate

I learnt using Symmetric keys so I will explain you that.

To achieve this we will create database, database master key, certificate and symeteric key.

Script for create database.

-- First Create a DATABASE

CREATE DATABASE EncryptSample ON PRIMARY

(NAME=N'EncryptSample' ,FILENAME=N'C:\EncryptSample.mdf')

LOG ON

(NAME=N'EncryptSample_LOG' ,FILENAME=N'C:\EncryptSample.ldf')

Using this database we will create database master key, certificate and symeteric key.

Use EncryptSample

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = 'gHt56$8abc'

Go

CREATE CERTIFICATE EncryptCert

WITH SUBJECT = 'EncryptCert'

Go

CREATE SYMMETRIC KEY Imagekey

WITH KEY_SOURCE = 'I am implementing encryption',

IDENTITY_VALUE = 'encryption identity',

ALGORITHM = Triple_DES

ENCRYPTION BY CERTIFICATE EncryptCert;

Now create a sample table and see the result.

USE EncryptSample

GO

CREATE TABLE TestTable (Id INT, [Rank] VARCHAR(50))

GO

INSERT INTO TestTable (Id,[Rank])

SELECT 1,'First'

UNION ALL

SELECT 2,'Second'

UNION ALL

SELECT 3,'Third'

UNION ALL

SELECT 4,'Fourth'

UNION ALL

SELECT 5,'Fifth'

GO

Check the content of the TestTable

SELECT *
FROM TestTable
GO









Let’s add a new column of type varbinary to original table, which will store the encrypted value of [Rank] Column

ALTER TABLE TestTable

ADD EncryptRank VARBINARY(256)

Let’s update the new column with encrypted data created by certificate and symeteric key.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'gHt56$8abc'

OPEN SYMMETRIC KEY Imagekey DECRYPTION

BY CERTIFICATE EncryptCert

UPDATE TestTable

SET EncryptRank = ENCRYPTBYKEY(KEY_GUID('ImageKey'),[Rank])

GO

Close Master key

Check the content of the first table

SELECT *

FROM TestTable

Fetch the encrypted data.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'gHt56$8abc'

OPEN SYMMETRIC KEY Imagekey DECRYPTION

BY CERTIFICATE EncryptCert

SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptRank)) AS DecryptSecondCol

FROM TestTable

Close master key