- 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 or Cell Encryption||Always Encrypted|
|This is an older encryption feature introduced in SQL Server 2005.||This is a newer and more advanced encryption feature included with SQL Server 2016.|
|The encryption key is stored in the database, meaning the SQL Server instance has access to the encryption key, and it can decrypt the data.||The encryption key is stored outside the SQL Server instance, typically in an external key store such as Azure Key Vault or Windows certificate store.|
|Database admins or anyone with sufficient privileges can view the encrypted data as plain text because keys are stored within the database.||Database admins can never view encrypted columns as plain text, which provides a higher degree of data confidentiality.|
|Data is protected at rest but when loaded in memory, it is only encrypted until the DECRYPTBYKEY function is used.||The data remains encrypted when stored on disk, in memory, and during transmission over the network.|
|The encryption and decryption operations are performed explicitly by the client application.||Always encrypted is completely transparent to the applications.|
|The client application needs to be heavily modified to support column-level encryption.||An Always Encrypted-enabled driver needs to be installed on a client computer to handle encryption and decryption transparently.|
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.
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:
- Expand the database (COMPANY, in our case), and then expand Tables.
- Right-click the table that contains sensitive data (Employees, in our case) and then select the Encrypt Columns option.
- 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.
Notice that the name of the encryption key is automatically set to CEKAuto1 (New).
- Click the Next button.
- 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.
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.
- On the Run Settings page, select the Proceed to finish now option, and click Next.
- On the Summary page, preview all the settings, and click the Finish button.
The wizard will now generate the column encryption key (CEK) and column master key (CMK). Then it will encrypt the selected column.
- Expand the Security > Always Encrypted Keys folder to view the CEKAuto1 and CMKAuto1 as the column encryption key and column master key, respectively.
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:
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.
Also, if you query the table again, you will notice that the SSN column information is no longer stored and displayed as plain text.
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.
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.
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.
Subscribe to 4sysops newsletter!
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.