Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

The simple guide to column-level encryption

05 July,2022 by Tom Collins

Using a symetric key , how can I encrypt a column in a SQL Server database table? And what are some of the common pitfalls related to setting up column level encryption


In summary these are the steps

=>Step 1 : Create a database master key
=>Step 2 :Create a SQL Server self-signed certificate 
=>Step 3 : Create and Configure a symmetric key for encryption
=>Step 4: Encrypt the column data
=>Step 5: Query and verify the encryption

There is a previous post with an outline of the sql code How to set up SQL Server Column Level Encryption . The following post is adding some extra notes and comments 


Although the details below outline the basic process for enabling & executing column-level encryption some considerations are required  beyond just enabling\configuring column-level encryption

1.Client coding changes are required. The DecryptByKey and EncryptByKey functions are client code related . 

2. Will there be a requirement to migrate the specific encrypted column data , for example an ETL process , nightly table copy or even a one-off table copy to another database. To successfully read the data once it's copied will require the the certificate used for the encryption and the private key to be backed up and imported into  to the target database. The import process should include the password used to backup the certificate

3. If you need to backup & restore the database hosting the encrypted column onto another server , & require to decrypt the data - you'll need to move the service master key details across to the target server - other wise you'll get this type of error .

Msg 15581, Level 16, State 7, Line 5
Please create a master key in the database or open the master key in the session before performing this operation.

If you plan to manage encrypted data on SQL Server, ensure you store\manage the  Database Master Key (DBMK) passwords, and you backup your Service Master Key (SMK).

When you enable  & utilise SQL Server column level encryption there is a chain of 5 keys utilise .  Service Master Key, Database Master Key, Asymmetric key, Certificate and Symmetric key. Whenever you implement column level encryption, a complete hierarchy of keys needs to be created and able to decrypt the other keys along the hierarchy. 



You may need to apply all these steps are some , depending on the current state of the database.

Step 1: Create a database master key

Check if a master key is exists in the database.If it doesn't exist a database master key will need to be created 


Msg 15578, Level 16, State 1, Line 13
There is already a master key in the database. Please drop it before performing this statement.

If you attempt to DROP the master key and get the message :

Msg 15580, Level 16, State 1, Line 7
Cannot drop master key because certificate 'mycertname' is encrypted by it.


This query on the database will report on the database master key 


SELECT name KeyName, 
    symmetric_key_id keyid, 
    key_length keylength, 
    algorithm_desc keyalgorithm
FROM sys.symmetric_keys;


Step 2 : Create a SQL Server self-signed certificate 

If there is a column encrypted it will not be possible to drop the certificate . You may need to first audit  if there are any encrypted columns , use this query :


SELECT TBL, COL FROM sys.columns syscol
        INNER JOIN sys.types typ ON syscol.system_type_id=typ.system_type_id
        INNER JOIN sys.tables tbls ON tbls.object_id=syscol.object_id
        AND tbls.is_ms_shipped=0


if a new self-signed certificate is required use this CREATE CERTIFICATE statement:


USE mydb;

Use this query on the sys.certificates to identify the certificate details 

SELECT name AS certificate_name,
pvt_key_encryption_type_desc AS pvt_key_encryption,
FROM sys.certificates

Step 3 : Create and configure a symetric key in preparation for column level encryption



Use the catalog view sys.symetric_keys 


SELECT name symKeyName, 
    symmetric_key_id symKeyID, 
    key_length symKeyLength, 
    algorithm_desc symKeyAlgorithm
FROM sys.symmetric_keys;


Step 4: Encrypt the data 

A SQL Server encrypted data column needs to be varbinary.

In the example table build , I've added a few rows and I've added a column with a varbinary data type 

CREATE TABLE [dbo].[testDataEnc]( [ID] [int] NULL, 
[Surname] [varchar](20) NOT NULL, 
[SocialSecNumber] [varchar](20) NOT NULL

select 1 , 'Prince', '1283719287319823'
select 2 , 'Santana', '1283719287319823'
select 2 , 'Hendrix', '1283719287319823'

ALTER  TABLE [dbo].[testDataEnc]
ADD [SocialSecNumber_enc] [varbinary](max) NULL

Next step is to  update the the varbinary column with encrypted data . I'll use the unencrypted data from the column SocialSecNumber  column to update the SocialSecNumber_Enc column.

To initiate the encryption process - requires the symetric key to be opened


OPEN SYMMETRIC KEY my_symetric_key  

   UPDATE   testDataEnc
SET SocialSecNumber_enc = EncryptByKey (Key_GUID('SymKey_test'),SocialSecNumber )

Once I've updated the columns and I issue a SELECT statement without using the symetric key - these are the results I get . To view the data decrypted - go to to step 5 for a sample code

ID Surname SocialSecNumber SocialSecNumber_enc
1 Prince 1283719287319823 0x00E13807D1F1194A94FE93FA325BD27302000000CD2D52C7E9883B9CC7F78F161B96C62D587073A2F370A3ADC3EC3D0EFA041DF96A5955765979A037DA5F79ABABA9F5C9
2 Santana 1283719287319823 0x00E13807D1F1194A94FE93FA325BD27302000000571FA0B5F05BDF342B7396666A90D025B4C59FD873DDF098BFB80983411AD20184BEBC8FE69104B7880EBDFE24549F52
2 Hendrix 1283719287319823 0x00E13807D1F1194A94FE93FA325BD273020000001B93DA0F0467F9B3BEFDC1AC06E20EADCA9011FE7AE4CEE798CB0972617AB7CC730C6D51241EEA412160D3FBCE7BDFF4


Step 5: Query the encrypted data 


OPEN SYMMETRIC KEY my_symetric_key

select ID,surname,socialSecNumber,
 CONVERT(varchar, DecryptByKey(SocialSecNumber_enc))   
    AS 'Decrypted Social Security  Number'  
FROM testDataEnc


Read more on database encryption

Impact on TDE encryption in progress on SQL Server restart

A guide to restore a TDE database backup to another SQL Server Instance


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on The simple guide to column-level encryption | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer