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,
Age varbinary(300), -- this is the field we would encrypt while storing dataEmailID 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:
6. AES_192 and
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'), 'firstname.lastname@example.org')
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 means = 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
EmailID = email@example.com
Decrypt the data:
Select EmpId, Firstname, emailid,
convert(varchar(10), decryptbykey(Age, 1, convert(varchar(30), EmpId))) as Age
Close the symmetric Key:
Close symmetric key symKeyemployees
Run the below script to clean up:
Drop table tblEmployeeInfo
Drop symmetric key symKeyemployees