SQLcommitted

Committed with SQL

SQL Server– How to Encrypt Column Data

Encryption is one of the most secure way to protect your confidential data like Social Security Number, Date Of Birth , Patient clinical information etc. We need to encrypt certain data to meet the business requirements and sometime to meet certain compliance. Here we’ll see how can we encrypt column data using symmetric key encryption in SQL Server 2012.

data-encryption

DEMO

Lets first create a table and insert a record into it. Here in this demo we’ll encrypt the RegdNumber column data.

CREATE TABLE  Employee (EmpID int, EmpName varchar(100), RegdNumber nvarchar(20), EncryptedRegdNumber varbinary(128))
GO

INSERT  Employee (EmpID, EmpName, RegdNumber)
Values (1,‘Jackson’,N’Regd_AA1′)

 

 

 

Steps to encrypt column data                                                                                   

Step 1- Check if master key does not exist then create one

 

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = ‘abc12$$^7′
GO

Note : You should back up the master key by using BACKUP MASTER KEY and store the backup in a secure, off-site location.

Step 2 – Create certificate

CREATE CERTIFICATE EmployeeCert
   WITH SUBJECT = ‘Employee RegdNumber’;
GO

Step 3 – Create symmetric key using the certificate

CREATE SYMMETRIC KEY EmployeeRegdNumberKey_1
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE EmployeeCert;
GO

Step 4 – Open the symmetric key using  which the data will be encrypted.

OPEN SYMMETRIC KEY EmployeeRegdNumber
   DECRYPTION BY CERTIFICATE EmployeeCert;

Step 5 – Encrypt the value in column RegdNumber using the symmetric key EmployeeRegdNumberKey_1.

UPDATE Employee
SET EncryptedRegdNumber = EncryptByKey(Key_GUID(‘EmployeeRegdNumber’)
    ,RegdNumber , 1, HashBytes(‘SHA1′, CONVERT( varbinary , EmpID)));
GO

 

Now query the original RegdNumber, encrypted RegdNumber and decrypted RegdNumber.  If encryption is working fine the the original RegdNumber column value should match with DecryptedRegdNumbers

SELECT RegdNumber ,EncryptedRegdNumber    AS ‘Encrypted RegdNumber’,

CONVERT(nvarchar,   DecryptByKey(EncryptedRegdnumbet, 1 ,HashBytes(‘SHA1′, CONVERT(varbinary, empid))))  AS ‘Decrypted RegdNumber’

FROM Employee;
GO

The above script you can use in your local environment to check how you can encrypt column data.

Hope the above demo will help to understand how to encrypt column data.

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

About these ads

March 10, 2013 - Posted by | Encryption | , , , , , , , , , , , ,

No comments yet.

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

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: