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

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

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