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 🙂
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
3 Comments on “SQL Server Column Level Encryption”
Nice one, thank you for sharing.
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’
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.
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…