Post
Ask Question

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.
  1. CREATE TABLE [UserDetails](  
  2. [FirstName] [varchar](50) NOT NULL,  
  3. [LastName] [varchar](50) ,  
  4. [LoginID] [varchar](20) NOT NULL,  
  5. [UserPassword] [varchar] (80)  
  6. )  
  7. GO  
  8. INSERT INTO [UserDetails]  
  9. VALUES('Rajesh', 'Pillai', 'rapi', 'password-1')  
  10. INSERT INTO [UserDetails]  
  11. VALUES('John', 'Bhaskar', 'johnny', 'john-123')  
  12. INSERT INTO [UserDetails]  
  13. VALUES('Jack', 'Daniel', 'pwdJD', 'pwd-17')  
  14. INSERT INTO [UserDetails]  
  15. VALUES('Anuraj', 'KS', 'AKS', 'aksPWD')  
  16. INSERT INTO [UserDetails]  
  17. VALUES('Jinesh', 'Raj', 'Jinu', 'passJIN')  
  18. INSERT INTO [UserDetails]  
  19. 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
 
How to Encrypt in SQL Server 

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.
  1. CREATE MASTER KEY ENCRYPTION BY  
  2. PASSWORD = 'Password-1'  
  3. 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.
  1. CREATE CERTIFICATE SelfSignedCertificate  
  2. WITH SUBJECT = 'Password Encryption';  
  3. GO  
  4. CREATE SYMMETRIC KEY SQLSymmetricKey  
  5. WITH ALGORITHM = AES_128  
  6. ENCRYPTION BY CERTIFICATE SelfSignedCertificate;  
  7. 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.
  1. USE DB;  
  2. GO  
  3. ALTER TABLE UserDetails  
  4. ADD EncryptedPassword varbinary(MAX )NULL  
  5. 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.
  1. OPEN SYMMETRIC KEY SQLSymmetricKey  
  2. DECRYPTION BY CERTIFICATE SelfSignedCertificate;  
Once Symmetric key is opened, we will use the EncryptByKey function and call the Update command on the table.
  1. UPDATE UserDetails  
  2. SET [EncryptedPassword] = EncryptByKey(Key_GUID('SQLSymmetricKey'), UserPassword);  
  3. GO  
  4. select * from UserDetails  
Thus, we can see that EncryptedPassword column has been populated with the encrypted password data.
 
EncryptByKey in SQL Server 
 
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.
  1. SELECT FirstName, LastName,LoginID,UserPassword,EncryptedPassword,  
  2. CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'DecryptedPassword'  
  3. FROM UserDetails;  
DecryptByKey in SQL Server 
 
Once we have completed the encryption and decryption procedures, we have to close Symmetric key, using the Close Symmetric Key command.
  1. CLOSE SYMMETRIC KEY SQLSymmetricKey;  
  2. 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.
  1. OPEN SYMMETRIC KEY SQLSymmetricKey  
  2. DECRYPTION BY CERTIFICATE SelfSignedCertificate;  
  3. SELECT FirstName, LastName,LoginID,UserPassword,EncryptedPassword,  
  4. CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'DecryptedPassword'  
  5. FROM UserDetails;  
Symmetric key in SQL Server 
 
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.


Similar Articles