Skip to main content

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 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:

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'), 'vmvadivel@yahoo.com')

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
D0683C5ABFA06734429C37BBDFB43C80A4EAA458678E328F4250A24AAEC74

EmailID = vmvadivel@yahoo.com

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

Comments

Anonymous said…
hey vadi nice to hear many important articles.
But here there is some wrong please modify it:

Decrypt the key and makes it available for use:

Open symmetric key symKeyemployees decryption by password = 'smart3a?'

Here "decryption by" must use not using.

ok.

Good luck

waiting for your reply...
radhekrsna@gmail.com

Popular posts from this blog

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul

AWS fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request

While using AWS and trying to copy a file from a S3 bucket to my EC2 instance ended up with this error message. Command Used: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ Error: fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request The error goes off if we add the region information to the command statement. I am using Asia Pacific (Mumbai) so used ap-south-1 as the region name. Modified Command: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ --region ap-south-1

[Non Tech] Want to know the recipe for Omelette :)

Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :) So in the first picture you see all the items which are needed for preparing an Omelette. When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition! I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :) Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :) After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :( I didn't leave it there. I took the bowl out of