The simple guide to column-level encryption

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. 

Now 

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 

CREATE MASTER KEY ENCRYPTION BY   PASSWORD = 'jaksjAKSJlkaj$%^';

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 tbls.name TBL, syscol.name 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
        WHERE typ.name='varbinary'
        AND tbls.is_ms_shipped=0

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

USE mydb;
GO
CREATE CERTIFICATE my_cert WITH SUBJECT = 'mycert';
GO

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,
thumbprint
FROM sys.certificates

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

CREATE SYMMETRIC KEY my_symetric_key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE my_cert;

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
 ) 

INSERT INTO testDataEnc
select 1 , 'Prince', '1283719287319823'
UNION ALL
select 2 , 'Santana', '1283719287319823'
UNION ALL
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  
   DECRYPTION BY CERTIFICATE my_cert;  

   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
   DECRYPTION BY CERTIFICATE my_cert_test;  

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