Home » SQL Master Database » Learning About Always Encrypted in SQL Server 2016

Learning About Always Encrypted in SQL Server 2016

Stephen West | Modified: 2016-06-28T11:42:43+05:30|SQL Master Database | 3 Minutes Reading


SQL Server has an additional layer of security that keeps the user’s data protected from any malicious activity.

This security feature protects data from vulnerable activities by encrypting the data stored in the database.

When the organizations are working with sensitive data, they must ensure some sort of security, which could be encrypted, to protect their data.

The encryption of data can be done by different ways including Column Level Encryption, Transparent Data Encryption, Backup Encryption, Database File Level Encryption, SQL Server Connectors Encryption.

These different types of encryption methods provide high security for data that exists in SQL Server database preventing the unauthorized attempts on database.

One cannot directly access an encrypted set of database. To perform any action on such database, one needs to have the decryption key to it. This means the data is not encrypted throughout.

Always encrypted in SQL Server 2016 can encrypt the data while transmitting, storing, creating and even when performing an action on database.

The Problem With Previous SQL Versions

In the past user were able to access the desired encrypted data by decrypting it using key/Password/certificate due to which security of rest of the database files becomes compromised. This was possible with Transparent Data Encryption.

Solution – Always Encrypted in SQL Server 2016

To solve such problems, Microsoft introduces a new feature namely ‘Always Encrypted’ in SQL 2016 Version in which user can perform action on database in encrypted form. It protects data against rouge user and from other malicious attacks.

Unlike TDI Always Encrypted feature only encrypt some column of the database, instead of encrypting the complete database.

Implementing Always Encrypted Concept in SQL Server 2016

Step1. Create Column Master Key – The Master Column key protects all Column Encryption keys. There must be one master key before encrypting any column using CEK.

For creating Column Master Key user needs to right-click on the CMK definition folder and then click on New Column Master Key, this will open up New Column Master key definition. In Name Box, user needs to enter the name of Column Master key.

Step2. Column Encryption Key – This encryption key protects all encrypted columns.

For creating a column encryption key, one needs to right-click on Encryption Keys Folder and then click on New Column Encryption key. This will opens a New Column Encryption key Window, now user needs enter the name of Column encryption Key.

Step3. Column Level Encryption Settings – In Always Encrypted concept columns are encrypted with specific Column Encryption Key and algorithm. There are two types of Encryption to be used:

  • Deterministic – It always generates same ciphertext for given plaintext and key, this type of encryption not secure because there is lack of randomness in ciphertext. It can be indexed.
  • Randomized – It cannot be indexed and more secure as it is cannot be evaluated.

Step4. In last step user can Create the Table with encrypted column in SQL Server Management Studio (SSMS), user needs to enter CREATE TABLE statement, it will create a table with encrypted columns.

By using Deterministic, encryption user can apply lookups, joins and groupby operation, whereas in Randomized encryption, user cannot apply operations on Tables.


After going through the complete procedure of Always Encrypted in SQL 2016, it is clear that it provides better security than Transparent Data Encryption by encrypting only specified columns and user can apply Deterministic or Randomize Encryption according to their needs.