Column-level encryption using Symmetric AES 256 in SQL

June 7, 2023 by Rajesh R

Introduction

The document aims to specify the implementation for the Column level encryption(CLE) using symmetric AES 256 in SQL.

High-Level Design

The high-level process of implementing CLE in the SQL database involves the following steps:

Step 1: Create SQL Server Service Master Key

This is the root of the SQL Server encryption hierarchy.
Usually created when the instance is created.
Confirm the existence and if not created already, manually create it.

Step 2 – Create SQL Server Database Master Key

The next level in the hierarchy is DMK.
The DMK does not directly encrypt the data.

Step 3 – Create a Self Signed SQL Server Certificate

This certificate is protected by the DMK.
The self-signed X.509 certificate is to safeguard encryption keys, which are used to encrypt data in the database

Step 4 – Create SQL Server Symmetric Key

The Symmetric Key will be used for encryption and decryption.
While creating the key, we can specify to use the ALGORITHM: AES_256

Step 5 – Add the required Schema changes

An Encrypted column can only be of datatype varbinary.
Create a new column with the type Varbinary

Step 6 – Creating the Encryption and Decryption functions

Create the required functions in SQL which will be used inside the various database objects( Stored Procedures, Functions, etc.)
Populate the new column after encrypting values from the original column.
Changes to be made in the dependent objects to use the new column (Insert/Update) with the newly created functions
After testing, remove the original column

Step 7 – Grant Permissions

A user need to have permission to symmetric key and certificate to decrypt data.
If they don’t have access, there will not be any errors
Permissions can be granted to a set of users to decrypt and read data.

Implementation

Generate Master keys: Generate a unique encryption key for each sensitive column. We will generate the key using GUID and MD5(message-digest algorithm) Hashing.

DECLARE @uniqueid UNIQUEIDENTIFIER
SET @uniqueid = NEWID()
DECLARE @guidval VARCHAR(MAX)
SET  @guidval = CONVERT(VARCHAR(MAX), @uniqueid)
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '''(SELECT HashBytes(''MD5'', @guidval))'''

For Viewing the Keys
SELECT name KeyName,symmetric_key_id KeyID,key_length,algorithm_desc 
FROM sys.symmetric_keys;

Generate a Certificate: Encryption certificate uses digital certificates to secure communications between parties.

CREATE CERTIFICATE SymmetricCertificate
WITH SUBJECT = 'Certificate for Symmetric Encryption’

For Viewing Certificates
SELECT name,certificate_id,pvt_key_encryption_type_desc EncryptType,issuer_name 
FROM sys.certificates

Generate Symmetric Key: Symmetric key generation is an important aspect of cryptography and encryption.

CREATE SYMMETRIC KEY SymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SymmetricCertificate;

Encrypt the data: For Encryption use a scalar valued function named ‘fn_symmetric_aes_encrypt’. This function contains two input parameters, one is @Input for pass the plain text for encryption and one is @key for passing particular symmetric key for encryption. The function returns @result as the encrypted cipher text.

CREATE Function [dbo].[fn_symmetric_aes_encrypt] 
   (@Input  nvarchar(max)
  ,@key nvarchar(32))
  Returns Varbinary(max)
AS
Begin
   DECLARE @result Varbinary(max);
   SET @result = EncryptByKey(Key_Guid(@key), @Input)
   RETURN @result;
End

Decrypt the data:Decrypting data means to convert encrypted or ciphered data back into its original plaintext form, making it readable and usable again. For Decryption use a scalar valued function named ‘fn_symmetric_aes_decrypt’. This function contains one input parameter @Input for pass the encrypted cipher textt for decryption and returns @result as the decrypted original plaintext.

CREATE FUNCTION  [dbo].[fn_symmetric_aes_decrypt]
   ( @Input varbinary (max))
 RETURNS nvarchar(max)
AS
BEGIN
     DECLARE @result nvarchar(max);
     SET @result = CAST(DecryptByKey(@Input) AS nvarchar(max));
     RETURN @result;
END;

Perform encryption and decryption: For invoking encryption in a particular table column we can call the database function :

[dbo].[fn_symmetric_aes_encrypt]([Column Name],[Key])

For invoking decryption call the database function:

[dbo].[fn_symmetric_aes_decrypt]([Column Name])

Grant permissions: Grant permissions to a Database User to use the symmetric key using the following SQL command:

GRANT CONTROL ON SYMMETRIC KEY::[SymmetricKeyName] TO [DatabaseUser];

Steps to convert existing table column VarChar to VarBinary

The column will not be converted directly to type VarBinary. The following steps will be done to ensure correct migration.

Step 1 – Add a new column with datatype VarBinary.

Step 2 – Populate the new column with the data from the existing column.

Step 3 – Execute the script to encrypt the data in the new column.

Step 4 – Rename the old column as backup.

Step 5 – Perform a sanity check by comparing the data in the old and new columns as well as from the application.

Step 6 – Final step is to rename the new column to the old column name.

Script for the one-time process to encrypt existing data for the table (An Example Table)

ALTER TABLE CSAUtilHist ADD AccountNumberEnc varbinary(max);
UPDATE CSAUtilHist SET AccountNumberEnc = CAST(AccountNumber AS 
                         varbinary(max));
OPEN SYMMETRIC KEY SymmetricKey
    DECRYPTION BY CERTIFICATE SymmetricCertificate;
       UPDATE CSAUtilHist SET AccountNumberEnc = 
               ENCRYPTBYKEY(KEY_GUID('SymmetricKey'), AccountNumber);
CLOSE SYMMETRIC KEY SymmetricKey;
EXEC sp_rename 'CSAUtilHist.AccountNumber', AccountNumberBefEnc', 'COLUMN';
EXEC sp_rename 'CSAUtilHist.AccountNumberEnc', 'AccountNumber', 'COLUMN';

Steps to change the password of the Database Master Key

Step 1 – Open the Database Master key with old password

Step 2 – Execute script to change the password for the Master key

Step 3 – Close the Database Master key

Eg: 
   OPEN MASTER KEY DECRYPTION BY PASSWORD = 'CurrentPassword';
     ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'NewPasswprd';
   CLOSE MASTER KEY