Basic Column Encryption of data with SQL Server August 6th, 2014

Vinod Kumar

The more I talk with customers on basic architecture, more are the questions about how to implement. These scenarios when talked look great on whiteboard and when they implement and come back (say after 6 months) is completely different. These are some challenges when working with customer developer teams who has just started their career in writing DB level code.

One such scenario I talk to customers who have requirements of security is around column level encryption. It is one of the most simplest implementation and yet difficult to visualize. The scenario is simple where-in most of the HRMS (Hospital Management Systems) come with a simple requirement that data of one person must be masked to other.

So in this post, I thought it will be worth take a step-by-step tutorials to what I am talking. These capabilities are with SQL Server since the 2005 version and can be used by anyone. I am not talking about the infra best practices or the deployment strategy yet, that will be for a future post.

Creating Users for Demo

— Creating the Logins for demo
CREATE LOGIN doctor1 WITH password = ‘MyComplexPass@w0rd’
go
CREATE LOGIN doctor2 WITH password = ‘MyComplexPass@w0rd’
go
CREATE DATABASE hospitaldb
go
USE hospitaldb
go
CREATE USER doctor1
go
CREATE USER doctor2
go

For our example we have two doctors, we want to build a mechanism where Doctor1 patients details must not be visible to Doctor2. Let us create our simple table to keep values.

— Create tables
CREATE TABLE patientdata
(
id         INT,
name       NVARCHAR(30),
doctorname VARCHAR(25),
uid        VARBINARY(1000),
symptom    VARBINARY(4000)
)
go
— Grant access to the table to both doctors
GRANT SELECT, INSERT ON patientdata TO doctor1;
GRANT SELECT, INSERT ON patientdata TO doctor2;

Basic Encryption steps

Next step is to create our keys. To start with, we need to create our Master Key first. Then we will create the Certificates we will use. In this example, I am using a Symmetric key which will be encrypted by the certificates as part of logic.

— Create the Master Key
CREATE master KEY encryption BY password = ‘HospitalDBpwd@123’

CREATE CERTIFICATE doctor1cert AUTHORIZATION doctor1 WITH subject =
‘Doctor1cert’, start_date = ’07/07/2014′ 
GO
CREATE CERTIFICATE doctor2cert AUTHORIZATION doctor2 WITH subject =
‘Doctor2cert’, start_date = ’07/07/2014′ 
GO

CREATE symmetric KEY doctor1key AUTHORIZATION doctor1 WITH algorithm =
triple_des encryption BY certificate doctor1cert 
GO
CREATE symmetric KEY doctor2key AUTHORIZATION doctor2 WITH algorithm =
triple_des encryption BY certificate doctor2cert 
GO 

Let us next look at the Keys we just created using the DMVs.

SELECT *
FROM   sys.symmetric_keys 

A typical output looks like:

Adding Data into table

Next is to simulate as two different users and enter some data into our tables. Let us first impersonate as Doctor1 and enter values, next we will do it for Doctor2.

EXECUTE AS LOGIN = ‘Doctor1’

OPEN SYMMETRIC KEY doctor1key DESCRIPTION BY CERTIFICATE doctor1cert 

— View the list of open keys in the session
SELECT *
FROM   sys.openkeys 

Insert into our table.

INSERT INTO patientdata
VALUES      (1,
‘Jack’,
‘Doctor1’,
Encryptbykey(Key_guid(‘Doctor1Key’), ‘1111111111’),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Cut’))

INSERT INTO patientdata
VALUES      (2,
‘Jill’,
‘Doctor1’,
Encryptbykey(Key_guid(‘Doctor1Key’), ‘2222222222’),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Bruise’))

INSERT INTO patientdata
VALUES      (3,
‘Jim’,
‘Doctor1’,
Encryptbykey(Key_guid(‘Doctor1Key’), ‘3333333333’),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Head ache’))

In this example the Doc1 has 3 records for him. Next is to revert back to Doctor2 and do the same set of operations.

— Close all opened keys
CLOSE ALL symmetric keys

REVERT 

Impersonate as Doctor2 and do the same steps.

EXECUTE AS login = ‘Doctor2’

OPEN symmetric KEY doctor2key decryption BY certificate doctor2cert

–view the list of open keys in the session
SELECT *
FROM   sys.openkeys

INSERT INTO patientdata
VALUES      (4,
‘Rick’,
‘Doctor2’,
Encryptbykey(Key_guid(‘Doctor2Key’), ‘4444444444’),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Cough’))

INSERT INTO patientdata
VALUES      (5,
‘Joe’,
‘Doctor2’,
Encryptbykey(Key_guid(‘Doctor2Key’), ‘5555555555’),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Asthma’))

INSERT INTO patientdata
VALUES      (6,
‘Pro’,
‘Doctor2’,
Encryptbykey(Key_guid(‘Doctor2Key’), ‘6666666666’),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Cold’))

CLOSE ALL symmetric keys

— View the list of open keys in the session
SELECT *
FROM   sys.openkeys

REVERT

Check on the data

let us do a simple select on the table to check how the values are stored.

— Select data and see values encrypted
SELECT *
FROM   patientdata 

As you can see the values are not visible as-is but has some garbage.

Impersonate as Doctor and show Values

The next step is to show that Doctor1 can see his data and Doctor2 can see his data alone. The steps are simple:

EXECUTE AS LOGIN = ‘Doctor1’

OPEN SYMMETRIC KEY doctor1key decryption DESCRIPTION BY CERTIFICATE doctor1cert

SELECT id,
name,
doctorname,
CONVERT(VARCHAR, Decryptbykey(uid))      AS UID,
CONVERT (VARCHAR, Decryptbykey(symptom)) AS Symptom
FROM   patientdata

CLOSE ALL SYMMETRIC keys

REVERT 

The output would be like:

Now let us impersonate as Doctor2 and check for values.

EXECUTE AS LOGIN = ‘Doctor2’

OPEN SYMMETRIC KEY doctor2key decryption DESCRIPTION BY CERTIFICATE doctor2cert

SELECT id,
name,
doctorname,
CONVERT(VARCHAR, Decryptbykey(uid))      AS UID,
CONVERT (VARCHAR, Decryptbykey(symptom)) AS Symptom
FROM   patientdata

CLOSE ALL SYMMETRIC keys

REVERT 

The output for this stage would be:

Conclusion

As you can see this is a very simple implementation of column level encryption inside SQL Server and can be quite effective to mask data from each others in a multi-tenant environment. There are a number of reasons one can go for this solution. I thought this will be worth a shout even though the implementation has been in industry for close to a decade now.

Tags: , , , , , , , , , ,

This entry was posted on Wednesday, August 6th, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


3 Responses to “Basic Column Encryption of data with SQL Server”

  1. Krishna says:

    Thank you sir for this article. It helped me to accomplish my task of encrypting values

  2. Phaneendra says:

    Thanks Vinod for the nice article on the aspect which is niche. Felt it was Truly productive time that I had spent on reading and experimenting this blog and learnt the concept.

    Regards,
    Phaneendra.

Leave a Reply