SQL Server Column Level Encryption

Hello Folks,

You would have heard about Cell-Level Encryption which was introduced earlier with the arrival of SQL Server 2005. It was built for the developers with a granular level of encryption for their applications that have specific data security requirements.

While on the other side, SQL Server Column Level Encryption provides much flexibility in terms of performance and space costs. The main difference between the Column-Level Encryption and Cell-Level Encryption is that the expense of column-level encryption is magnified by the number of rows in the table.

There are some pre-requisites before using Column-Level or Cell-Level Encryption please go through it:

• Column to be encrypted in the table schema should be stored as varbinary object.
• It requires additional processing and disk space overhead.
• The performance cost will be more while converting the data to and from a varbinary type.

It will become much clear to you, if you follow the example carefully:

First, I will let you know that how to encrypt the column which has values stored in it.

Here, I have created a table name “Student” inside “School” database.

In the above table, S_PhoneNumber_SSN is a Plain-text column, while S_PhoneNumber_SSN_asym is a Encypted column.

Now, it’s time to create the simple asymmetric key to use for encrypting the “S_PhoneNumber_SSN_asym” column as mentioned above:

CREATE ASYMMETRIC KEY dboAsymKey AUTHORIZATION dbo
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD =N'Piyush@Bajaj';

So, as to see the result, we have to insert some values:

DECLARE @number1 VARCHAR(11) = '9874303859'
DECLARE @number2 VARCHAR(11) = '9876453935'
DECLARE @number3 VARCHAR(11) = '9454738394'
INSERT into Student(S_ID, S_Name, S_City, S_PhoneNumber_SSN, S_PhoneNumber_SSN_asym)
VALUES
(1, 'Pallavi' , 'Bhilai' ,@number1, EncryptByAsymKey(AsymKey_ID('dboAsymKey'),@number1)),
(2, 'Pragya' , 'Delhi' ,@number2, EncryptByAsymKey(AsymKey_ID('dboAsymKey'),@number2)),
(3,'Avneet','Banglore',@number3, EncryptByAsymKey(AsymKey_ID('dboAsymKey'),@number3));

Let see the table now:

As you can see from the above, that S_PhoneNumber_SSN_asym is being encrypted into a non-readable format.

Therefore, it’s the right time now to show that how to decrypt the above encrypted code.

Write the following code to decrypt the above data:

SELECT CONVERT(VARCHAR(11), DecryptByAsymKey(AsymKey_ID('dboAsymKey'),S_PhoneNumber_SSN_asym, N'Piyush@Bajaj'))
AS Decrypted_SSN, * FROM Student;

It can be seen as:

Well, this was about Column-Level Encryption and Decryption from my side.

Please do comment on this 🙂

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook