What is Always Encrypted in SQL Server

Always Encrypted is a security solution that introduced with SQL Server 2016. With this solution, application developers can encrypt the columns from the client side. So, even someone with sysadmin authority on the instance cannot see the contents of these columns.

Difference Between Column Level Encryption and Always Encrypted

Previously we could encrypt our columns with column based encryption. But because this process was performed on the server side, users who have sysadmin privilege on the instance could access the data.

Details about column-based encryption can be found in the article “Column Level Encryption On SQL Server“.

Different Types of Encryptions in SQL Server

You can also encrypt the entire database using TDE. You can also find detailed information about TDE in “TDE (Transparent Data Encryption) On SQL Server“.

Another issue about encryption is encrypted backup. You can find details in my article “Encrypted Backup On SQL Server“.

If you want to encrypt objects in the database, you can read the article “How To Encrypt SQL Server Stored Procedures, Views and Functions“.

Always Encryption Usage

Always Encrypted works with two types of key.

Column Encryption Key

• The key used to encrypt columns. It is stored on SQL Server.

Column Master Key

Since Column Master Key is not stored on SQL Server and data encrypted with Column Encryption Key cannot be opened without this key, a high level security solution is provided.

The data is sent in an encrypted manner from the application server to the sql server and is sent back from the sql server to the application server in an encrypted manner. On the application server, the ADO.NET library decodes the password using the Column Master Key to allowing the application to view the data as clear text.

In this way, it is ensured that data is sent encrypted over the network.

Let’s make an example to understand the subject in more detail and to examine the details.

Example

We will implement the installation process through the application server to be realistic.

Let’s connect to the database server using SSMS via the application server and run the following script.

After running the script, as you can see, the results returned as clear text.

Lets encrypt name and identification_number columns.

Right-click on the table and click Encrypt Columns….

The first screen shows an explanation that Always Encrypted is designed to protect some of the information stored in the SQL Server Database and that encryption is performed on the application side. You can proceed by clicking “do not show this page again”.

On the next screen we select the columns we want to encrypt.

In the Encryption Type section, you can see Deterministic or Randomized options.

Deterministic

Encrypts the same data in the same way. For example, if there is a data with its value “Ahmet” in the database and the value of “Ahmet” is encrypted as ‘gferty’, it will also encrypt other “Ahmet” values in the database as ‘gferty’. If you select deterministic encryption, someone who obtains the data can discover the encryption model by working on the data. On the other hand, you can perform sorting, grouping, combining, and indexing operations.

Randomized

Encrypts data in a less predictable way. However, you cannot perform sort, group, merge, and indexing operations. That is, it does not generate the same encryption value for all “Ahmet” values ​​in the database as in the Deterministic option. A different encryption value is generated randomly each time. Thus, it becomes more difficult to solve the encryption model.

In the Encryption Key section, you will create a new Encryption Key named CEK_Auto1 (New) for Name and identification_number columns. Click next after choosing below options.

Who Can Access the Data

The next screen asks whether to store the master key on windows certificate store or on Azure Key Vault. In our example, we select the Windows certificate store.

In the “select a master key source” section;

• If we select Current User, only the user who is installing can access the data(the current user can access the data as clear text using any sql login that can access the data. That is, current user is a local user, not a sql login.).
• If we select Local Machine, all users who are authorized on the application server can access the data.

We continue by selecting Current User. Then click next and next and finish to complete the process.

You can create the Encryption Key and Master Key from the Security tab under the database instead of the wizard, as shown in the following screen.

When we create the Master Key over SSMS instead of the wizard, we have two options where the master key can be stored except Windows Cetificate Store and Azure Key Vault.

How To See the Data as Clear Text

After installation, you must add “Column Encryption Setting=ENABLED” to the connection string to access the data in a clear text manner.

If you want to access data from the application server with SSMS as clear text, you must add “Column Encryption Setting = ENABLED” to the “Options / Additional Connection Parameters” tab on SSMS.

When you try to read the data as clear text from the database server by adding “Column Encryption Setting = ENABLED” to “Options / Additional Connection Parameters” tab on the SSMS, you will receive the below error.

Msg 0, Level 11, State 0, Line 0

Failed to decrypt column ‘Name’.

Msg 0, Level 11, State 0, Line 0

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: ’52-F8-A2-E7-FD-F2-C7-BF-C9-9A’.

Msg 0, Level 11, State 0, Line 0

Certificate with thumbprint ‘559CBB33121DA44988155DC43F2445DBBDD6FC30’ not found in certificate store ‘My’ in certificate location ‘CurrentUser’. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.

Parameter name: masterKeyPath

If you want to read the data without adding  “Column Encryption Setting = ENABLED” to the “Options / Additional Connection Parameters” tab on SSMS, you will see data as follows. We suppose you have read privilege on the table.

Some Commands for Always Encrypted

• ALTER ANY COLUMN MASTER KEY (required to create and delete the master key.)
• ALTER ANY COLUMN ENCRYPTION KEY (required to create and delete column encryption key.)
• VIEW ANY COLUMN MASTER KEY DEFINITION (required to read the metadata of the master key.)
• VIEW ANY COLUMN ENCRYPTION KEY DEFINITION (required to read the metadata of the column encryption key..)

You can re-create the column master key. For details, please refer to “Column Master Key Rotation Process (Always Encrypted)”.