Encrypting SQL Server Columns

If you have been working with databases long with large company you would have definitely encountered a requirement to encrypt databases and data. Especially if you are dealing with NPI, PCI or HIPPA data.   Most of the control requirements state that sensitive data needs to be encrypted . Well with all the hacking and leaking of credit card data lately , we all know how important  its to secure your system and data.  As DBAs and data custodians its our job to ensure that sensitive data is protected.

While there are many products that will encrypt and protect databases ( I am not going to do any sales pitch of any third party product), there are encryption available in off the shelves databases.  IBM has a encrypt and decrypt function, but its not robust enough to pass audits, you will have to buy additional product. Oracle has the same functionality, but again its a licensed product that you will need to buy.  However, SQL Server has encryption features built in to their standard and enterprise edition that will do the job.

They have a feature called Transparent Database Encryption  that will encrypt the entire database with a master key and certificate.  TDE will encrypt the database, protecting folks from restoring the database onto another system without knowing the key or certificate. It will also prevent folks from copying the database file off the server. It however won’t encrypt the data, meaning if you query the table, you will see the data in plain text. So this is not a solution for encrypting sensitive columns.  Here is a glimpse of how you can setup TDE

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MySecretPassword1@#$’;
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My Own Certificate’;
go
USE PaymentDB;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE PaymentDB
SET ENCRYPTION ON;
GO

What if you want to encrypt a column ?  Lets say you have a table that credit card numbers and you don’t  want anybody other than the application to see the credit card number, even the DBA shouldn’t be able to see the  data. What you can do is create a master key and a certificate, then create a symmetric key against the database using the certificate then encrypt the column during insert and then decrypt the column when you select. Well this will ensure that a regular user who doesn’t have the authority to open the symmetric key will not be able to see the data in plain text. However it doesn’t stop a DBA from seeing the data. So lets take a little further . One other thing many audit checks is separation of duties. In many places the server system admin won’t have access to databases and DBA won’t have all the privileges to system, especially if it contains sensitive data.  And changes are there is a third party product securing the file systems. So what I suggest is on top of the symmetric key encryption, use a password based SHA1 or SHA2 hashbyte logic to further encrypt the column. The hashbyte password can be stored in a file system protected by security product that the dba cannot access.  Below I will show you my suggestions :

Lets say we have the following table on PaymentDB with some data :

Create table payment
(OrderID  int
,CreditCardNumber varchar(16)
,ExpirationDate char(5)
,CVCode  char(4)
);

Now lets start working in creating the keys and certificate

use PaymentDB;
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = ‘MySecretPassword1@#$’;

CREATE CERTIFICATE CardCert
WITH SUBJECT = ‘CreditCard Number’;

CREATE SYMMETRIC KEY CreditCardKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CardCert;
GO

At this point we have a symmetric key that we can use to encrypt the column.  Ensure you save the password you used to create the master key or better yet backup the master key using  BACKUP MASTER KEY   . In order to for the ease of explanation , lets add another column that we can use to encrypt the data

ALTER TABLE Payment
ADD EncryptedCreditCardNumber  varbinary(max);

Now lets update the table and encrypt the credit card number on the new column. I am also going to add the HASHBYTES using a password ‘ABC%’ . This can/should be a long password.

OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY CERTIFICATE CardCert;
update payment set EncryptedCreditCardNumber = EncryptByKey(Key_GUID(‘CreditCardKey’),CreditCardNumber,1,HASHBYTES(‘SHA1′,CONVERT(varbinary ,’ABC%’)));
CLOSE SYMMETRIC KEY CreditCardKey;

Now lets do a simple select :

select * from payment

You should see that the EncryptedCreditCardNumber column now contains encrypted data.

Lets see how we can decrypt it :

OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY CERTIFICATE CardCert;
select CreditCardNumber,
CONVERT(varchar, DecryptByKey(CreditCardNumberEcrypted))
,CONVERT(varchar,DecryptByKey(CreditCardNumberEcrypted,1,HASHBYTES(‘SHA1′,CONVERT(varbinary ,’ABC%’))))
from payment;
CLOSE SYMMETRIC KEY CreditCardKey;

Did you notice that on the select I also have the DecryptByKey without the hashbytes. Thats to show that the data is still encrypted without specifying the hashbytes password.
But this still have the hashbytes password in the sql.

To take it a step further lets save the hashbytes password in a file called secret.txt and have   it secured by the OS or a third party product.  We can then read that in a TSQL or Stored Procedure.  Here is a quick and dirty stored procedure I wrote , but you can write plain sql to do it too

How to insert it :

USE [PaymentDB]
GO

CREATE PROCEDURE [dbo].[InsertPaymentInfo]
( @IOrderID  int ,
@ICreditCardNumber varchar(16),
@IExpirationDate varchar(5),
@ICVCode char(4)
)
AS
BEGIN
DECLARE @secretkey varchar(256);

create table #secrettemp (secret varchar(256))
bulk insert #secrettemp from ‘D:\protectedfiles\secret.txt’;
select @secretkey = secret from #secrettemp

OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY CERTIFICATE CardCert;
insert into dbo.payment
(OrderID
,CreditCardNumber
,ExpirationDate
,CVCode
,EncryptedCreditCardNumber
)
values
(@IOrderID
,@ICreditCardNumber
,@IExpirationDate
,@ICVcode
,EncryptByKey(Key_GUID(‘CreditCardKey’),@ICreditCardNumber,1,HASHBYTES(‘SHA1’,CONVERT(varbinary ,@secretkey)))
);

CLOSE SYMMETRIC KEY CreditCardKey;
drop table #secrettemp;
END

The above procedure reads the file and stored the hashbytes password in a variable and then pass it to EncryptByKey function. The OS can set restriction on only the app user id can read that file.

Here is a stored procedure to decrypt and select the table.

USE [PaymentDB]
GO

CREATEPROCEDURE [dbo].[ReadPaymentInfo]
(
@OOrderId int output,
@OCreditCardNumber varchar(16) output,
@OExpirationDate varchar(5) output,
@OCVcode varchar(4) output,
@ODeCryptedCreditCardNumber varchar(16) output
)
AS
BEGIN
DECLARE @secretkey varchar(256);

create table #secrettemp (secret varchar(256))
bulk insert #secrettemp from ‘D:\protectedfiles\secret.txt’; 
select @secretkey = secret from #secrettemp

OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY CERTIFICATE CardCert;
Set RowCount 1
Select
@OOrderID = OrderID,
@OCreditCardNumber = CreditCardNumber,
@OExpirationDate = ExpirationDate,
@OCVCode = CVCode,
@ODecryptedCreditCardNumber = CONVERT(varchar,DecryptByKey(EncryptedCreditCardNumber,1,HASHBYTES(‘SHA1’,CONVERT(varbinary ,@secretkey))))
From Payment
Order By OrderID;

CLOSE SYMMETRIC KEY CreditCardKey;
drop table #secrettemp;
END

We use the bulk insert #secrettemp from ‘D:\protectedfiles\secret.txt’  to read in the password from the protected file to pass the value to the DecryptByKey function.

If you want your application userid or app role to use the symmetric key then you need to grant VIEW DEFINITITION ON SYMMETRIC KEY and VIEW DEFINITIONS ON CERTIFICATE to the app user id or role.

This is one of the many few ways to use the built in feature in SQL Server.   As usual please test and implement at your own risk