Understanding SQL Server Always Encrypted

Security is one of the most important requirements for a data-driven system. Encryption is one of the ways to secure the data. Wikipedia defines encryption as:

“Encryption is the process of encoding a message or information in such a way that only authorized parties can access it and those who are not authorized cannot.”

In SQL Server 2016, Microsoft introduced an encryption feature called Always Encrypted. In this article, we will see what Always Encrypted is, and how it can be used to encrypt and decrypt data, with the help of simple examples.

What is SQL Server Always Encrypted?

Always Encrypted is a security feature that allows the client application to manage the encryption and decryption keys, thus ensuring that only the client application can decrypt and use sensitive data.

Several encryption techniques exist, however they are not as secure as Always Encrypted. For instance, symmetric key encryption is used to encrypt data on the database side. A drawback of this approach is that if any other database administrator has the decryption key, he can access the data.

On the other hand, in case of Always Encrypted, the data is encrypted on the client side and the database server receives a ciphered version of the data. Hence, the data cannot be deciphered at the database end. Only the client that encrypted the data can decrypt it.

Key Types

SQL Server Always Encrypted feature uses two types of keys:

• Column Encryption Key (CEK)

It is always placed on the database server. The data is actually encrypted using column CEK. However, if someone on the database side has access to CEK, he can decrypt the data.

• Column Master Key (CMK)

This key is placed on the client side or any third party storage. CMK is used to protect the CEK, adding an additional layer of security. Whoever has access to CMK can actually decrypt the CEK which can then be used to decipher the actual data.

Encryption Types

• Deterministic

This type of encryption will always generate similar encrypted text for the same type of data. If you want to implement searching and grouping on a table column, use deterministic encryption for that column.

• Randomized

Randomized Encryption will generate different encrypted text for the same type of data, whenever you try to encrypt the data. Use randomized encryption if the column is not used for grouping and searching.

Configuring Always Encrypted Using SSMS

We can configure SQL Server Always Encrypted via SSMS. But before that, we need to create a database and add a table to the database. Execute the following script to do so:

CREATE DATABASE School

Use School
CREATE TABLE Student  
(  
   StudentId int identity(1,1) primary key,  
   Name varchar(100),  
   Password varchar(100) COLLATE Latin1_General_BIN2 not null,  
   SSN varchar(20)  COLLATE Latin1_General_BIN2 not null
)

In the script above, we create a new database named School. The database has four columns: StudentId, Name, Password, and SSN. You can see that the Password and SSN columns have a COLLATE. It is necessary to specify the COLLATE for the column that you want Always Encrypted. The type of encryption is specified as “Latin1_General_BIN2”.

Let’s now first try to add two records into the Student table.

insert into Student ( Name, Password, SSN)
VALUES ('John','abc123', '451236521478'),
('Mike','xyz123', '789541239654')

At this point of time, we have not configured Always Encrypted on any of the columns in the Student table, therefore if you try to select the records from the Student table, you will see the actual data values rather than the encrypted values. Execute the following query to select records:

SELECT * FROM Student

The output looks like this:

Let’s now configure SSMS for to enable Always Encrypted. As we said earlier, Always Encrypted creates column encryption keys and column master keys.

To see the existing column encryption keys and column master keys, for the School Database, go to Databases -> School -> Security -> Always Encrypted Keys as shown in the following figure:

Since you don’t have any encrypted record in the dataset, you won’t see any CEK or CMK in the list.

Let’s now enable encryption on the Password and SSN columns of the Student table. To do so, Right Click on Databases -> School. From the dropdown menu, select Encrypt Columns option as shown in the figure below:

Click Next button on the Introduction window. From the Column Selection window, check Password and SSN columns. For the Password column, select the encryption type as Randomized. For SSN column, choose Deterministic. This is shown in the following screenshot:

Click the Next button on the Master Key Configuration window. By default, the master key is stored on the client machine as shown below:

Click the Next button on the Run Settings and the Summary windows. If everything goes fine, you should see the following Results window.

Now if you again go to Databases -> School -> Security -> Always Encrypted Keys, you should see the newly created CEK and CMK as shown in the following figure:

Now try to select records from the Student table.

SELECT * FROM Student

The output looks like this.

From the output, you can see that the Password and SSN columns have been encrypted.

Retrieving Decrypted Data

The SELECT query returned encrypted data. What if you want to retrieve data in decrypted form? To do so create a New Query Window in SSMS and then click the Change Connection icon at the top of Object Explorer as shown in the following figure:

SQL Server connection window will appear. Select Options button from the bottom right as shown below:

From the window that appears, click on Additional Connection Parameters tab from the top left and enter “Column Encryption Setting = Enabled” in the text box as shown in the following screenshot. Finally, click the Connect button.

Now again execute the following SELECT query:

SELECT * FROM Student

In the results, you will see the records returned in decrypted form as shown below:

Conclusion

Always Encrypted is one of the latest security features of SQL Server. In this article, we briefly reviewed what Always Encrypted is and how to enable it using SQL Server Management Studio. We also saw a basic example of encrypting and decrypting data using Always Encrypted feature.