SQL Server 2016: Implement Column Level Encryption/Decryption
Introduction
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
Before getting started with the encryption of a table column, we will provision an SQL Server table which we will use throughout this demo.
Create the SQL Server Table
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 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 the 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 Column 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 an Open Symmetric key command.
OPEN SYMMETRIC KEY SQLSymmetricKey
DECRYPTION BY CERTIFICATE SelfSignedCertificate;
Once the 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.