SQL Server Column Level Encryption Example using Symmetric Keys

In this article, I have posted step by step procedure to apply encryption on columns in SQL Server using symmetric keys.

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.

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
)
INSERT INTO dbo.Customer_data
SELECT 1,'Cust1','1111-2222-3333' UNION ALL
SELECT 2,'Cust2','1452-2563-1526' UNION ALL
SELECT 3,'Cust3','2147-4526-4587'

SELECT * FROM dbo.Customer_data

OUTPUT

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.

USE master
GO
SELECT  *
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 MASTER KEY ENCRYPTION BY PASSWORD = 'Pwd@123'

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.

CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Secure 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

CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1

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.

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.

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
CLOSE SYMMETRIC KEY SymmetricKey1;

OUTPUT

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.

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 bykey option. As we indicated before, make sure you open and closesymmetric key as shown earlier.

OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1;
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;
CLOSE SYMMETRIC KEY SymmetricKey1;

OUTPUT

Step 10 - Adding Records to the Table
Below is the sample code to insert values into the newly created encrypted column.

OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1;
INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt)
VALUES (4, 'Cust4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'2539-2222-1245')))

OUTPUT