Configuring Always Encrypted on Azure SQL by using Azure Key Vault and Entity Framework Core

As encryption is one of the main security concerns . Microsoft introduced Always Encrypted which is a feature designed to protect sensitive data on database level. Always encrypt is available on SQL server 2016 or later as well as Azure SQL Database.

Before starting using Always Encrypted you need to know that there is 2 types of encryption either “Deterministic” or “Randomized” the encryption algorism of “Randomized” is more complex and more secure than “Deterministic”. But the most important point here you must know that columns encrypted using “Randomized” mechanism cannot be used on searching, grouping, indexing, or joining. You need to only use such columns on select statements. While “Randomized” can be used normally on all database operations but only with equality comparison . You can not use it on like , greater than , less than , max , min or any other operators.

On this article we will apply always encrypt using SQL server on Azure , Visual studio 2019 , Azure Key Vault, SQL server management studio , Entity Framework core.

Creating Azure Key Vault

Login To your Azure Portal using portal.azure.com → If you don’t have a subscription please get a free one.

Create an Azure Key Vault using the below article from Microsoft.

Manage Key Vault in Azure Stack Hub using the portal - Azure Stack Hub

Once this is done please go inside the created key vault resource and create a key a new key using the below article from Microsoft.

Manage Key Vault in Azure Stack Hub using the portal - Azure Stack Hub

Please copy the key identifier and keep it on notepad as we are going to use it late (Please refer to the below screen).

Navigate to Key Vault access policy and then try to give your Azure Active directory account which you are going to use on logging to SQL Server Management Studio access on key vault on “Key management” for “Get” , “List” , “Decrypt” , “Encrypt” , “Unwrap key” , “Wrap key” , “Verify”.(Please check this article).

Apply Always Encrypted on database level

Go your SQL Server management studio login using your credentials and same Azure AD user has permission key vault then apply the below script to create database, table, Master key linked with Azure Key Vault & Encryption key as following:

https://raw.githubusercontent.com/khaldelshiekh/Always.Encrypted/main/Always.Encrypted.Web/Always_Encrypt.sql

Now it is time to Encrypt your sensitive data on our example we will encrypt column “Visa_No” on table “Client_Info” using the below steps:

Right click on your table and select Encrypt Columns from Menu

Try to Move next till it is done

Now try to test inserting any data on this table

Try to select the insert row you can see that Visa_NO already encrypted!

If you want to see data unencrypted please use the below additional connection parameter while connection to SQL Server

Column Encryption Setting=Enabled

Creating .Net Core MVC application with Entity Framework Core

Create Normal Visual Studio .Net Core Web Application 3.1

You need to select authentication then work or school Accounts as shown above this will register a new app on your Azure Active directory

Now you need to do two important steps Navigate to Azure Active directory Apps registration and create client secret (More info on this article). Then Navigate to Key Vault and provide the newly created Azure Active directory App using this article. You need to provide “Get” , “List” , “Decrypt” , “Encrypt” , “Unwrap key” , “Wrap key” , “Verify” on Keys object.

Add Entity Framework and Always Encrypted support

Install the below NuGet packages

It is time to create your EF Core Models from database considering always encrypt on connection string using the below Package manger console command

Scaffold-DbContext “Server=tcp:<you sql server instance name>.database.windows.net,1433;Initial Catalog=Always_Encrypted_DB;Persist Security Info=False;User ID=<database user>;Password=<database password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Column Encryption Setting=enabled;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

Please update your appsettings values above according to your environment variables

The most tricky part you have to do on such application is not override the constructor of your EF Core and authenticate your application to decrypt the values using Azure Key Vault using the above partial class [Get the full source code from here]. We have created a new partial class in order to avoid any code overrides when you updated your model from database at any point of time

I have created a small APP to insert and retrieve values from our database source code is available on GitHub and This will be finally how the data is shown on database and on your web system!

Please check the full source code on my GitHub respo