How to set up column level encryption with Always Encrypted

March 08, 2022

In this post I’m going to detail how to set up column level encryption with Always Encrypted. SQL Server’s Always Encrypted feature is a simple and effective way to setup column level encryption. Using Always Encryption I’m able to encrypt individual columns in my database without having to worry about encrypting the entire database. It’s also very easy to setup column level encryption after the initial configuration is done. Once a column is encrypted the data can only be read by a client that can decrypt the data using a generated certificate. Because of the level of encryption all the data is encrypted at rest as well as in transit.

Here’s how to configure Always Encryption on a single database column. I’m going to walk through encrypting a Social Security Number column named SSN. If you want to know more details about Always Encryption and more configuration details I recommend this Configure Always Encrypted using SQL Server Management Studio page.

Set up column level encryption with Always Encrypted

  1. Run SQL Server Management Studio as an administrator. You will need to have admin rights on your machine in order to do this.
  2. Create a new Column Master Key
    • Navigate to your database –> Security –> Always Encrypted Keys
    • Right click Column Master Keys
    • Select New Column Master Key
    • Give your master key a name. I like to start mine with CMK followed by the objective. Something like CMK_SSN since I’m encrypting the social security number.
    • Set Key store to Windows Certificate Store – Local Machine. This part is key, you want the certificate to be available to any user that needs it on your machine. If you select Windows Certificate Store – Local User you can’t decrypt the values as any other user. Like if your IIS instance is running as a different user. You will also need to be running SQL Server Management Studio as an admin otherwise this option will not be available to you.
    • Click Generate Certificate at the bottom of the table of certificates. You should now see a new certificate in the table of certificates. Select the newly created certificate.
    • Click OK
  3. Create a new Column Encryption Key
    • Navigate to your database –> Security –> Always Encrypted Keys
    • Right click Column Encryption Keys
    • Select New Column Encryption Key
    • Enter a name for your column encryption key. I like to start with CEK followed by the objective. Something like CEK_SSN since I’m encrypting the social security number.
    • Select your column master key you created above from the drop down labeled Column master key
    • Click OK
  4. Learn the differences between Column Master Keys and Column Encryption Keys here
  5. Now that the encryption keys are set, it’s time to encrypt the the column. Navigate to Tables –> Your Table –> Columns, right click your column to encrypt and select Encrypt Column. In my case the I’m selecting the column SSN.
  6. Select the checkbox next to your column. In the Encryption Type column choose either Deterministic or Randomized
    • Select Deterministic if you plan to use this column in any look ups or queries such as you would in a where clause. This method creates the same encrypted value for same plaintext value for each value that is encrypted.
    • Select Randomized if you will won’t use this column in a where clause or by any look ups at the database level. This method creates a unique encrypted value for each plaintext value that is encrypted.
  7. Select the Encryption Key you created in the previous steps (CEK_SSN)
  8. Click Next –> Next –> Next –> Finish
  9. The encryption process will begin and you’ll see a message stating the operation has passed when it’s finished
  10. Click Close
  11. Your column is now set up for Always Encryption.

Configure SQL Management Studio for Always Encryption

There are a couple tasks you have to complete in order to get SQL Server Management Studio to work with Always Encryption.

  1. The first is to disconnect your current connection and reconnect to the server instance but with the following options:
    • Click Options >> in the lower right corner of the Connect to Server window
    • Select the Always Encrypted tab
    • Check the box next to Enable Always Encrypted (column encryption). This will tell SQL Server that you want to try to decrypt any encrypted columns
    • Click OK
  2. The next task is to enable Parameterization for Always Encryption. This will allow you to send and use parameters in your queries from SSMS when working with encrypted columns.
    • Navigate to Tools –> Options –> Execution –> Advanced and enable Parameterization for Always Encryption
  3. You will now be able to insert data into an encrypted column.

Example insert statement

Run the below query to insert a new encrypted record into the database. 

declare @ssn nvarchar(max) = '123-46-5444'

insert into DemoTable
values (@ssn)


select * from DemoTable

You will now have data saved in the encrypted column. Because Enable Always Encryption was checked at the connection window the results are returned decrypted.

In order to see the encrypted values, reconnect from the server instance and uncheck the Enable Always Encryption checkbox in the Always Encrypted tab. This checkbox tells SQL Server to try to decrypt the encrypted columns. You should now see the encrypted values in the database table.

Errors

After you’ve set up Always Encryption you might run into this error:

Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’. The last 10 bytes of the encrypted column encryption key are: ‘XX-XX-XX-XX-XX-XX-XX-XX-XX-XX’.

This error is likely due to a permissions issue when your client tries to access the generated certificate. Walking through these steps should help you solve this issue.

Conclusion

That covers how to enable and configure Always Encryption on a database column. There are a few hoops to run through to get it set up and running, but overall I really enjoy working with Always Encryption. Once it’s up and running there’s little maintenance that needs to be done so I find that it’s a good option when I need to encrypt data at the database level.