SQL Server Encryption

Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 8471 red flag
Rating: 4.17 out of 5  
 6 vote(s)

This article deals with creating Different keys to encrypt data in SQL using the SQL Encryption.

 Download source code for SQL Server Encryption


Obfuscating data by the use of a key or password is called Encryption. Using Encryption we can make the data
useless without the corresponding decryption key or password for the Hackers.

Start creating necessary Keys

So Lets start with creating different all the necessary keys and then we will do a sample example.

First We will start with the Service Master Key

The Service Master Key is the root of the SQL Server encryption hierarchy which is generated automatically the first time which we can regenerate, Backup and restore so at to use the same across servers.

Next we will create a Master Key..

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. We can encrypt this master key using a password. We can backup and restore this too.

Before we create other keys Open The master Key

Next we will create a Certificate with a subject and a start date. Here start date is the date on which the certificate becomes valid.We can backup this to a file encrypting it with a password.

Next we will create a Symmetric key with an algorithm, key source and an identity value this will help us to generate the same symmetric key across servers and this is very important since we will use this symmetric key to encrypt or decrypt column values. This doesn't have any Backup and restore commands.

Different algorithms can be used DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256

We will use Triple_DES in our case.

Once we are done with these keys we can view them using the below commands.

Lets start using these with an example.

We will create a table called employee with id, name, age and SSN. SSN being a sensitive info we will encrypt while inserting data and decrypt while retrieving.

Here we have used Varbinary to store the encrypted SSN data and length depends on the algorithm used.

Now that we have created the Table we will insert a record into this table.

To Insert data we need to first open the symmetric key and then use the EncryptByKey function to use the GUID that got created for the Symmetric key. And finally close the symmetric key. We can place the above lines in a stored procedure also.

Next we will see how we can retrieve this encrypted info.



Hope you all will like this article.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Madhu Rokkam
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/13/2011 3:13:20 PM
Country: India
Thanks and Regards Madhu

Login to vote for this post.

Comments or Responses

Posted by: Sashys on: 5/7/2011 | Points: 25
Hi Madhu,
I downloaded all your scripts, i was confused a bit initally[With backup and restore queries] but worked fine at last.. Thanks madhu.. I hope the backup and restore is totally optional, but required for future use right.

It was really good madhu..
Posted by: Lakn2 on: 6/30/2011 | Points: 25
good one

Login to post response

Comment using Facebook(Author doesn't get notification)