solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server Column Level Encryption Example using Symmetric Keys

MSSQLTips author Nitansh Agarwal By:   |   Read Comments (24)   |   Related Tips: More > Encryption
Problem

Financial firms have sensitive data including credit card numbers, social security numbers, financial history, etc.  Based on the sensitivity of the data, it needs to be secured and protected from unauthorized access. With data stored in tables, you have a few options to protect data.  First, you can protect the data using views. Second, we can also assign column level permissions to users.  Are there any other options available?  Can I use database encryption? Can I encrypt only a column in my table or do I need to encrypt the whole database? Check out this tip to learn more about column level encryption.

Solution

An important option to be considered during restricting unauthorized access to data is to encrypt data so that even if somebody was able to reach to data it is not discernable by a simple query. Since critical information like credit card numbers are stored in column or two, it does not make sense to encrypt the complete database or database files. In this tip I will walk through the processes of encrypting a column in a table which contains credit card information of customers of XYZ company by using SQL Server symmetric key encryption.  SQL Server has an encryption hierarchy that needs to be followed in order to support the encryption capabilities. We will follow the same hierarchy in the subsequent steps of this tip.

Step 1 - Create a sample SQL Server table

Let's use an example where we create the dbo.Customer_data table which contains credit card details for customers. Our task is to protect this data by encrypting the column, which contains the credit card number. I will populate it will some sample data as shown below.

USE encrypt_test;
GO
-- Create Table
CREATE TABLE dbo.Customer_data
(Customer_id int constraint Pkey3 Primary Key NOT NULL,
Customer_Name varchar(100) NOT NULL,
Credit_card_number varchar(25) NOT NULL)
-- Populate Table
INSERT INTO dbo.Customer_data 
VALUES (74112,'MSSQLTips2','2147-4574-8475')
GO
INSERT INTO dbo.Customer_data 
VALUES (74113,'MSSQLTips3','4574-8475-2147')
GO
INSERT INTO dbo.Customer_data 
VALUES (74114,'MSSQLTips4','2147-8475-4574')
GO
INSERT INTO dbo.Customer_data 
VALUES (74115,'MSSQLTips5','2157-1544-8875')
GO
-- Verify data
SELECT * 
FROM dbo.Customer_data
GO
Sample Credit Card data not encrypted

Step 2 - SQL Server Service Master Key

The Service Master Key is the root of the SQL Server encryption hierarchy. It is created during the instance creation. Confirm it's existence using the query below. If it does not exist we need to manually create it. Read more about service master key here.

USE master;
GO
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';
GO

Step 3 - SQL Server Database Master Key

The next step is to create a database master key. This is accomplished using the CREATE MASTER KEY method. The "encrypt by password" argument is required and defines the password used to encrypt the key. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data encryption. It is important that you keep the encryption password in a safe place and/or keep backups of your SQL Server Database Master Key.

-- Create database Key
USE encrypt_test;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

Step 4 - Create a Self Signed SQL Server Certificate:

The next step is to create a self-signed certificate that is protected by the database master key. A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. An optional argument when creating a certificate is ENCRYPTION BY PASSWORD. This argument defines a password protection method of the certificate's private key. In our creation of the certificate we have chosen to not include this argument; by doing so we are specifying that the certificate is to be protected by the database master key. Read more on about SQL Server certificates.

-- Create self signed certificate
USE encrypt_test;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

Step 5 - SQL Server Symmetric Key

A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database. Read more about SQL Server Symmetric Keys.

-- Create symmetric Key
USE encrypt_test;
GO
CREATE SYMMETRIC KEY SymmetricKey1 
 WITH ALGORITHM = AES_128 
 ENCRYPTION BY CERTIFICATE Certificate1;
GO

Step 6 - Schema changes

An Encrypted column can only be of datatype varbinary and since the column we want to encrypt is of datatype varchar, we have to create a new column and populate it with encrypted values.

USE encrypt_test;
GO
ALTER TABLE Customer_data 
ADD Credit_card_number_encrypt varbinary(MAX) NULL
GO

Step 7 - Encrypting the newly created column

To encrypt the data we will use the encryptbykey command. Below is a sample code which can be used to encrypt the data. Please note that symmetric key needs to opened before we can encrypt data and be sure you manually close the key else it will remain open for the current session.

-- Populating encrypted data into new column
USE encrypt_test;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE Customer_data
SET Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
FROM dbo.Customer_data;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Below is an example of the encrypted data.

Sample Encrypted and Decrypted Credit Card Data

Step 8 - Remove old column

To finalize this process, let's remove the old column so that the table is left only with the encrypted data.

USE encrypt_test;
GO
ALTER TABLE Customer_data
DROP COLUMN Credit_card_number;
GO

Step 9 - Reading the SQL Server Encrypted Data

Let's take a look at an example of reading data using the decrypt by key option. As we indicated before, make sure you open and close symmetric key as shown earlier. Read more about the decrypt by key option.

USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID 
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
 
 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Here are the results from the query:

Read SQL Server Encrypted Data

Step 10 - Adding Records to the Table

Below is the sample code to insert values into the newly created encrypted column.

USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
-- Performs the update of the record
INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt)
VALUES (25665, 'mssqltips4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'4545-58478-1245') ) );    
GO

Below are the results from the table after the insert statement.

Add an encrpyted record in a SQL Server database

Step 11 - Accessing the Encrypted Data

All the read access users will see the encrypted values while they do a select on table. A user need to have permission to symmetric key and certificate to decrypt data, if they still try to decrypt then they will receive null for encrypted values. However they do not receive any errors. In the below sample code I am running select in context of a user 'test' which has only read access on DB.

Execute as user='test'
GO
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

Below you can see from the image below, the test user is not able to access the encrypted data.

Accessing SQL Server encrypted data with insufficient rights

Step 12 - Grant Permissions to the Encrypted Data

Permissions can be granted to a set of users to decrypt and read data using the commands below.

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test; 
GO
GRANT VIEW DEFINITION ON Certificate::Certificate1 TO test;
GO
Next Steps
  • In the above steps we have learned how to encrypt a data column. The same concept can be used to encrypt employee salaries, social security numbers, customer phone numbers, etc.  So check out your data to determine what should be encrypted.
  • Keep in mind implementation of column level encryption needs schema modification.
  • Reading from an encrypted column is resource intensive and lowers the overall performance of database, hence that should be considered as well.
  • The element of data that is encrypted remains in that state, even when recalled into memory.
  • Read more Encryption Tips.


Last Update: 11/22/2011


About the author
MSSQLTips author Nitansh Agarwal
Nitansh Agarwal is a lead with 4+ years of extensive experience in database administration where he handles large critical databases.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, October 21, 2014 - 4:29:10 PM - Urvashi Saxena Read The Tip

Nitansh,  do you have any experience with third-party column encryption (data masking) tools like IRI FieldShield and how do they compare with the built-in for SQL?


Tuesday, October 14, 2014 - 4:12:40 AM - prakash Read The Tip

hi all,

i have successfully encrypted and decrypted the data in the sql server

and i have one more requirement what about the searching by like query

is it supported when we use encrypt and decrypt if a column


Wednesday, October 01, 2014 - 1:23:30 PM - Og˙n NikÚ Read The Tip

I found a workaround but one that I'm afraid isn't scalable. I'm hoping that you can help me find the best way to accomplish this...

Instead of ENCRYPTING the Request.Form submission and comparing it to the VARBINARY string in the DB, I found that if I flipped them and compary the Request.Form submission the the DECRYPTED VARBINARY string in the DB, it works.

I think this is probably much, much more labor intensive for the DB and probably not scalable.

Can you tell me if there is a way to compare the ENCRYPTED Request.Form submission with the VARBINARY string in the DB instead of the other way around?

Many, many thanks in advance! All best, Ogún Niké 


Wednesday, October 01, 2014 - 12:49:34 PM - Larry Read The Tip

Hi Nitansh,

Nice article!  I have a question that I can't seem to find an answer to on the web...  When I'm inserting (or updating for that matter) a table row with multiple columns that need to be encrypted, is EncryptByKey efficient enough to just specify the parameter variable even when the variable's value is NULL, or would it be more efficient to use a CASE statement, and when the parameter variable value is NULL, simply store NULL, otherwise do the EncryptByKey function against the parameter variable value? 

I've got about 20 columns in my table that need to be encrypted, but there is also the chance that many of them will contain NULL (columns defined to allow NULLs, obviously).  Just wondering if I didn't use CASE that the overhead of doing the EncryptByKey on multiple columns could be more "expensive" resource-wise when it's a NULL value, or doesn't it really matter?

Thanks,

Larry


 


Wednesday, October 01, 2014 - 2:57:27 AM - Og˙n NikÚ Read The Tip

This article was hugely helpful. Thank you!!

I have implemented everything in ASP Classic with VBScript but I have not been able to use the "EncryptByKey" funciton in the WHERE portion of a SELECT statement.

For example, I have encrypted my username field but I cannot use EncryptByKey to match it with the user's entry. 

Here is my code. I would be very grateful if you could tell me where I am going wrong.

            AccountFinder.Open "OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; SELECT *, CONVERT(nvarchar(MAX), DecryptByKey(FName_encrypt)) AS 'FName_Denc', CONVERT(nvarchar(MAX), DecryptByKey(MName_encrypt)) AS 'MName_Denc', CONVERT(nvarchar(MAX), DecryptByKey(LName_encrypt)) AS 'LName_Denc' FROM Accounts WHERE Username_encrypt = EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varbinary,'" & Request.Form("Username") &"')) AND Password = '"& strEncodedPassword &"'; CLOSE SYMMETRIC KEY SymmetricKey1;", OBJdbConnection

When I encrypt the user's submission of the Username, it does not match or cannot match the encrypted Username in the DB. I am very confident that these values should match. 
 
If I am correct, then there is something wrong with how I am using the EncryptByKey function.
 
Please advise. Many, many thanks in advance! All best, Ogún Niké

Monday, September 22, 2014 - 11:45:24 AM - Ameena Read The Tip

I found the solution. Test user can see the encrypted data when used with open symmetric key and close symmetric key.

Thanks,

Ameena

 


Monday, September 22, 2014 - 11:28:40 AM - Ameena Read The Tip

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test1; 
GO
GRANT control ON Certificate::Certificate1 TO test1;
GO

 Even after granting control permission, tes1 user sees NULL in the decrypted column. Any suggestion would be most welcome.

Thanks,

Ameena


Friday, February 14, 2014 - 11:21:30 AM - mario Read The Tip

is there a way to encrypt an int column instead of varchar. I did try to do that but I received an error “Argument data type int is invalid” The thing is that in my application I need to keep that column as int because I need to apply the sum function on it after decrypting it and I don’t want data inside that column to be exposed.
Thank you in advance for your help or suggestion


Wednesday, February 12, 2014 - 11:26:12 AM - Ravi A Read The Tip

User 'test' will need  control permissiosn on Cettificate 1. Below code will work.

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test1;
GO
GRANT control ON Certificate::Certificate1 TO test1;
GO

 


Saturday, September 28, 2013 - 9:09:39 AM - Srinivas Read The Tip

Hi All,

 

I have 2 columns in one table, its datatype are numeric(18,0),datetime when i am going to encrypt these column i’m adding columns with same datatype it is giving error when the time of updation and I am tried with varbinary(256) also it is giving the same error.
The error is
” Argument data type numeric is invalid for argument 2 of EncryptByKey function “.
I am using SQL Server 2008r2 Enterprises edition . Please give me the solution for this how to encrypt the columns which i have mentioned. Is encryption will not work on these datatypes?

Thanks in advance……

Srinivasa Babu Doosa


Tuesday, July 23, 2013 - 2:13:39 PM - pratik Read The Tip

Hi,

 Great article..

I was just wondering about , if I am normal user , who can create a database..in sql server...can also perform this steps..and do the column level encryption.?

and if this is possible , can restrict my co-workers(we are working on same server, everybody log on with their windows authentication policy), who has the same access as me, to reach out certificate and keys..

Please let me know.

thanks.

 


Friday, July 05, 2013 - 2:49:12 PM - Alex Read The Tip

Thank you! Great job on this article. It helped me accoplish something I had not done before.


Wednesday, April 03, 2013 - 12:19:20 PM - Eesha Jayaweera Read The Tip

Thanks a lot for this article


Tuesday, February 12, 2013 - 4:48:00 AM - Pavan Read The Tip

Hi Nitansh

Am getting NULL values after restoring the DB in another server. Is there any work around to get rid of this issue


Saturday, December 01, 2012 - 4:44:36 AM - Surekha Read The Tip
USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID 
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
 
 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
 
i get NULL in 'Decrypted Credit Card Number' when i execute the above but i get  'Encrypted Credit Card Number',
actually i need the data encrypted or decrypted to compare the datas
my database has a encrypted column , when the uses sends tht data i need to compare user data with the encrypted column data 
but not able to get it

Friday, December 16, 2011 - 2:20:28 PM - Nitansh Read The Tip

Thanks for your comments Brian.


Friday, December 16, 2011 - 1:42:59 PM - Brian White Read The Tip

"do you have a method for backup and restore those keys ?"

You can back up the cert and its key together.  There is Restore Certificate too.  

BACKUP CERTIFICATE MyCert

TO FILE = 'c:\MSSQL\DATA\MyCert'

WITH PRIVATE KEY

(

    FILE = 'c:\MSSQL\DATA\SQLPrivateKeyFile',

    ENCRYPTION BY PASSWORD = 's5usW2Daxap4-zuC'

);


 


Friday, December 16, 2011 - 1:37:52 PM - Brian White Read The Tip

You should be quite concerned about the index impact.  Do not use this technique on a column that will be searched by.  Like do not expect to be able to search for card number 4111-1111-1111-1111 efficiently.  If you want to you need to either decrypt every row in the table, or encrypt 4111-1111-1111-1111.  Notice the VARBINARY(MAX) datatype that you are limited to for encrypted columns.  You can't index varbinary(max) even if you wanted to.  So all table scans all the time.  

If you don't search by card number, but just search for cards belonging to user id 999, then this will be fine.  But having more than one column you're selecting be encrypted can cause more and more slowness.  So if first you do card number, then you need to do other personally identifiable info, consider a different technique.  

We actually do our card encryption in the ui code layer.  The reason being that that way we can make it so that no single person is able to get card numbers from the database.  In the examples given you have some protection in that if some outside person stole your database they wouldn't be able to read the card numbers.  But a dozen+ devs and IT types would be able to decrypt all the cards and pocket them the day before they quit the company.  So from a PCI perspective, it is not a sufficient technique.  That also means that if we want to search for 4111-1111-1111-1111, the ui layer encrypts it, then passes in the encrypted string, and the underlying encrypted card number column is a regular varchar(255) column that is indexed and searchable.  With encryption you need to know the algorithm, the encryption key, and the salt.  You can break the pieces apart, so that server admins can set an encryption key or a salt in a place that devs can't access, like a file, or in the registry or something else outside of the source code tree.  Then neither the devs nor the server admins know enough to decrypt the cards, it would take them getting together to conspire to steal the data.  Which is orders of magnitude less likely than one bad apple.  


Wednesday, December 14, 2011 - 1:22:59 AM - Daniel Read The Tip
What happens if the server is dead and you want to restore from backup to another server ?
you dont have the certificate or encryption key...
 
do you have a method for backup and restore those keys ?
 
 

Wednesday, November 23, 2011 - 11:03:05 PM - Nitansh Read The Tip

 

Mike, there would be performance degradation if the table has high data volume and the query is fetching a lot of rows since SQL Server has to decrypt values in all the rows. Regarding, Indexing encrypted data - normal clustered or non-clustered index will not work. Queries which refer to encrypted data in where clause should be avoided and they should be run with reference to primary key value in the table. This will make the search faster.

 


Tuesday, November 22, 2011 - 3:53:32 PM - Mike Read The Tip

Nice Article and it looks worth implementing. I am concerned about db performance issues. Can I index encrypted data to improve performance?


Tuesday, November 22, 2011 - 2:37:44 PM - Sim Read The Tip

Great Article. Thanks for sharing.


Tuesday, November 22, 2011 - 11:45:28 AM - Nitansh Read The Tip

Thanks Deep.


Tuesday, November 22, 2011 - 8:36:54 AM - Deep Pandey Read The Tip

Great ! Thanks for sharing ..




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.