Learning About Always Encrypted in SQL Server 2016

存储架构 2016-06-21


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.


Quick SQL Server Health Check Report By: Joe Gavin | | Related Tips:More > Database Administration Trends in Database Administration - How Do You Compare - Click To Find ...
SQL Server 2016 Multi Threaded Log Writer This is probably one of the most unheralded new features of SQL Server 2016 which gets but a single bullet point in the CSS engineers “It Just Runs Fa...
Why Don’t Universal Groups Work in SQL Server? Tags: https://www.flickr.com/photos/123327536@N08/23891946434 If you’ve tried using Universal Groups in Active Directory to access your...
Crosstab Query with Dynamic Columns in SQL Server ... I'm having trouble with a Cross Tab query in SQL Server and was hoping that someone could please help? I have the following table: - Student ID - ...
SQL Server Database Files Wise I/O Latency Details One of the real-world I/O troubleshooting problems is – If you have your databases spread across multiple LUNs and you want to find out which datab...