Implement Column Level Encryption/ Decryption In SQL Server 2016

Priyaranjan K S

SQL Server 2016Implement Column Level

7 Comments

Join the conversation! Your thoughts help the community grow.

Mungara RISHITHA1mo ago
Passwordhash and encryption code two sql code rasalla
Swapnil Mehta7y ago
How can I decrypt the encrypted column in the entity framework?
swapnali more7y ago
I want to use the encrypted column data in a user defined function(slipt the encrypted csv string) and get the output ? how to implement this
Sourav Mukherjee8y ago
Instead of creating a new column (EncryptedPassword), can we not encrypt the existing column [UserPassword] in the table?
Sri Ram9y ago
I have a requirement to encrypt existing PROD data in SQL Server 2016. Can we encrypt the columns as it is without adding extra column or without changing the data type of the base columns? Is there any settings which we can apply on selected columns level?
Krishna Nadella9y ago
For the above scenario, i have created a view with decryption for the encrypted column. Ho w can i get the original data without opening SYMMETRIC KEY.
Gagan Sharma9y ago
Please suggest other ways of database encryption.

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.

How to Encrypt in SQL Server

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 EncryptByKey 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.