Encrypt and decrypt data in SQL Server 2005

http://sqlpractices.wordpress.com/2008/01/11/encrypt-and-decrypt-data-in-sql-server-2005/

 

In this article we would examine how to encrypt a column data in SQL Server 2005. Needless to say we would also look into the decryption part. The complete source code is also provided below for your better understanding.

Script for creating an EmployeeInfo table

Create table tblEmployeeInfo
(
EmpId int primary key,
Firstname varchar(50),
Age varbinary(300), – this is the field we would encrypt while storing data
EmailID varchar(50)
)

Generate a key to protect the AGE of the employee:

Create symmetric key symKeyemployees
with algorithm = TRIPLE_DES
encryption by password = ‘smart3a?’

Other encryption algorithm which we could use instead of TRIPLE_DES are:

1. DES,
2. RC2,
3. RC4,
4. DESX,
5. AES_128,
6. AES_192 and
7. AES_256

Decrypt the key and makes it available for use:

Open symmetric key symKeyemployees
using password = ‘smart3a?’ In order to know whether the key has been opened or not query sys.openkeys table as follows: Select * from sys.openkeys

Result of running the above query in our case is as follows: database_id = 8 
database_name = AdventureWorks 
key_id = 256
key_name = symKeyemployees 
key_guid = A383AB00-E692-49EE-9252-E6AD4371F865 
opened_date = 2005-08-27 09:52:07.693
status = 1

Since the ground work has been done, let us now insert some sample data into the table.

Insert into tblEmployeeInfo values
(
 1,
 ’Vadivel’,
 encryptbykey(key_guid(‘symKeyemployees’), ’29′, 1, ’1′),   ‘[e-mail]’
)

The 3rd parameter of encryptbykey is an integer which helps us to specify whether we are going to use an authenticator value or not. Value 1 means =  an authenticator value would be supplied. Value 0 meas = an authenticator value would not be supplied. By the way, this is the Default.

The next parameter is the actual authenticator data. In our case it is ’1′ because we are using EmpID as our authenticator value. FYI, default is NULL.

Query the table to find that the value of AGE field is encrypted.

Select * from tblEmployeeInfo

Result of running the above query would be something like this (Note that I have formatted the output for better understanding):

EmpID = 1 
Firstname = Vadivel  Age (Encrypted value) = x00AB83A392E6EE499252E6AD4371F86533996F7339B9E43B8D6477142A785FB6292
D0683C5ABFA06734429C37BBDFB43C80A4EAA458678E328F4250A24AAEC74  EmailID = [e-mail]

Decrypt the data:
Select
 EmpId,
 Firstname,
 emailid,
 convert(varchar(10), decryptbykey(Age, 1, convert(varchar(30), EmpId))) as Age
from tblEmployeeInfo

Close the symmetric Key:

Close symmetric key symKeyemployees

Run the below script to clean up:

Drop table tblEmployeeInfo
Drop symmetric key symKeyemployees