This article will give a brief overview of SQL Server Always Encrypted Columns.
What are Always Encrypted Columns?
Always Encrypted columns are a special type of table column within SQL Server. They only store encrypted binary data, regardless of the actual datatype assigned to the column. They are particularly useful for protecting sensitive data such as passwords or personal user data as they ensure that the data is always encrypted within the database. Additionally, all encryption and decryption is handled by the client, which means, if properly done, the database engine on its own does not have sufficient information to read the data in those columns.
Due to being encrypted, Always Encrypted columns cannot be used as any part of a query other than the SELECT statement. The only exception to this rule is the ‘Deterministic’ encryption option, which allows for equality checks within other parts of the query, such as the WHERE clause (However, greater-than checks, less-then checks, or any SQL functions will still raise an error if used on those columns).
NOTE: The default collation in SQL Server is case-insensitive. However, when enabling Always Encrypted on a database column, it will be changed to a case-sensitive collation. Due to this, Always Encrypted columns are always case-sensitive, which can affect equality checks when using ‘Deterministic’ encryption.
Which versions of SQL Support the Always Encrypted Columns?
Always Encrypted Columns were introduced as a new feature in SQL Server 2016 (Version 13.x as shown in SQL Server Management Studio). However, it was only available in the Enterprise edition until the service pack 1(SP1) update (Version 13.0.4xxx.x as shown in SQL Server Management Studio):
- SQL Server 2012: NOT SUPPORTED
- SQL Server 2014: NOT SUPPORTED
- SQL Server 2016: NOT SUPPORTED
- SQL Server 2016 SP1 and newer: SUPPORTED
CEK? CMK? What are all these keys?
SQL Server Always Encrypted columns use two layers of encryption. The CEKs (Column Encryption Keys) are used to encrypt the data in the columns. Then, the CMK (Column Master Key) is used to encrypt all CEKs. Only the encrypted version of the CEK is stored in SQL Server, which makes further encryption/decryption impossible without the CMK. The CMK is then installed on the server that will be accessing the encrypted columns, such as your website server. As long as your SQL client supports Always Encrypted Columns and has access to the CMK, it will be able to read/write to the encrypted columns.
How can I enable Always Encrypted Columns?
The easiest way to enable Always Encrypted Columns is using SQL Server Management Studio. This can be done by right-clicking on the database and choosing ‘Tasks’ -> ‘Encrypted Columns…’ or right-clicking on a specific table and choose ‘Encrypted Columns…’:
This will bring up the wizard for enabling encrypted columns. For each column, you will have to choose a couple options:
Encryption Type:
- Randomized: Encrypting the same data twice will produce different output.
- Deterministic: Encrypting the same data twice will produce the same output.
Deterministic encryption allows you to perform equality checks on the encrypted data. However, it also allows a potential attacker to infer information about encrypted data. For example, if you had a column containing two different roles, ‘user’ and ‘admin’, and you encrypted that column with the ‘Deterministic’ option, you could quickly tell that there were only two possible options just by looking at the encrypted binary values (even though you wouldn’t know what they were). And if you had a user named ‘Admin’ in the table, you could probably guess what role that user might be in.
Randomized encryption does not allow anything to be done with the data other than SELECT or UPDATE. However, all encrypted binary values in the table will be different. Given our example above, Randomized encryption would prevent someone from being able to see how many roles existed, which would make it difficult to guess who might be in which role.
Based on this, the most secure option is to always use ‘Randomized’ encryption unless you have an application requirement that needs ‘Deterministic’ encryption.
After choosing the columns, encryption types, and column encryption keys, you will be taken to the “Master Key Configuration” screen:
If you have an existing ‘Column Master Key’(if this is not the first time you have enabled always encrypted columns), you should choose it from the drop-down on this page.
The other option is related to whether you will store the ‘Column Master Key’ at the ‘User’ level or the ‘Machine’ level. Our recommendation is to choose ‘Local Machine’, as this allows you to install the key just one time per machine and manage which users have access to the key using permissions. If you do not see the ‘Local Machine’ option, you will need to re-open SQL Server Management Studio using the ‘Run as Administrator’ option in Windows.
Choosing the ‘Current User’ option will require you to login or impersonate every user that will use the key and install the key into that user’s certificate store, which may not be possible for websites running under an ‘Application Pool Identity’ account. For this reason, the ‘Current User’ option is not recommended.
Once you finish the rest of the pages and confirm the change, the columns in the database will be encrypted and your ‘Column Master Key’ will be placed in the certificate store on the computer where you are running SQL Server Management Studio. You can export the key by opening the certificate management console (Run -> certlm.msc).
Expand ‘Personal’ and choose ‘Certificates’. Then, right-click on the Always Encrypted certificate and select ‘All Tasks’ -> ‘Export…’. Within the export wizard, you will want to choose ‘Yes, export the private key’ on the second page.
Finish the export certificate wizard and save the key to a password protected .pfx file. This file will be used to install the certificate on any machine that will be used to access the encrypted columns. If you lose this key, all data in the encrypted columns will be irretrievable so it is advisable to store a backup copy.
When installing the certificate on a client machine, you can manage permissions to the key by opening the certificate manager (Run -> certlm.msc), navigating to the key, right-clicking on the key, and choosing ‘All Tasks’ -> ‘Manage Private Keys…’.
Once the client application has access to the key, you must add the following connection string parameter to enable encrypted colums:
.Net SQL Client: “Column Encryption Setting=enabled”
JDBC Client: “columnEncryptionSetting=Enabled”
Using Always Encrypted Columns with ColdFusion
In order to use Always Encrypted Columns with ColdFusion, you must install the latest Microsoft JDBC driver for Java into the ColdFusion lib directory. Then, you must create the datasource in the ColdFusion administrator using the ‘Other’ option with the following parameters:
JDBC URL: jdbc:sqlserver://SERVER_HOSTNAME;databaseName=DATABASE_NAME;columnEncryptionSetting=Enabled;
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
You must also ensure that the ColdFusion service account has access to the private key. This service usually runs under the ‘cfusion’ user so you will want to give read permissions for that user to the private key of the ‘Column Master Key’.
Additional Information
Enable viewing of encrypted columns in SQL Server Management Studio:
https://social.technet.microsoft.com/wiki/contents/articles/37979.working-with-the-always-encrypted-feature-in-sql-server-2016.aspx#View_Always_Encrypted_data_from_SSMS
View column master keys for your database:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-master-keys-transact-sql?view=sql-server-2017
View column encrypted keys for your database:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-encryption-keys-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-encryption-key-values-transact-sql?view=sql-server-2017