Intro

In the world of data and information liability is everywhere for you and your clients.

Data needs to be protected at all costs to ensure that is not misused by third parties. Bank and credit card details, names, addresses, social security numbers, health information, etc. are some types of information you want to protect no matter what.

In Europe, data is protected by GDPR (General Data Protection Regulation), while in the USA there is a combination of laws on both federal and state levels that covers privacy on different types of data(eg. HIPAA ,FCRA, FERPA , GLBA, ECPA, COPPA).

There are several different methodologies used to protect the data in SQL Server. From Dynamic Data Masking, creating different database schemas, using Transparent Data Encryption to creating separate tables and views, but in this article we will go through encrypting specific columns by using symmetrical keys and certificates in MS SQL Server.

Symmetric encryption uses secret key to encrypt and decrypt the data. Encrypted data is shown in random letters and numbers so that only the person who possesses the key can read the data. To the rest of the users, the encrypted columns will be shown as random letters and numbers or if they try to decrypt the data without appropriate permissions, they will see NULL values.

Algorithms and Data Types

There are a couple of algorithms we can choose from when we are creating symmetric key and they are listed in the table below.

Types of algorithms:

In this example we will use AES_256 which is most commonly used symmetric algorithm.

Data types listed in the table below support column encryption:

Steps Diagram

Steps

In MSSQL Server we created a table that holds information about credit cards. Inserted data is dummy data. AccountNumber, CreditCardNumber and PIN were generated by random numbers in the excel file and FirstName, LastName and CreditCardType were taken from the Internet.

In the screenshots we can see first and last name of credit card owners, their account and credit card number, PIN and what kind of credit card they own. Considering PIN is sensitive information, we will create encryption on that column.

1. Creation of a Master Key (the database / server master key)

Master key is a root key and it’s created during database setup. Check to see if master key was created by running the statement below.

SELECT * FROM sys.Symmetric_Keys;

We can see from the screenshot below that the master key was created.

If master key was not created, we would need to run the statement below to create it:

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = 'password' ;

2. Creation of a Self-signed Certificate (column encryption certificate)

For the creation of Symmetric key, we need a certificate that will be protected by master key created in the first step.

CREATE CERTIFICATE Column_Encryption_Certificate

WITH SUBJECT = 'Credit Card Encryption '

GO

From the screenshot below we can see the certificate was created:

SELECT * FROM sys.Certificates;

3. Creation of a Symmetric Encryption Key

The next step is to create the symmetric key for the columns you need. If there are more than one column that needs to be encrypted, then we need to create symmetric keys for all of them.

Syntax used to create the Symmetric key:

CREATE SYMMETRIC KEY SYMKEY _<Table_Name> _<Column_Name>

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE [<Certificate Name>]

For our example, we will encrypt PIN numbers.

CREATE SYMMETRIC KEY SYMKEY_CreditCard_PIN

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE Column_Encryption_Certificate

4. Encryption of Data

To encrypt data, we need to first open the Symmetric Key (in the query), then encrypt the column. Once the data is returned, we close the Symmetric Key unless we’re doing further encryption in our SQL script.

First, we will add a new column to the table with VARBINARY data type. Encrypted column can only be of VARBINARY data type because it’s good for storing large objects. Our encrypted column is stored as binary byte string rather than non-binary character strings. If we don’t manually add datatype while encrypting the column, database will manually convert it to VARBINARY (MAX) data type.

ALTER TABLE [dbo].[CreditCard]

ADD PIN_ENCRYPTED varbinary(MAX) NULL

Second step, encrypt the data in the new table with SQL statement:

OPEN SYMMETRIC KEY SYMKEY_CreditCard_PIN

DECRYPTION BY CERTIFICATE [Column_Encryption_Certificate];

UPDATE CreditCard

SET [PIN_ENCRYPTED] = EncryptByKey(Key_GUID('SYMKEY_CreditCard_PIN'), PIN);

CLOSE SYMMETRIC KEY SYMKEY_CreditCard_PIN;

5. Grant Access for the Certificate as well as the Symmetric Key to your Users

When we finish setting up encrypted columns, we need to decide which users or groups will have access to decrypt and encrypt columns.

A user with read permission cannot decrypt data using the symmetric key. If a user that doesn’t have required permissions tries to open a symmetric key and then decrypt the data, the output message will say that the symmetric key does not exist, or the user does not have permission to use it (picture below)

And if a user tries to decrypt the data, he will get the NULL values in the decrypted column.

We need to grant access to the symmetric key we created and grant VIEW and CONTROL definition on column encryption certificate.

If there are multiple symmetric keys (more than one column needs to be encrypted) then we need to grant control for all of them.

GRANT VIEW DEFINITION ON SYMMETRIC KEY:: SYMKEY_CreditCard_PIN TO [user1];

GO

GRANT VIEW DEFINITION ON Certificate::[Column_Encryption_Certificate] TO [user1];

GO

GRANT CONTROL ON Certificate::[Column_Encryption_Certificate] TO [user1];

GO

From the screenshot below we can see user1 now has access to open the symmetric key and decrypt the table.

6. Decryption of Data

Users that have access to decrypt the data can run special statement that will show them decrypted PIN numbers. PIN_ENCRYPTED is VARBINARY type and it’s shown to us in an unreadable format.

If we try to decrypt the column using DECRYPTBYKEY without converting it to VARCHAR, our column will me unreadable.

To decrypt the column, we need to convert it to a readable format VARCHAR:

OPEN SYMMETRIC KEY SYMKEY_CreditCard_PIN

DECRYPTION BY CERTIFICATE [Column_Encryption_Certificate];

SELECT

t.*

,convert(varchar(100), DECRYPTBYKEY(PIN_ENCRYPTED)) AS 'PIN_DECRYPTED'

FROM CreditCard t;

CLOSE SYMMETRIC KEY SYMKEY_CreditCard_PIN;

7. Insert New Records to Table with Encrypted Columns

If we want to insert new records to a table that has an encrypted column, we will use EncryptByKey logic in the insert statement.

OPEN SYMMETRIC KEY SYMKEY_CreditCard_PIN

DECRYPTION BY CERTIFICATE Column_Encryption_Certificate;

INSERT INTO dbo.CreditCard (FirstName,LastName,AccountNumber,CreditCardNumber,PIN,CreditCardType,PIN_ENCRYPTED)

VALUES ('Lorna','Stage','07589650952','836164-5861013-524398','4579','Visa', EncryptByKey( Key_GUID('SYMKEY_CreditCard_PIN'), CONVERT(varchar,'4579') ) );

CLOSE SYMMETRIC KEY SYMKEY_CreditCard_PIN;

From the screenshot below we can see new record with encrypted column.

Pros and Cons

Pros:

• Encryption is possible when data is active and at rest.
• We can decide which columns we want encrypted (instead of encrypting the whole table) and that will save us CPU requirements and provide us with flexibility.
• You can create different keys for different columns and decide which user group can access which column. **Cons: **
• You need to decide who will have access to encrypted columns and keep that list updated.
• In our case, while using reporting tools like Power Bi for dynamic reports, we needed to use stored procedures that held decryption logic for encrypted columns.
• Using key rotation on a regular basis to avoid misuses and data leakage.

Summary

In this article we explained how to create a master key, certificate, symmetrical key, how to encrypt and decrypt the column and how to grant access to other users so they can view those columns. Remember that you can use one key to encrypt multiple columns. Keep your data safe and your clients happy.