SQL Server Always Encrypted

• Author
• Recent Posts

Surender Kumar

Surender Kumar has more than twelve years of experience in server and network administration. His fields of interest are Windows servers, Active directory, PowerShell, web servers, networking, Linux, virtualization, and Kubernetes. He loves writing for his blog.

Latest posts by Surender Kumar (see all)

• Update container images with Copa - Mon, Nov 27 2023
• Deploying stateful applications with Kubernetes StatefulSets - Wed, Nov 1 2023
• Install and enable IIS Manager for Remote Administration - Thu, Oct 26 2023

Always Encrypted vs. column encryption

There is a lot of confusion between column (or cell) encryption and Always Encrypted features. While both share a common goal of encrypting the desired columns, they offer distinct features. The following table covers a few differences:

Column encryption key and column master key

When Always Encrypted is enabled, the columns are encrypted with the help of a column encryption key (CEK). The CEK is, in turn, encrypted by the column master key (CMK). The CEK is stored in the database in an encrypted format along with the path of the CMK. But, most importantly, the CMK (which encrypts the CEK) is stored in an external key store (e.g., Azure Key Vault or Windows certificate store). The client must have access to the CMK to successfully decrypt the CEK and the actual column data.

Deterministic vs. randomized

When using Always Encrypted, choosing the right encryption type is very important. Two types of encryption are supported:

• Deterministic: This always generates the same ciphertext for a given plain text value, which makes it less secure than randomized encryption. However, it supports operations such as grouping, indexing, filtering, joining, etc. on the encrypted columns.
• Randomized: This encrypts the data less predictably, which makes it more secure but doesn't support the common operations mentioned above.

Considering these differences, you can choose the type of encryption that is right for you.

Always Encrypted demo

For the demo, I have a COMPANY database with an Employees table. The table has various columns storing information about employees, but most importantly, the column named SSN stores social security numbers. Our goal is to encrypt the SSN column so that nobody (including the database admins) can read the information stored in this column.

Viewing the SQL Server database before enabling Always Encrypted

Encrypting a column

To encrypt a column of a database table using Always Encrypted, launch SQL Server Management Studio (SSMS), connect your SQL Server instance, and follow these steps:

1. Expand the database (COMPANY, in our case), and then expand Tables.
2. Right-click the table that contains sensitive data (Employees, in our case) and then select the Encrypt Columns option.
   

   Launch the Always Encrypted wizard in SQL Server Management Studio

3. In the Always Encrypted wizard, click Next to skip the Introduction page.On the Column Selection page, select the desired column (SSN, in our case). You may select multiple columns here if needed. In this step, you also need to select an encryption type for the column encryption key (CEK). I will choose Deterministic encryption and leave the defaults for everything.
   

   Select columns and encryption type for Always Encrypted

   Notice that the name of the encryption key is automatically set to CEKAuto1 (New).
   

4. Click the Next button.
5. On the Master Key Configuration page, define an external key store provider to store the column master key (CMK). You get two options for that: Windows certificate store or Azure Key Vault. To use Azure Key Vault, click the Sign In button, complete the login process, and select an Azure Key Vault. For this demo, I will choose the Windows certificate store for the Current User.
   

   Master key configuration to protect the column encryption key CEK

   Note that if you're working in an elevated session of SQL Server Management Studio, you get two choices for the Windows certificate store: Current User or Local Machine.

6. On the Run Settings page, select the Proceed to finish now option, and click Next.
   

   Finish the Always Encrypted wizard

7. On the Summary page, preview all the settings, and click the Finish button.
   

   Review the summary of the Always Encrypted wizard

   The wizard will now generate the column encryption key (CEK) and column master key (CMK). Then it will encrypt the selected column.

   

   View the results of the Always Encrypted wizard

8. Expand the Security > Always Encrypted Keys folder to view the CEKAuto1 and CMKAuto1 as the column encryption key and column master key, respectively.
   

   Viewing the column encryption key CEK and column master key CMK in the SQL Server database

When you look at the properties of the CMK, you will see the key store provider and the key path, as shown in the screenshot below:

View the key store provider and key path under the column master key CMK properties

You can also view the Always Encrypted certificate in the User or Machine certificate store, depending upon where you stored it. Don’t forget to backup and restore this certificate when reinstalling Windows on your computer.

View the Always Encrypted certificate in the Windows certificate store

Also, if you query the table again, you will notice that the SSN column information is no longer stored and displayed as plain text.

Query the database table again to view the information in encrypted form

View the encrypted column

Your data is now encrypted, which is good. But what if you, as a data owner, want to view the encrypted information? Well, in that case, you can adjust a setting in SQL Server Management Studio (or any other tool) and view the encrypted information as plain text as long as you have access to the column master key.

Since we already have the CMK in our certificate store, to view the encrypted information, launch the SQL Server Management Studio, click the Options button on the connection screen, select the Always Encrypted tab, and make sure the Enable Always Encrypted (column encryption) checkbox is selected. Now, connect to the SQL Server instance.

Enable the Always Encryption setting in the SQL Server Management Studio connection

Once you configure this setting, the client driver in SQL Server Management Studio will automatically decrypt the data stored in the SSN column for you.

Viewing the encrypted data as plain text using the Always Encrypted setting in SQL Server Management Studio

You can see in the screenshot that even though the column is encrypted, you can view the information as plain text since you have the column master key on the same system. The client driver handles the encryption/decryption for you behind the scenes. The best thing is that your data is always encrypted, whether it is stored on the disk, in memory, or a backup (.bak) file. Even if a malicious user (or a rogue admin) steals a backup file and restores it to their server, they will not be able to view the sensitive information since it is encrypted by Always Encrypted, and they do not have the column master key to decrypt it.

Conclusion

Always Encrypted is particularly useful in scenarios where your database needs to be stored in an untrusted environment, such as a third-party hosting provider or in the cloud. You can prevent database admins and privileged users from viewing your sensitive information.