Click on the banner to learn about and purchase my database training on Azure

SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted

Views: 7.032 views
This post is the 2 part of 5 in the series. Data Protection
Reading Time: 8 minutes

Hey guys!
Ready for one more article?


With the advent of Better quality of care, business data security concerns are growing, and an area that was once overlooked is in evidence more than ever now. As a result, IT professionals, especially DBAs, have been looking for ways to reduce the risk of data exposure and one way to do this is to encrypt data to prevent unauthorized access by third parties.

My idea in this article is to demonstrate a SQL Server solution that lets you encrypt data, which is the Always Encrypted, available from SQL Server 2016 on Express, Standard, Enterprise, and Developer editions (Express and Standard from 2016 SP1).

Be sure to check out my post SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE), another SQL Server data encryption solution.

For the examples below, I will use the following script for base generation:

What is Always Encrypted

Always Encrypted is a feature designed to protect sensitive data, available from SQL Server 2016, such as credit card or national identification numbers (for example, US social security numbers) stored in Bank of America databases. Azure SQL data or SQL Server. Always Encrypted allows customers to encrypt sensitive data in client applications and never reveal encryption keys to the Database Engine. As a result, Always Encrypted provides a separation between those who own the data (and can display it) and those who manage the data (but should not have access).

One of the biggest advantages of Always Encrypted is that only users and applications that have the master encryption key have access to the original data. Not even DBAs and other sysadmin users can view the original data. This ensures data and information security at a level rarely seen in other solutions. Another great advantage of this solution is that the data is encrypted, as well as the logs, backups, and data trafficked across the network, ensuring complete security on all media, even if someone intercepts packets during their transmission. Because of this, losing the master key can be fatal to your data because recovering it is no longer possible since a backup made to an Always Encrypted database can only be restored to another instance if the master key be restored before.

Always Encrypted makes encryption almost transparent to applications. An Always Encrypted-enabled driver installed on the client computer accomplishes this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts sensitive data columns before passing data to the Database Engine and automatically reconfigures queries so that application semantics are preserved. Similarly, the driver transparently decrypts data stored in encrypted database columns that is contained in the query results.

However, while this feature ensures an excellent level of security, be aware of potential performance issues when using it and increasing space consumption:

Always Encrypted vs Transparent Data Encryption (TDE)

Below, I will list some similarities and differences between these two SQL Server encryption solutions:

Always EncryptedTransparent Data Encryption (TDE)
Column levelDatabase Level
Client Encryption (using a driver)Server Encryption (Database Engine)
Server does not know encryption keysServer knows encryption keys
Data in memory is encryptedData in memory is unprotected (plain-text)
Data on the network is encryptedData on the network is unprotected (plain-text)
Only users with key access can view the original data. Not even the DBA can view the original data without the key.DBA can view original data without key
Backups and log files are encryptedBackups and log files are encrypted
Requires application changes (may be small or large depending on the encryption algorithm you choose)No app changes required
Available from SQL Server 2016 - All editions, up to Express (Express and Standard from 2016 SP1)Available from SQL Server 2008 - Enterprise and Developer Only

Deterministic or Random Encryption

When using Always Encrypted, you will find that there are 2 ways to encrypt columns in this solution:

  • Deterministic: Deterministic encryption always generates the same encrypted value for the same text. Using deterministic encryption provides lookups, joins, groupings, and indexing on encrypted columns. However, it can also allow unauthorized users to estimate encrypted value information by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True / False or North / South / East / West region.
  • Randomized: Random encryption uses a method that encrypts data in a less predictable manner, ie for the same text the encrypted value is different. Random encryption is more secure, but prevents the use of lookups, collations, indexing, and joins on encrypted columns.

How to install and configure Always Encrypted on SQL Server

Now that I've explained some of the Always Encrypted theory, let's move on to the practical part.

The most practical way to encrypt a column is by using SQL Server Management Studio (SSMS):

And changing existing tables can also be done using PowerShell:

However, it is not possible to encrypt existing table columns using Transact-SQL, just create a new encrypted table (you can create a new encrypted table and migrate data from the previous table), which would have this syntax:

Viewing Always Encrypted Data in SQL Server

Now that we've encrypted the dbo table columns. Let's try to view the original data, logged in with a sysadmin user:

To redisplay the original data in SSMS, you will need to enter the parameter column encryption setting = enabled in the connection string:

After changing this setting, you can view the original data again.

Remember that the user can only see the original data if they have VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions or if you are storing Master Encryption Key and Column Encryption Key on the bank server (Windows Certification Store) and the certificate is stored on your machine or on your user. If you want to use a safer way, opt for Azure Key Vault (AKV).

To assist in configuring Always Encrypted in your applications, I have separated 2 more articles that can help DEV's to configure this feature with Azure Key Vault (AKV):

How to identify which columns are encrypted with Always Encrypted

To identify which columns are encrypted with Always Encrypted and which encryption algorithm is used, simply use the query below:


Always Encrypted Restrictions

Always Encrypted columns are not supported for the columns below (for example, the Encrypted WITH clause cannot be used in a column's CREATE TABLE / ALTER TABLE if one of the following conditions applies to the column):

  • Columns using one of the following data types: xml, timestamp / rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, and user-defined types.
  • FILESTREAM columns
  • Columns with the IDENTITY property
  • Columns with the ROWGUIDCOL property.
  • String columns (varchar, char, etc.) with non-bin2 collations
  • Columns that are keys to nonclustered indexes using a randomly encrypted column as a key column (deterministically encrypted columns are allowed)
  • Columns that are keys to clustered indexes using a randomly encrypted column as a key column (deterministically encrypted columns are allowed)
  • Columns that are keys to fulltext indexes containing random, deterministic, encrypted columns
  • Columns referenced by computed columns (when the expression performs operations not supported by Always Encrypted)
  • Sparse Column Set
  • Columns that are referenced by statistics
  • Columns using alias type
  • Partitioning Columns
  • Columns with default constraint
  • Columns referenced by unique constraints when using random encryption (deterministic encryption supported)
  • Primary key columns when using random encryption (deterministic encryption is supported)
  • Reference columns in foreign key constraints when using random encryption, or when using deterministic encryption, if the referenced and reference columns use different algorithms or keys.
  • Columns referenced by check constraint
  • Columns in tables that use Change Data Capture (CDC)
  • Primary Key Columns in Change Tracking Tables
  • Masked Columns (using Dynamic Data Masking)
  • Columns in Stretch Database Tables (Tables with columns encrypted with Always Encrypted can be Stretch enabled.)
  • Columns in external tables (PolyBase) (Note: The use of external tables and tables with encrypted columns in the same query is supported)
  • Table-valued parameters targeting encrypted columns are not supported.

The following clauses cannot be used for encrypted columns:


The following features do not work on encrypted columns:

  • Transactional Replication or Merge Replication
  • Distributed Queries (Linked Servers)

Dynamic Data Masking

After demonstrating the use of this encryption solution, you may be asking yourself, "What about Dynamic Data Masking (DDM) ???". Well, for a start, DDM is not a data encryption solution, but a data masking solution.

While data encryption effectively prevents your data from being restored (including from backups) and accessed improperly, data masking with Dynamic Data Masking only restricts content that is displayed at the end of a SELECT command, or that is, if you have access to a backup file, you can restore it to an instance where you are sysadmin and you have free access to previously masked data.

To learn more about Dynamic Data Masking, see the article. SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM), where I demonstrate how to use it, restrictions and even how to “break” the masking and access the original data (even without having permission to do so).

Well guys, I hope you enjoyed this article and now start protecting your data better!
Big hug and until next time!