Column Level Encryption Decryption using Symmetric Keys

SQL Server allows several options to protect sensitive data, and one of them is specifying a column level encryption.

Using this option you can encrypt a column data also decrypt the data. SQL Server encrypts data with a hierarchical encryption and key management infrastructure, you can read about Encryption Hierarchy

To demonstrate the implementation of column level encryption, First we will create a table which will contain a column named AccountNumber having a account number of customer. Then we will encrypt the data of column AccountNumber using a SQL Server Symmetric keys.

Lets use the steps included in Encryption Hierarchy and implement the column level encryption.

First we create a sample table named CustomerAccountDetails, which contains the sensitive data of customer account number.

CREATE TABLE dbo.CustomerAccountDetails ( 
CustId INT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(100),
LastName VARCHAR(100), 
AccountNumber VARCHAR(40) 
) 
GO

INSERT INTO dbo.CustomerAccountDetails
(FirstName, LastName, AccountNumber)
VALUES
('Atul', 'Kumar', 'HKT783459871'),
('Mahesh', 'Agarwal', 'BKY874534683'),
('Suraj', 'Negi', 'EBY778533682'),
('Lokesh', 'Mathur', 'CBY972534186'),
('Kumar', 'Ajay', 'NKH874534388'),
('Rahul', 'Rawat', 'ETY844524694'),
('Sanjay', 'Joshi', 'EBY274034688')

Implementing a SQL Server column level Encryption using a SYMMETRIC Keys

Lets start with the root of the SQL Server encryption hierarchy that is Service Master Key, which is created during the instance creation. The Service master keys are protected by using the AES-256 algorithm.

It is created in master database and you can check, if the service master key has been created yet using following query.

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

Next we will setup a database master key.

Creating a DATABASE MASTER KEY

The database master key is a symmetric key which is used to protect the private keys of certificates and asymmetric keys that are present in the database. The database master keys are protected by using the AES-256 algorithm.

USE Prod_Db ;
GO
CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'password@123' 
GO

The  encrypt by password argument which defines the password that is used to encrypt the master key in the database.

Lets quickly check whether a Database Master key is created or not using the following query.

USE Prod_Db ;
GO
SELECT *
FROM sys.symmetric_keys

You can see, a Database master key is created. Now we will create a self signed SQL Server Certificate.

Creating a self signed SQL Server CERTIFICATE

A self signed SQL Server Certificate is a digitally signed security object  contains a public or private (Optionally) key for SQL Server. It is protected by the database master key.

You can create a self certificate either with or without specifying an encryption by password.

When you create a certificate without encryption by password then in this case certificate is to be protected by the database master key.

When a certificate is created with argument ENCRYPTION BY PASSWORD, then in this case a certificate is protected by the password.

Lets create certificate without ENCRYPTION BY PASSWORD as we want certificate to be protected by Database master key.

USE Prod_Db;
GO
CREATE CERTIFICATE SelfSign_Certificate
WITH SUBJECT = 'Data Security';
GO

Note that, subject should not be more than 64 characters long and this limit is enforced for SQL Server on Linux, and the subject can be up to 128 characters long for SQL Server on Windows.

Lets check whether the certificate is created or not using following query.

SELECT name, certificate_id, pvt_key_encryption_type_desc, 
Subject, expiry_date, start_date 
FROM SYS.certificates

As you can see above result, a certificate is created and you can see it is encrypted by the Database master key.

Also you can see that the certificate expiry date is also there. If an expiration date is not specified by the time certificated is created then a default expiration date of one year from the creation date will be applied.

If you want to provide the an expiry date when you create a certificate then you can modify above query as follows.

USE Prod_Db;
GO
CREATE CERTIFICATE SelfSign_Certificate
WITH SUBJECT = 'Data Security', EXPIRY_DATE =  'specify expiry_date' 
GO

Now we will setup a symmetric key.

Creating a SYMMETRIC KEY

A symmetric key is used to encryption and decryption data in database. It is recommended to use symmetric key encryption for best performance when you encrypting a large amount of data.

Lets create a SYMMERTRIC KEY using following query.

USE Prod_Db ;
GO
CREATE SYMMETRIC KEY SymmetricKey 
WITH ALGORITHM = AES_128 
ENCRYPTION BY CERTIFICATE SelfSign_Certificate;
GO

The WITH ALGORITHM clause specifies the algorithm with which you want to use the key. You can choose between AES_128, AES_192 and AES_256. Here we have chosen AES_128.

The ENCRYPTION BY CERTIFICATE, a symmetric key has to be protected by either a password, certificate , an asymmetric key or another symmetric key. Here we have used a certificate that we had just created.

Now we will add a new column EncryptedAccountNumber in table CustomerAccountDetails  that will store a encrypted data of column AccountNumber.

Lets add a column named EncryptedAccountNumber in table using Alter Table command.

USE Prod_Db;
GO
ALTER TABLE dbo.CustomerAccountDetails
ADD EncryptedAccountNumber VARBINARY(MAX ) NULL
GO

Lets see the records in table.

SELECT * FROM dbo.CustomerAccountDetails

As you can see, there are seven records in table, now we will encrypt the column AccountNumber values and update the encrypted values into column EncryptedAccountNumber.

Encrypting a data using symmetric key

To encrypt the data, first we will open Symmetric key using OPEN SYMMETRIC KEY command to use the symmetric key then uses the EncryptByKey command.

After using the symmetric we close the symmetric key using CLOSE SYMMETRIC KEY command as following.

USE Prod_Db; 
GO 
OPEN SYMMETRIC KEY SymmetricKey
DECRYPTION BY CERTIFICATE SelfSign_Certificate;
GO
UPDATE dbo.CustomerAccountDetails
SET EncryptedAccountNumber = EncryptByKey (Key_GUID('SymmetricKey'),AccountNumber)
FROM dbo.CustomerAccountDetails ;
GO

CLOSE SYMMETRIC KEY SymmetricKey;
GO

As you can see, seven rows affected. Lets check the table.

SELECT * FROM dbo.CustomerAccountDetails;

As you can see the data of column AccountNumber is encrypted and updated in new column EncryptedAccountNumber.

Decrypting a data

To reading the encrypted data you need to decrypt it using a DecryptByKey command.

USE Prod_Db;
GO
OPEN SYMMETRIC KEY SymmetricKey
DECRYPTION BY CERTIFICATE SelfSign_Certificate;
GO
SELECT CustId, FirstName, LastName, AccountNumber, EncryptedAccountNumber,
CONVERT(VARCHAR, DecryptByKey(EncryptedAccountNumber)) AS 'DecryptedAccountNumber'
FROM dbo.CustomerAccountDetails

CLOSE SYMMETRIC KEY SymmetricKey;
GO

As you can see, the encrypt data of column EncryptedAccountNumber is decrypted that can be seen in column DecryptedAccountNumber.

Inserting a records in table

Lets insert a record in table column EncryptedAccountNumber.

USE Prod_Db;
GO
OPEN SYMMETRIC KEY SymmetricKey
DECRYPTION BY CERTIFICATE SelfSign_Certificate;
GO
INSERT INTO dbo.CustomerAccountDetails
(FirstName, LastName, AccountNumber, EncryptedAccountNumber)
VALUES (
'Pradeep', 
'Raturi',
'PRAD28403463', 
EncryptByKey( Key_GUID('SymmetricKey'), 
CONVERT(VARCHAR,'PRAD28403463') ) );

CLOSE SYMMETRIC KEY SymmetricKey;
GO

 

Lets check the record in table, you can see, record is inserted in table also a account number is encrypted.

SELECT * FROM dbo.CustomerAccountDetails;

Note that you can delete the column AccountNumber from table, as you have EncryptedAccountNumber column that you can read using DecryptByKey command.

Also Read

Data discovery and classification

Implementing a ROW_LEVEL security in SQL Server

Encrypt SQL Stored Procedure

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More