SQLcommitted

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.

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

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

March 10, 2013 - Posted by SQLcommitted | Encryption | ALGORITHM, CONVERT, DecryptByKey, DECRYPTION, Encrypt, EncryptByKey, Encryption, HashBytes, Key_GUID, SQL Server, SQLcommitted, SYMMETRIC, T-SQL