Implement Column Level Encryption/ Decryption In SQL Server 2016

SQL Server table stores the data, which is used by different target audiences. There can be the instances, where we have to protect the sensitive data from unintended users. Usually, we create views to project the subset of the table data to the users and abstracts away the sensitive information. In this article, we will see another option to protect the table column, using column level encryption. We will be able to use a master key to encrypt and decrypt the column data, which we will explore through a demo.

Prerequisites

Let’s create a table named UserDetails, which will store the user login information of an online system. We will also update the table with some dummy login data.

CREATE TABLE [UserDetails](
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) ,
[LoginID] [varchar](20) NOT NULL,
[UserPassword] [varchar] (80)
)
GO
INSERT INTO [UserDetails]
VALUES('Rajesh', 'Pillai', 'rapi', 'password-1')
INSERT INTO [UserDetails]
VALUES('John', 'Bhaskar', 'johnny', 'john-123')
INSERT INTO [UserDetails]
VALUES('Jack', 'Daniel', 'pwdJD', 'pwd-17')
INSERT INTO [UserDetails]
VALUES('Anuraj', 'KS', 'AKS', 'aksPWD')
INSERT INTO [UserDetails]
VALUES('Jinesh', 'Raj', 'Jinu', 'passJIN')
INSERT INTO [UserDetails]
VALUES('Mathew', 'John', 'mat', 'Mathewz')

 

Thus, we have created the table and are now in a position to explore the encryption process.

Overall process

The overall process is to encrypt the column in SQL Server table and it can be summarized, as shown below.

Set up the Master Key

The encryption process of SQL Server table column involves a Master Key, Certificate and a Symmetric key. As the first step, we will create the database master key, which will be used to encrypt the Symmetric key. This is done, using Create Master Key command. " ENCRYPTION BY PASSWORD " argument, which defines the password (Password-1 in our case), which will be used to encrypt the key.

CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Password-1'
GO

 

Create the Symmetric key

As the next step, we have to create a Symmetric key but in order to secure a Symmetric key, we should have a digitally signed certificate. We can create the certificate, using ‘Create Certificate’ command, which will be protected by the Database Master Key. Once the certificate is created, we will create the Symmetric key using the command ‘Create Symmetric Key’ command. We will also make use of the AES_128 encryption algorithm and the digitally signed certificate, which we had created to secure Symmetric key.

CREATE CERTIFICATE SelfSignedCertificate
WITH SUBJECT = 'Password Encryption';
GO
CREATE SYMMETRIC KEY SQLSymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE SelfSignedCertificate;
GO

 

Add column to hold the encrypted data

Now, we will make a change to the table schema and add a new column to the UserDetails table, so as to store the encrypted password.

USE DB;
GO
ALTER TABLE UserDetails
ADD EncryptedPassword varbinary(MAX )NULL
GO

 

Encrypt table data

In order to encrypt the table data, we will open Symmetric key and trigger the update command on the table. We will open Symmetric key , using Open Symmetric key command.

OPEN SYMMETRIC KEY SQLSymmetricKey
DECRYPTION BY CERTIFICATE SelfSignedCertificate;

 

Once Symmetric key is opened, we will use the EncryptByKe function and call the Update command on the table.

UPDATE UserDetails
SET [EncryptedPassword] = EncryptByKey(Key_GUID('SQLSymmetricKey'), UserPassword);
GO
select * from UserDetails

Thus, we can see that EncryptedPassword column has been populated with the encrypted password data.

We can try to reverse engineer the encryption and decrypt the password, using the ‘DecryptByKey’ function. The decrypted data has come up in the ‘DecryptedPassword’ column.

SELECT FirstName, LastName,LoginID,UserPassword,EncryptedPassword,
CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'DecryptedPassword'
FROM UserDetails;

Once we have completed the encryption and decryption procedures, we have to close Symmetric key, using the Close Symmetric Key command.

CLOSE SYMMETRIC KEY SQLSymmetricKey;
GO

Once we have closed Symmetric key, if we try to run the decryption query; we will get NULL values in the column.

Hence, we have to ensure that we always open Symmetric key before starting the Encryption/Decryption query.

OPEN SYMMETRIC KEY SQLSymmetricKey
DECRYPTION BY CERTIFICATE SelfSignedCertificate;
SELECT FirstName, LastName,LoginID,UserPassword,EncryptedPassword,
CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'DecryptedPassword'
FROM UserDetails;

To achieve the real purpose of the encryption process, we can drop the existing plain text password column, using the drop command and retain only the encrypted column.

Summary

Thus, we saw how to implement column level encryption and decryption in SQL Server 2016.