SQL Server Encryption

Introduction

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.

Conclusion

Hope you all will like this article.