Click here to Skip to main content
15,610,839 members
Articles / Web Development / ASP.NET
Posted 5 Jul 2016


15 bookmarked

Always Encrypted Feature in SQL Server 2016

Rate me:
Please Sign up or sign in to vote.
4.92/5 (14 votes)
5 Jul 2016CPOL8 min read
The idea is to implement the latest and first of its kind “Always Encrypted” feature provided by Microsoft SQL Server 2016 designed to encrypt/decrypt sensitive data on back-end database engine


The idea is to implement the latest and first of its kind “Always Encrypted” feature provided by Microsoft SQL Server 2016 designed to encrypt sensitive data on backend database engine.


Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, "Always Encrypted" provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).


In this article, we are going to:

  1. Create a simple database table and connect the same with ASP.NET MVC application. Scaffold controller and views to populate data. If you are familiar with this process, go directly to step 2.
  2. Encrypt sensitive data columns in database table using Always Encrypted feature in SQL server 2016.
  3. Make necessary changes in ASP.NET MVC application to automatically encrypt/decrypt the data.

Create a Simple Database Table and Connect the Same With ASP.NET MVC Application

The first step is to create a new database “AlwaysEncryption” with the table name “Customer”. Assuming Email and SSN columns hold sensitive information.

Image 1

Now that the database is ready, we will create a new ASP.NET Web Application “AlwaysEncryption”. In this sample, I’m going to use Visual Studio 2015.

Image 2

Select MVC from the New ASP.NET Project template list. As we are not focusing on authentication, click on “Change Authentication” button to select “No Authentication” option and click "OK".

Image 3

This is how the basic bootstrap MVC solution looks like:

Image 4

Now go ahead and add a new Entity Data Model file to our Models folder:

Image 5

From the “Entity Data Model” Wizard, choose “EF Designer from database” and click “Next”.

Image 6

From the next wizard screen, choose “Microsoft SQL Server” as data source and click “Continue” to build the connection.

Image 7

Using “Connection Properties” wizard window to connect to the “AlwaysEncryption” database. Make a quick “Test Connection” to succeed and click “OK” to continue.

Image 8

From the next wizard screen, select “Yes” radio option to store sensitive SQL credential information within connection string. Then provide the context name “AlwaysEncryptionContext” that should go directly into connection string attribute in web.config file.

Image 9

Click “Next” to choose Entity Framework version for the application. In our case, I choose EF 6.x option.

Image 10

Click “Next” to choose our “Customer” table and name the Model Namespace and click “Finish” to import entity model.

Image 11

Visual Studio 2015 will show a couple of security warning dialog boxes before actually running the Text Template (T4) file. Click “OK” to generate context as well as entity model classes.

Image 12

This is how our solution looks after the import.

Image 13

Scaffolding Controller with Views using Entity Framework

Now right click on the Controller folder and add “New Scaffolding Item…” to create MVC 5 Controller along with views using Entity Framework.

Warning: Always first build the application after importing entity model from database before scaffolding a controller or view.

Image 14

Select the “Customer” Model class and “AlwaysEncryptionContext” as Data Context class and name the new controller as “CustomersController”. Click “Add” to create controller as well as views to perform all CRUD operations.

Image 15

This is how our solution looks like after scaffolding controller and its corresponding CRUD views:

Image 16

Now, set the default controller and action in route configuration file to our new Customers controller with Index action method.

    name: "Default",
    url: "{controller}/{action}/{id}",
    defaults: new { controller = "Customers", action = "Index", id = UrlParameter.Optional }

Go ahead and run the application to populate some records in Customer table.

Image 17

Now go to SQL Server Management Studio and run the following query to list all Customer records. This will display all data in plain text.

Image 18

To Encrypt Sensitive Data Using Always Encrypted Feature in SQL Server 2016

To encrypt sensitive data in Microsoft SQL 2016, go ahead and right click on “Customer” table to select “Encrypt Columns…” from the context menu.

Image 19

This will show Always Encrypted wizard with “Introduction” screen, click “Next”.

Image 20

Now we get “Column Selection” Wizard screen, this is where we choose all data columns from the selected table that need to be encrypted.

Here, we selected Email and SSN columns to be encrypted. Notice that the “Encryption Type” column is set to “Deterministic” and choose default or auto generated Column Encryption Key (CEK).

Image 21

Selecting Deterministic or Randomized Encryption Type

Always Encrypted supports two types of encryption: Deterministic Encryption and ​​​​​​ Randomized Encryption.

Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.

Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records and are not used to join tables.

After selecting the columns with the desired encryption type, click “Next”, this will display “Master Key Configuration” Wizard screen. In order for us to encrypt columns using CEK, we need to have a Master Encryption Key (MEK) which is stored outside of the database in either Windows Certificate store or Azure key vault.

Here, we auto generate MEK and choose to store the same in Windows certificate store under Current User.

Image 22

Click next to view “Run Settings” Wizard. This screen warns users not to perform any read/write operations onto the table while encryption and decryption is in process. This may lead to potential data loss. SQL Server 2016 recommend to run this settings during planned maintenance window.

Image 23

Hit “Next” to view the “Summary” wizard screen. Click “Finish” after verifying the settings.

Image 24

This will show the "Results" wizard screen with details. In our cases, it passed all three steps:

  1. Generated a new Column Master Key CMK in windows certificate
  2. Generated new Column Encryption Key CEK, and
  3. Performed encryption operation on selected columns on the Customer table

Image 25

Now again, run the select script to see the selected columns encrypted. Perfect, isn’t it?!

Image 26

Encrypting columns in a table will definitely alter the table schema but not like what we all expected. There will be no change to the column data types. If you closely look at the table create script, we can see COLLATE along with ENCRYPTED WITH CEK and the ALGORITHM.

Note: As of now, there is only one encryption algorithm “AEAD_AES_256_CBC_HMAC_SHA_256” provided by SQL Server 2016.

Image 27

Make Necessary Changes in ASP.NET MVC Application to Automatically Encrypt/Decrypt the Data

Now, re-run our MVC application to view how encrypted data is shown in our views. The strange thing is that it shows the following error message. The application is expecting string data type in Email column but it has been fed with encrypted data stream as an array of bytes.

Image 28

To fix this, we need to update our connection string to enable “column encryption settings”. This will allow our application to automatically encrypt/decrypt the necessary columns when connecting to the database.

column encryption setting=enabled

This is how our connection string looks like after the update.

Image 29

Now run the application once again to view columns automatically decrypted/encrypted. The application makes use of MEK as well as CEK to decrypt/encrypted columns.

Image 30

If you closely look at the "Always Encrypted Keys" settings under database Security:

Image 31

Right click on CEK to generate its create script:

Image 32

Right click on CMK to generate the create script. Here, you can see the path to Windows Certificate:

Image 33

If you go and look for "Always Encrypted Certificate" under Current User…

Image 34

Here are the certificate details when you double click it.

Image 35

Suppose you take a backup of the database and restore the same on other machine/server. Having the MVC application targeted to the new database… What do you think, the application would retrieve the data if we restore the database onto another machine or server?

The answer is NO.

Why? Because, although we may have the database restored from the backup file and have access to all CEKs to encrypt/decrypt columns, we do not have the Master Encryption Keys which are stored externally on Windows Certificate store. And to get the MEK certificates, we need to contact the owners.

We will get an error when doing this as the application could not able to decrypt/encrypt a column using keys provided.

Image 36

Image 37

Image 38

Check my other article (yet to be published) on how to export and/or import Master Encryption Key from/to Windows Certificate store.

Click here to learn more about "Always Encrypted" feature in SQL Server 2016.

I hope you had a wonderful time learning new things. Let me know if you have any suggestions or improvements.

Feel free to rate this article, bookmark and leave comments for better clarification.


  • 6th July, 2016: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Technical Lead
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionHelp Pin
Member 1412424320-Jan-19 19:22
Member 1412424320-Jan-19 19:22 
QuestionError Pin
Member 141103107-Jan-19 6:31
Member 141103107-Jan-19 6:31 
QuestionUse of Master or Encryption key in the application that consumes the database Pin
Ankit Mittal1920-May-17 11:46
professionalAnkit Mittal1920-May-17 11:46 
AnswerRe: Use of Master or Encryption key in the application that consumes the database Pin
Sreekanth Mothukuru22-Jul-17 2:58
Sreekanth Mothukuru22-Jul-17 2:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.