Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories


SQL Server Column Level Encryption Example using Symmetric Keys

By:   |   Read Comments (32)   |   Related Tips: More > Encryption


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.


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;
-- 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')
INSERT INTO dbo.Customer_data 
VALUES (74113,'MSSQLTips3','4574-8475-2147')
INSERT INTO dbo.Customer_data 
VALUES (74114,'MSSQLTips4','2147-8475-4574')
INSERT INTO dbo.Customer_data 
VALUES (74115,'MSSQLTips5','2157-1544-8875')
-- Verify data
FROM dbo.Customer_data
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;
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';

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;

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;
WITH SUBJECT = 'Protect Data';

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;

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;
ALTER TABLE Customer_data 
ADD Credit_card_number_encrypt varbinary(MAX) NULL

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;
-- Opens the symmetric key for use
UPDATE Customer_data
SET Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
FROM dbo.Customer_data;
-- Closes the symmetric key

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;
ALTER TABLE Customer_data
DROP COLUMN Credit_card_number;

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;
-- 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

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;
-- 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') ) );    

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'
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 Certificate::Certificate1 TO test;
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:

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

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Sunday, May 22, 2016 - 1:35:16 AM - Ali Back To Top

Nitansh Agarwal,thank you for your help. it is realy very amazing code with good explanation.


Monday, May 02, 2016 - 12:01:26 PM - NITANSH Back To Top

Thank you Steve!

Thursday, April 21, 2016 - 12:17:03 PM - Steve Back To Top

 I've searched videos and articles on column encryption. NOTHING I found was as comprehensive (yet still concise), and straightforward as this article. It's amazing how much information there isn't out there on this topic. Thank you!


Thursday, March 24, 2016 - 9:16:01 AM - Naresh Back To Top

 It is really helpfull**** Exellent

Thank you


Friday, June 05, 2015 - 4:53:47 PM - R. L. Dawson Back To Top

There is one other thing I didn't see mentioned in the article that is of some concern to security conscious and PCI compliant enterprises. Once set there is no mechanism for changing the password to the database master key. You can change the certificates assigned to symmetric keys. You can reencrypt the data. However once set you are stuck with your database master key password. Use a strong one. I set a standard of 100 + random number of additional characters for my systems.

Also, I didn't see it mentioned adding the dmk to the service master key so that you do not have to explicitly open it when you need to create new symmetric keys. We are a hosting system for merchants lending money. Each merchant has their own symmetric key and we add them regularly.


I hope this helps.

Richard L. Dawson

Thursday, February 19, 2015 - 12:14:53 PM - Jose Back To Top

Not sure if I am missing something but above permissions are not enough for a regular user to be able to see the encrypted data. This one is missing:



With that.... then user "test" is able to see the encrypted data. And this is because we are using a certificate. Without a certificate I think GRANT CONTROL is not needed.

Monday, January 05, 2015 - 6:28:27 PM - Lauren Glenn Back To Top

Ogún Niké:

Chances are that it's very intensive because it is basically doing a table scan.  For my purposes, I used a separate criteria and then wrote them into a temporary table doing the comparison after the fact (or with a USING).  To be honest, I wouldn't encrypt the usernames and would solely encrypt the passwords (or just use hashes which mask the data and make it easy to compare.  With that, you can search for an indexable field (username) and then take that result data to use the decrypt process on it (which would be a bit faster).  

As secure as you want it to be, you don't want it to be so secure that it becomes something that is tedious or counter-productive.

This should be used on resulting data that doesn't require you to decrypt and then compare.  The WHERE clause gets executed on the results from the FROM and the fields returned are done after that.  So, use the decrypt part there and leave the WHERE for things that you can index.  You can return the decrypted data in the fields and then take that result to return to your program.

Tuesday, December 02, 2014 - 1:25:30 AM - Max Back To Top

select Password, UserName from aspnet_Membership, aspnet_Users where UserName='Max';


I have used this and it gives me a responce of d6mSTRfbTz0q0V7jTgyvBSm2NoU= this code contains the password : password.


I have tried to use pwdcompare and pwdencrypt, none of them work, not even the hashbytes helps at all, all I have done is use ASP.NET membership tool, the aspnet_regsql.exe and made the databse in MS SQL 2012 and linked it to the local website which has a connection string in the web.config file. all i wanted to do was for eg, when a user enters a password say password it gets compared.

if the UserName is Max and password is password. then the user enters Max in the Username textbox and the password in the password textbox, but when it comes to compare it will have : select Password, UserName from aspnet_Membership, aspnet_Users where UserName='Max'; then for the password it will be compared wiith the database but before that happens it will have to be hashed and salted? so it matches using the pwdcompare('password', Password_column) in the database? I thought of making it clear text I know that will work... but where is security...

Tuesday, October 21, 2014 - 4:29:10 PM - Urvashi Saxena Back To Top

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 Back To Top

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Ú Back To Top

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 Back To Top

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?




Wednesday, October 01, 2014 - 2:57:27 AM - Og˙n NikÚ Back To Top

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 Back To Top

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




Monday, September 22, 2014 - 11:28:40 AM - Ameena Back To Top

GRANT control ON Certificate::Certificate1 TO test1;

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



Friday, February 14, 2014 - 11:21:30 AM - mario Back To Top

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 Back To Top

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

GRANT control ON Certificate::Certificate1 TO test1;


Saturday, September 28, 2013 - 9:09:39 AM - Srinivas Back To Top

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 Back To Top


 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.



Friday, July 05, 2013 - 2:49:12 PM - Alex Back To Top

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 Back To Top

Thanks a lot for this article

Tuesday, February 12, 2013 - 4:48:00 AM - Pavan Back To Top

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 Back To Top
USE encrypt_test;
-- 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
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 Back To Top

Thanks for your comments Brian.

Friday, December 16, 2011 - 1:42:59 PM - Brian White Back To Top

"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.  





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

    ENCRYPTION BY PASSWORD = 's5usW2Daxap4-zuC'



Friday, December 16, 2011 - 1:37:52 PM - Brian White Back To Top

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 Back To Top
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 Back To Top


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 Back To Top

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 Back To Top

Great Article. Thanks for sharing.

Tuesday, November 22, 2011 - 11:45:28 AM - Nitansh Back To Top

Thanks Deep.

Tuesday, November 22, 2011 - 8:36:54 AM - Deep Pandey Back To Top

Great ! Thanks for sharing ..

Learn more about SQL Server tools