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.

1_SQL_Server_Column_Level_Encryption

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:

2_SQL_Server_Column_Level_Encryption

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:

3_SQL_Server_Column_Level_Encryption

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

3 Comments on “SQL Server Column Level Encryption”

  1. Hi Piyush,

    Have a douth.

    A service master key(SMK) is already created during SQL Server setup.

    But when we go for column level encryption we have to first create database master key(DMK) & this DMK is encrypted by password.

    Syntax:CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’

    My question:

    1. Later on where this password is asked for decryption?

    2. What is the use of this password?

    Because while backup this DMK if we supply different password it is ok.

  2. Question:- I have a table and one of the columns is VARBINARY(MAX) in which i have bulk uploaded JPEG, PDF files…

    Now, i want to Encrypt this VARBINARY(MAX) column and store the encrypted data in another column..

    Can you help me how to do this? Because when i try to do this, i get “String or binary data would be truncated”

    I am using sql 2005… My table structure is as follows:-

    Column Data Type Description

    tbId Int Identity(1,1)
    tbName Varchar(50) File Name
    tbDesc Varchar(100) File Description
    tbBin varbinary(max) Actual File uploaded in this column
    EnryptedtbBin varbinary(max) Encrypted tbBin column

    Please help me out… Its driving me nuts…

    Regards,
    Ravinder

Leave a Reply

Your email address will not be published. Required fields are marked *