Symmetric Key (Encryption – Decryption in SQL Server 2008 Part 6)

Well this is sixth article in the series of Encryption and Decryption. It was really good to have good response from all of you about this series. I would like to give links about past 5 article in these series for those who directly landed to this page.

Ok, so above links are well enough, if you want to get the concept of encryption – decryption in Microsoft SQL Server 2008 from scratch, do read all articles given above too from part 1.
Anyway, let us move ahead with Symmetric key. This is at the very bottom level in hierarchy of Encryption-Decryption. Further you can refer hierarchy chart given in my first article.  Symmetric key can either use same key for encryption and decryption or use different key for encryption and decryption but do keep in mind that both the keys are mathematically related via simple transformation. Symmetric key could be encrypted by Certificates or via Asymmetric key as per your need.
Use AdventureWorks
Create Table emps
      name varchar(20),
      dept varchar(20),
      EncryptedName Varbinary(256),
      DecryptedName Varchar(20)
Insert into emps (Name,Dept)
Select ‘Ritesh’,‘MIS’ union all
Select ‘Rajan’,‘Acct’
Select * from emps
–create Database Master Key (DMK)
Create master key
Encryption by Password =‘$qlhub’
–create certificate which will be used to
–encrypt symmetric key
Create Certificate AdvCert
With Subject= ‘Certificate to encrypt emps table’,
Start_date = ‘2009-09-29’,
Expiry_date =‘2012-02-07’
–creating symmetric key
Create Symmetric Key AdvSym
With Algorithm =AES_256
Encryption by Certificate AdvCert
–once you create symmetric key
–you need to open it before use.
Open Symmetric key AdvSym
Decryption by certificate AdvCert
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName from emps
–now this is time to update table
update emps set EncryptedName=EncryptByKey(Key_Guid(N‘AdvSym’),Name)
–let us check data
select * from emps
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName,
Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
–now this is time to decrypt data and update table.
Update emps set DecryptedName=Convert(Varchar(20), DecryptByKey(EncryptedName))
–check your data again
select * from emps
So this is how Symmetric key works!!!
I want to add one more article in this series which will be complete practice script from top to bottom.
Hope to have it for you very soon.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of

4 Responses to “Symmetric Key (Encryption – Decryption in SQL Server 2008 Part 6)”

  1. Anonymous Says:

    hi Ritesh,ok,its working fine in sql server gui's(sql server mangement studio)but i want to do same from my with c# app.please forword answer to my,

  2. Ritesh Shah Says:

    You can use all these statements like normal SELECT, UPDATE, INSERT TSQLs in your .NET code. whats the problem?

  3. Ritesh Shah Says:

    setting of database key, certificate or/else symmetric key should be done from SSMS as it is one time process than you can use it in SELECT, UPDATE, INSERT etc. statements like normal TSQLs

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: