Encrypt and Decrypt the columns in table – SQL Server 2005


In a company have been asked to encrypt the few critical table columns contains containing sensitive data like Credit Card No, SSN even monthly generated payslip amount. Using SQL Server 2005 or 2008 we can encrypt data using different algorithms by creating symmetric and asymmetric keys.

How much does it costs to company, By writing lengthy code and changing the application at later stage and some projects analyst fail to consider encrypting sensitive data ( This happen in one of the company).

Using the SQL 2005 or 2008 we can encrypt and decrypt the columns using symmetric and asymmetric keys, without changing application code.

1.       Create database and Tables

use master

go

create database HR

go

use HR

go

create table Employee

(

     EmployeeID INT PRIMARY KEY,

     EmployeeName VARCHAR(55)      not null,

     EmployeeSSN VARCHAR(MAX)           

);

go

create table tmpEmployee

(

     tmpEmployeeID INT PRIMARY KEY,

     tmpEmployeeSSN VARCHAR(128)             

);

2.       Create a Master Key

USE master 

GO    

CREATE MASTER KEY 

     ENCRYPTION BY PASSWORD = ‘MySTr0ngPass@Me’

 

 There would be only one master key for SQL Instance and without the Master key backup, encrypted data cannot be viewed

3.       Create an Asymmetric key – you can use SymKey and AsymKey key depends on your choice and each key has its own pros and cons and also there is performance impact. Considering which column to encrypt is most important.

use HR

go

CREATE ASYMMETRIC KEY MyFirstAsyMKey  

    WITH ALGORITHM = RSA_512  ;            

4.       Create a database trigger  for insert statement for table Employee. When insert is done from application column EmployeeSSN will be encrypted using the Asymmetric Key which we have created in the earlier.

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create  trigger ForInsert on Employee

After insert

as

begin

 insert into tmpEmployee select EmployeeID,EmployeeSSN from inserted

 update Employee set EmployeeSSN = (select EncryptByAsymKey(AsymKey_ID(‘MyFirstAsyMKey’),tmpEmployeeSSN) FROM tmpEmployee , Employee

                                    where  Employee.EmployeeID = tmpEmployee.tmpEmployeeID)

                                    FROM tmpEmployee , Employee where Employee.EmployeeID = tmpEmployee.tmpEmployeeID

      truncate table tmpEmployee        

end

5.       Create database view to see the decrypted data and only application account or user should have access for this view so that other used do not read the data. This view is created using same Asymmetric Key

 

create view ViewEmployee

as

   select  EmployeeID, EmployeeName,

            convert(varchar(128),DecryptByAsymKey(AsymKey_ID(‘MyFirstAsyMKey’), EmployeeSSN)) as EmployeeSSN

      from Employee

go

7.   Let’s try inserting some data to employee table

 

insert into Employee (EmployeeID,EmployeeName,EMployeeSSN )

             values (1,’Amar’,’SSN100001′)

go

insert into Employee (EmployeeID,EmployeeName,EMployeeSSN )

             values (2,’Michel’,’SSN100002′)

go

insert into Employee (EmployeeID,EmployeeName,EMployeeSSN )

             values (3,’Don’,’SSN100010′)

8 . View the employee table and decrypted data by view

select * from Employee

go

select * from ViewEmployee

next will write for employee table update

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: