SQL Server: Configure Always Encrypted for databases using Always Encrypted wizard.

Article
1/17/2024

Introduction

Always Encrypted is a new feature introduced in SQL Server 2016 for encrypting sensitive individual columns data such as credit card numbers and personal details at rest as well as in-transit completely.

There are several ways to enable Always Encrypted feature:

By using always encrypted wizard in SSMS.
Configure Always Encrypted using SSMS.
Configure Always Encrypted using PowerShell.
Configure Always Encrypted using T-SQL.

Now, in this article we will see how to secure sensitive columns data in a SQL database with always encrypted by using the Always Encrypted Wizard in SQL Server Management Studio (SSMS).

Prerequisites

Always Encrypted-enabled driver that interfaces with SQL Server 2016 need to install on the client machine. To access data stored in an encrypted column in plain text.
Ensure SQL Server 2016 with SP1 or later version installed.
Ensure SSMS 13.0.700.242 or later version installed.
Ensure .NET framework 4.6 or later version installed.

Types of Encryption keys

This encryption technique mainly utilizes two types of keys, column encryption keys and column master keys.

Column encryption key (CEK):

This key is used to encrypt/decrypt the Always Encrypted columns. The database engine stores the column encryption key on the SQL Server instance where Always Encrypted is implemented.

Column Master Key [CMK]:

This key is used to protect\encrypt the one or more Column Encryption keys. The database engine stores only metadata of column master key that points to the key’s location which are stored in external trusted key stores.

The actual master key is saved at trusted external key store, such as the Windows certificate store. [Where SSL certificate will store]. By placing this key on the application machine SQL Server doesn’t have access to the column master key directly. Therefore SQL Server by itself will not be able decrypt the Always Encrypted data.

Always Encrypted Workflow

Below diagram illustrates the Always Encrypted workflow.

"The application communicates with SQL Server via the Always Encrypted-enabled driver installed on the client/application machine.

Once Always Encrypted-enabled driver receives a parameterized query, the driver transparently collaborates with the Database Engine to identify which column needs to be encrypted and other metadata, such as encrypted value of the column encryption key for the column, algorithm type, as well as the location of its corresponding column master key. thereafter it goes to external key store to get the column master key.

Once driver has all the information, first it decrypts the encrypted column encryption key value thereafter uses this plain text column encryption key to encrypt the parameter received from application and sends the query with encrypted column values to SQL Server for processing.

SQL Server processes the query and returns the result set to the application (driver) with metadata for any encrypted column. The driver tries to find the plain text value of the column encryption key in the local cache and only makes a round to the external key store if it doesn’t find it in the local cache. Afterwards, the driver decrypts the returned result set and returns the plain text values to the application. " [databasejournal.com]

Observations:

The sensitive column data is only revealed within the application. It won’t revealed data at network (in-transit) level and database (rest) level.
To understand that column encryption is in use, the connection string must have the Column Encryption Setting= enabled; attribute.

Types of Encryption Modes

Deterministic Encryption:

This mode always generates the same encrypted value for any given plain text value. Also, this encryption method allows lookup, table joins, grouping and indexing on encrypted columns.

Encrypt('123-45-abcd') = 11b#2sz4
Repeat: Encrypt('123-45-abcd') = 11b#2sz4

**Observation:**It allow unauthorized users to guess encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False.

Randomized Encryption:

This mode always generates a random encrypted value for any given plain text value but it prevents searching, grouping, indexing, and joining on encrypted columns.

Encrypt('123-45-abcd') = 01br%r2@3
Repeat: Encrypt('123-45-abcd') = @67ujg034#

**Observation:**This method is less predictable and most secure. Preferable to use on columns which had high confidential data.

Enable Always Encrypted by using Wizard in SSMS

Step 1**:** Go to object explorer of management studio**>>>Right click on database that contains tables with columns we wish to encrypt>>>** point to Tasks >>>Then click “Encrypt Columns” to open the Always Encrypted Wizard:

Step 2: Review the Introduction page, and then click next:

Step 3: On the Column Selection page**>>>** Expand the tables >>>Select the columns that you want to encrypt>>> Set the Encryption Type to either Deterministic or Randomized**>>>** select an Encryption Key:

Note: If there is no previously created any encryption keys for this database, select the default choice of a new auto-generated key, and then click next.

Step 4: On the Master Key Configuration page >>> Choose ‘Auto generated column master key (It will generate the mater key for us). >>> Select location to store the new key**>>>** and select a master key source, and then click Next:

**Select Master key Source: **

Current User: Access the Master key current user only.

Local Machine: Access the master key all users. (Recommended).

Step 5: On the Run Setting page**>>>** choose whether to run the script immediately or create a PowerShell script >>> and then click Next:

Step 6: On the Summary page**>>>**Review the options what we have selected, and then click Finish. >>> Close the wizard when completed.

Once it configured we can able to see the key details under "Always Encrypted Keys" Tab as below:

View Always Encrypted data from SSMS

Assume there is table “EMP” and column “ID” was encrypted by using always encrypted future.

Do a quick SELECT from the table and view the encrypted column. In the below screenshot, the ID column was encrypted:

In order to decrypt the ID column, the following settings should be enabled in the SSMS client.

Go to connect to server dialog box >>> Additional connection parameters tab**>>>** Type Column Encryption Setting = Enabled >>> Click Connect:

Now, again do a quick SELECT from the table and view the encrypted column:

Summary

Overall, Always Encrypted is one of the important features introduced in SQL Server 2016 and available in higher versions, including Azure SQL Database. Always Encrypted helps us ensure that only approved applications and individuals can access sensitive data.

Share via