Majority of my current projects are clients of Insurance industry, and similar to any sector which handles and stores personal data they also follow stringent security policies.
Audits are regular thing in any organization and in one of the internal audits it was decided to hide the confidential data like Social Security Number from everyone including all the DBAs.
Prior to SQL Server 2016 there wasn’t any feature that could have helped but starting from SQL Server 2016 we have Always-Encrypted.
The concepts that helps in protecting the data even from DBA are:
1. Column Master Key (CMK)
2. Column Encryption Key (CEK)
Column Master Key
It is a key encrypting key (for example, a key that is used to encrypt other keys) that is always in a client’s control, and is stored in an external key store. An Always Encrypted-enabled client driver interacts with the key store via a CMK store provider, which can be either part of the driver library (a Microsoft/system provider) or part of the client application (a custom provider). – Taken from docs.
Column Encryption Key
It is a content encryption key protected by CMK – docs.
What it basically means is that we have two encryption keys one (CMK) is stored somewhere where even the DBAs don’t have access to and the second key (CEK) stored in the database which is encrypted by the first.
Configuring Always Encrypted
We can configure AE using SSMS or using Powershell. We cannot use T-SQL for this because we need to have a certificate created at Windows or in Azure Key Vault and that is not possible using T-SQL.
Configuring Always Encrypted is easy at SQL Server end but there are many things to consider. Refer this link for the limitations.
To configure AE using GUI, right click the database and select “Encrypt Columns”
Encryption type is deciding if the same plain text will lead to same cipher or a different one every time.
MS suggests to use deterministic encryption for columns that will be used as search or grouping parameters. And use randomized encryption for data such as confidential investigation comments, which aren’t grouped with other records and aren’t used to join tables
Once the columns are selected choose the Master Key, either the Windows certificate store or from Azure Key Vault and choose an already created certificate or create a new one.
In the next tab you will be given the option to either generate PowerShell script or to proceed to finish.
Hiding the data from a DBA
The main thing to consider is that the server from which the Windows certificate is generated should not be the database server itself. If we are creating the server certificate from database server than after configuring AE take the backup of the certificate and move it to the application server.
If using WCS make sure to restore the backup of the certificate on every server from where the data will be accessed.
Notes and things to consider
- The database only stores the metadata of the Column Master key, original key is stored in the WCS or Azure Key Vault.
- Security admins can be given the permission or access to create CEK and CMK but not the actual data. And DBA can have access to the data and the metadata but not to the WCS or AKV. And so restricting the data visibility from both.
- The client application issues a parameterized query. And has to use the “Column Encryption Setting = Enabled” in the connection string.
- Temporal and CDC is not supported, only change tracking is.
- Replication is not supported.
- Always On and Log Shipping can be be configured as long as we have the certificate at the other end too.
- When using a Web Server in between like IIS then the certificate should be stored there, or that server should have access to AKV.
There are four permissions for Always Encrypted:
ALTER ANY COLUMN MASTER KEY (Required to create and delete a column master key.)
ALTER ANY COLUMN ENCRYPTION KEY (Required to create and delete a column encryption key.)
VIEW ANY COLUMN MASTER KEY DEFINITION (Required to access and read the metadata of the column master keys to manage keys or query encrypted columns.)
VIEW ANY COLUMN ENCRYPTION KEY DEFINITION (Required to access and read the metadata of the column encryption key to manage keys or query encrypted columns.)
Changing the CMK or CEK
In case the keys are compromised we can always rotate the key. To rotate the CMK:
1. Database -> Security -> Always Encrypted Keys -> Right click and create new Master Key.
2. Once created right click the original CMK and choose “Rotate”
Choose the new CMK in Target and click OK. It will decrypt all the CEK using the older CMK and re-encrypt it using the new CMK and store it.
To change the CEK: Object Explorer -> Right the database -> Choose encrypt Columns and in the Column Select tab select another CEK.
Removing Always Encrypted
To remove AE go back to Encrypt Columns and select plain text for the encrypted columns.
That’s all for today, hope it helps you.