{"id":1435,"date":"2014-02-12T10:45:17","date_gmt":"2014-02-12T04:45:17","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1435"},"modified":"2016-01-07T11:31:03","modified_gmt":"2016-01-07T05:31:03","slug":"encrypting-sql-server-columns","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1435","title":{"rendered":"Encrypting SQL Server Columns"},"content":{"rendered":"

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. \u00c2\u00a0 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 \u00c2\u00a0its to secure your system and data. \u00c2\u00a0As DBAs and data custodians its our job to ensure that sensitive data is protected.<\/p>\n

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. \u00c2\u00a0IBM 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. \u00c2\u00a0However, SQL Server has encryption features built in to their standard and enterprise edition that will do the job.<\/p>\n

They have a feature called Transparent Database Encryption<\/a>\u00c2\u00a0 that will encrypt the entire database with a master key and certificate. \u00c2\u00a0TDE 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. \u00c2\u00a0Here is a glimpse of how you can setup TDE<\/p>\n

USE master;<\/span>
\nGO<\/span>
\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MySecretPassword1@#$’;<\/span>
\ngo<\/span>
\nCREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My Own Certificate’;<\/span>
\ngo<\/span>
\nUSE PaymentDB;<\/span>
\nGO<\/span>
\nCREATE DATABASE ENCRYPTION KEY<\/span>
\nWITH ALGORITHM = AES_256<\/span>
\nENCRYPTION BY SERVER CERTIFICATE MyServerCert;<\/span>
\nGO<\/span>
\nALTER DATABASE PaymentDB<\/span>
\nSET ENCRYPTION ON;<\/span>
\nGO<\/span><\/p>\n

What if you want to encrypt a column ? \u00c2\u00a0Lets say you have a table that credit card numbers and you don’t \u00c2\u00a0want anybody other than the application to see the credit card number, even the DBA shouldn’t be able to see the \u00c2\u00a0data. 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. \u00c2\u00a0And 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. \u00c2\u00a0Below I will show you my suggestions :<\/p>\n

Lets say we have the following table on PaymentDB with some data :<\/p>\n

Create table payment<\/span>
\n(OrderID \u00c2\u00a0int<\/span>
\n,CreditCardNumber varchar(16)<\/span>
\n,ExpirationDate char(5)<\/span>
\n,CVCode \u00c2\u00a0char(4)<\/span>
\n);<\/span><\/p>\n

Now lets start working in creating the keys and certificate<\/p>\n

use PaymentDB;<\/span>
\nCREATE MASTER KEY ENCRYPTION BY<\/span>
\n PASSWORD = ‘MySecretPassword1@#$’;<\/span><\/p>\n

CREATE CERTIFICATE CardCert<\/span>
\n WITH SUBJECT = ‘CreditCard Number’;<\/span><\/p>\n

CREATE SYMMETRIC KEY CreditCardKey<\/span>
\n WITH ALGORITHM = AES_256<\/span>
\n ENCRYPTION BY CERTIFICATE CardCert;<\/span>
\n GO<\/span><\/p>\n

At this point we have a symmetric key that we can use to encrypt the column. \u00c2\u00a0Ensure you save the password you used to create the master key or better yet backup the master key using \u00c2\u00a0BACKUP MASTER KEY\u00c2\u00a0\u00c2\u00a0<\/span>\u00c2\u00a0. In order to for the ease of explanation , lets add another column that we can use to encrypt the data<\/p>\n

ALTER TABLE Payment<\/span>
\nADD EncryptedCreditCardNumber \u00c2\u00a0varbinary(max);<\/span><\/p>\n

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.<\/p>\n

OPEN SYMMETRIC KEY CreditCardKey<\/span>
\n DECRYPTION BY CERTIFICATE CardCert;<\/span>
\nupdate payment set EncryptedCreditCardNumber = EncryptByKey(Key_GUID(‘CreditCardKey’),CreditCardNumber,1,HASHBYTES(‘SHA1′,CONVERT(varbinary ,’ABC%’)));<\/span>
\nCLOSE SYMMETRIC KEY CreditCardKey;<\/span><\/p>\n

Now lets do a simple select :<\/p>\n

select * from payment<\/span><\/p>\n

You should see that the EncryptedCreditCardNumber column now contains encrypted data.<\/p>\n

Lets see how we can decrypt it :<\/p>\n

OPEN SYMMETRIC KEY CreditCardKey<\/span>
\n DECRYPTION BY CERTIFICATE CardCert;<\/span>
\n select CreditCardNumber, <\/span>
\nCONVERT(varchar, DecryptByKey(CreditCardNumberEcrypted))<\/span>
\n ,CONVERT(varchar,DecryptByKey(CreditCardNumberEcrypted,1,HASHBYTES(‘SHA1′,CONVERT(varbinary ,’ABC%’))))<\/span>
\n from payment;<\/span>
\n CLOSE SYMMETRIC KEY CreditCardKey;<\/span><\/p>\n

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.
\nBut this still have the hashbytes password in the sql.<\/p>\n

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

How to insert it :<\/p>\n

USE [PaymentDB]<\/span>
\n GO<\/span><\/p>\n

CREATE PROCEDURE [dbo].[InsertPaymentInfo]<\/span>
\n ( @IOrderID \u00c2\u00a0int ,<\/span>
\n @ICreditCardNumber varchar(16),<\/span>
\n @IExpirationDate varchar(5),<\/span>
\n @ICVCode char(4)<\/span>
\n )<\/span>
\n AS<\/span>
\n BEGIN<\/span>
\n DECLARE @secretkey varchar(256);<\/span><\/p>\n

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

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

CLOSE SYMMETRIC KEY CreditCardKey;<\/span>
\n drop table #secrettemp;<\/span>
\n END<\/span><\/p>\n

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.<\/p>\n

Here is a stored procedure to decrypt and select the table.<\/p>\n

USE [PaymentDB]<\/span>
\n GO<\/span><\/p>\n

CREATEPROCEDURE [dbo].[ReadPaymentInfo]<\/span>
\n (<\/span>
\n @OOrderId int output,<\/span>
\n @OCreditCardNumber varchar(16) output,<\/span>
\n @OExpirationDate varchar(5) output,<\/span>
\n @OCVcode varchar(4) output,<\/span>
\n @ODeCryptedCreditCardNumber varchar(16) output<\/span>
\n )<\/span>
\n AS<\/span>
\n BEGIN<\/span>
\n DECLARE @secretkey varchar(256);<\/span><\/p>\n

create table #secrettemp (secret varchar(256))<\/span>
\n bulk insert #secrettemp from ‘D:\\protectedfiles\\secret.txt’;\u00c2\u00a0<\/span>
\n select @secretkey = secret from #secrettemp<\/span><\/p>\n

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

CLOSE SYMMETRIC KEY CreditCardKey;<\/span>
\n drop table #secrettemp;<\/span>
\n END<\/span><\/p>\n

We use the\u00c2\u00a0bulk insert #secrettemp from ‘D:\\protectedfiles\\secret.txt’\u00c2\u00a0<\/span>\u00c2\u00a0to read in the password from the protected file to pass the value to the DecryptByKey function.<\/p>\n

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.<\/p>\n

This is one of the many few ways to use the built in feature in SQL Server. \u00c2\u00a0 As usual please test and implement at your own risk<\/p>\n","protected":false},"excerpt":{"rendered":"

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. \u00c2\u00a0 Most of the control requirements state that sensitive data needs to be encrypted . Well with all the hacking and […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[358,1,658],"tags":[891,890,887,886,421,888,892,306,885,889],"class_list":["post-1435","post","type-post","status-publish","format-standard","hentry","category-how-to","category-misc","category-sql-server-2","tag-audits","tag-credit-card","tag-decryptbykey","tag-encryptbykey","tag-encryption","tag-hashbytes","tag-pci","tag-sql-server","tag-symmetric-key","tag-tde"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1435"}],"version-history":[{"count":2,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1435\/revisions"}],"predecessor-version":[{"id":1437,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1435\/revisions\/1437"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1435"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}