Encrypt And Decrypt Column Data In SQL Server

Sometimes, we may want to encrypt a SQL Server column data, such as a credit card number. In this blog, let's learn how we can encrypt and decrypt SQL Server column data in the database itself.

Recently, I worked on a project to hide sensitive data. Basically, the client wanted sensitive data to be encrypted, then accessed and decrypted by the application only. In case a hacker or an employee or a DBA accesses data directly, they can't read the field. Some perfect examples are customer's credit card number, date of birth, social security, or even medical records.

 

SQL Server provides a feature that allows DBAs and data developers to encrypt and save encrypted data on a column level. Once a column is encrypted, it's not readable by humans.

 

In this blog, let's see how this can be acheieved.  

 

In the below example, I have used the Credit Card Number column to be encrypted.

 

You need to write a stored procedure to execute a set of statements and queries. Though it is not a foolproof way to encrypt or decrypt at the database level, while working on this task, I learned some good techniques and features of SQL Server.

 

There are 3 major factors to encrypt data at the column level, as below.

1. Master Key -  a key which is used to protect the keys of certificates and symmetric keys in the database
2. Certificates - used to encrypt the data in the database
3. Symmetric Key - can be encrypted by using many options, like certificate, password, symmetric key. There are different algorithms available for encrypting a key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

So, let's start step by step and achieve the encryption and decryption.

 

Step 1

 

Create a Master Key first with the help of the below script.

use TestingDB; //This is the Test Database created.
Create master key encryption by password ='abc123'

Step 2

 

Once the Master Key is created, now it's time to create a Certificate.

Create certificate C1 with subject = 'Credit Card Data'

Step 3

 

Now with the help of certificate and master key create SYMMETRIC KEY.

 

Create symmetric key SK1 with algorithm = AES_256 encryption by certificate C1.

 

Once all these KEYs are created in the database, we can use those for encrypting and decrypting data.

 

Below is the script to encrypt the data in the column. Here I have created one TABLE named TestEncryption having 3 columns with its datatype as below. Note that the column in which we want to insert or update encrypted data should have VARBINARY as the datatype.

1. Id - INT
2. EncryptedCCNumber - varbinary (256)
3. CCNumber - Numeric(18,0)

Let's insert data in the column of the table,

 

Open symmetric key SK1

 

Decryption by certificate C1

insert into TestEncryption(Id, EncryptedCCNumber, CCNumber) values (1, ENCRYPTBYKEY(key_guid('SK1'),'5000'), '5000')

Close symmetric key SK1

 

Now it's time to check if the data is encrypted or not so when you execute a simple query you will get the data from the Table as it is.

select * from TestEncryption

IF you want to DECRYPT the data you can use the below script

 

Open symmetric key SK1

 

Decryption by certificate C1

select *, convert(varchar, DECRYPTBYKEY(EncryptedCCNumber)) as 'Decrypted CC Number' from TestEncryption

Close symmetric key SK1

 

You will get an extra column named "Decrypted CC Number". This way we can insert/update and select the encrypted data from the table in SQL Server.

 

Happy Coding!!