Hans Michiels

How to mimic a wildcard search on Always Encrypted columns with Entity Framework

March 22, 2017 by


The title of this post should have been “How to implement wildcard search functionality with Always Encrypted, make deterministic encryption safer, and load initial data using SqlBulkInsert”, but as you understand, that’s just too long for a title.

A number of years ago I built a web application with “Always Encrypted” as VARBINARY columns, before Microsoft offered this feature out of the SQL Server Box. So in case the database server would be compromised by hackers, no client details could be revealed.

Just like Microsoft my Data Access Layer, built in .NET did the encryption and decryption, transparent for other code that accessed it. And I had the same challenge how to search on encrypted columns.

So with (maybe more than average) interest I was looking forward to know how the Always Encrypted feature of SQL Server 2016 works.

And I noticed that Microsoft implements the searching differently from what I did at the time. What I did different (using randomized encryption and search columns) was my inspiration for this article.

But first a little introduction on the feature itself.

Always Encrypted is a client-side encryption technology in which data is automatically encrypted not only when it is written but also when it is read by an approved application. It’s an end-to-end encryption, therefore SQL server only sees (and stores) the encrypted version of the data.

This means that your client application needs to use an Always Encrypted enabled driver to communicate with the database.

At this time, the available Always Encrypted enabled-drivers are:

  • the .NET Framework Data Provider for SQL Server, which requires installation of .NET Framework version 4.6 on the client computer
  • the JDBC 6.0 driver
  • the Windows ODBC driver

For more information and to download the drivers see Always Encrypted client development (on MSDN).

To be honest I hate to replicate MSDN or other sites or blog posts when I do not have to. So as an introduction I kindly refer to:

How to mimic a wildcard search on Always Encrypted columns with Entity Framework

The challenges with searching on a column that is Always Encrypted are twofold:

  • You can only search on an exact value, you cannot do a wildcard search.
  • For the exact search you have to do a sacrifice in the area of security: you have to use deterministic encryption, which always produces the same encrypted value for a given input value. When the number of distinct values in a column is low (for instance true/false or a domain value with a limited number of values), you can imagine this can be dangerous: you could guess values by comparing the same encrypted value of other rows. Also brute force to encrypt all possible values so you can compare the encrypted values with the ones in the database is a possible threat. So you do not really want that.

Coping with these limitations is not ‘easy’. For instance to implement wildcard search functionality you have to to find out how the business users would like to search.

So if you have encrypted credit card numbers in your database, and the business users want to be able to search on the last 4 digits, you have to “do something” for that.

If the business users want to be able to search on expiration month and year of a credit card, but a security requirement is to avoid deterministic encryption because of the limited number of distinct values for those two columns (only 12 month numbers, maybe only 5 to 10 years that are still relevant), the same counts: you have to “do something” for that.

But what is “something”? Okay, to be straight with you, you will need an extra search column to implement each of these search requirements.

Follow the demo and I can show you how this works.

Preparing a database for the demo

First here are my preparation scripts to set up a demo using a CreditCard table.

If you have read the other resources mentioned above you should be able to understand what these scripts do. Also there is some comment in the scripts.




Creating a .NET Console Application with Entity Framework Core and an Entity Model.

Alright, the encryption keys and database tables are created, now we have to move over to .NET to populate the [CreditCard] table with rows of which some columns are encrypted.

After that, I’ll show you how you can mimic wildcard searches.

For the demo I created a Console Application using Visual Studio 2015 (Visual Studio 2017 seemed unstable during building the solution, so I moved back to VS2015 for now).

In this little demo program the following things will be demonstrated:

  1. How to populate a table that has Always Encrypted columns from an old or staging table, using SqlBulkCopy (very fast).
  2. How to use RANDOMIZED encryption for your columns while still be able to a ‘like’ search using a different ‘search’ column with DETERMINISTIC encryption.
  3. How to make the DETERMINISTIC encryption of the search columns even safer by adding a random part to it.

This is done by the example of a [CreditCard] table.

When not installed yet, download and install the Microsoft .NET Framework 4.6.2.

Then open Visual Studio and create a new Console Application. Make sure to set the .NET Framework to 4.6.1.

Creating a new project for a Console Application

Now Entity Framework has to be installed. For this start the menu option Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution.

The “Manage NuGet Packages for Solution” menu-option ..

I have used the Entity Framework Core v1.1.1 which uses the .NET Framework 4.6.

To install Entity Framework Core v1.1.1 in the Browse tab search on entity framework core and select Microsoft.EntityFrameworkCore from the list.

Initial steps to install EntityFramework Core v1.1.1

Then select the project name on the right and press the Install button.

After reviewing the changes and accepting the license Entity Framework Core will be installed. Installation takes some time, the output window will show when it is finished.

The Output Window ..

To use Entity Framework, we have to add a model. Right-click on the project node to add a new item, then follow the steps in the pictures below:

Initial steps to add a ADO.NET Entity Data Model

After this, your project should look as follows:

This was not too difficult, right?

Now modify the connection string in App.config, so the connection is prepared for Always Encrypted. To do so, add
;Column Encryption Setting=enabled
at the end of the connection string, as indicated in the pictures below.

Adding code to interact with the database

As you might have noticed the [CreditCard] table contains two search columns CardSearch1 and CardSearch2, of which the values are derived from other columns, namely CardNumber, ExpMonth and ExpYear. So logic needs to be implemented to calculate the value of those columns. So add a “CreditCardSearchColumns.cs” class/file to the project. Replace the initial code in it with the following code:

This leads to a problem immediately, because both properties CardSearch1 and CardSearch2 are already in a class generated by the installed T4 template AlwaysEncryptedModel.tt.

Compile error for CardSearch1 (and CardSearch2)

This can be solved by deleting the two properties from CreditCard.cs:

A more elegant solution would be to adjust the AlwaysEncryptedModel.tt T4 template so that it excludes those properties when generating this file, but doing that is beyond the scope of this article.

We also need code to do the initial load of encrypted values. As you might have noticed, there is also a CreditCardTemp table in the database, that was loaded from the AdventureWorks database. This table does not contain any encrypted columns. We have to copy all rows from this table to the CreditCard table. I do not use Entity Framework for this, but the .NET Framework 4.6 Data Provider for SQL Server directly, so I can use SqlBulkInsert, which is really fast.

What needs to be done is add a class InitialLoad to the EFCoreAlwaysEncryptedDemo project, the InitialLoad.cs file has the following contents:

Let’s see, where are we .. we are getting near the end, just one class to add and some code to Program.cs.
First add a class DemoPlease to the project, add it to the file DemoPlease.cs

Paste this code into the file (replace existing code):

Now it is time to glue everything together! To do this, paste this code into Program.cs (replace all existing code):

Now it’s time to press <F5> (or the Start Debugging from the Debug Menu)! Watch how all the methods from DemoPlease work.

Initial encryption ..

Add a credit card, retrieve it using the credit card number, update it, retrieve it by the expiration month and year

Delete confirmed ..

And finally retrieving multiple credit cards for the same expiration month and year ..

Conclusion / Wrap up

In this blog post I shared my insights on how to cope with the limitations of searching on columns that were encrypted using SQL Server’s new feature Always Encrypted, by introducing extra Search columns, so that wildcard searches can be simulated and security is not weakened so much by the less safe deterministic encryption. To demonstrate this I used the Entity Framework Core v1.1.1 that uses the .NET Framework 4.6 Data Provider for SQL Server. This driver can be used to interact with Always Encrypted columns.

About best practices .. please forgive me that I took some shortcuts, which enabled me to focus on the main subject. Things to remember when building production software:

  • Follow best practices for Always Encrypted when implementing it. For instance, your application should not run on the same server as the SQL Server Database Engine.
  • Do not hardcode a connection string in an executable program.

Hans Michiels

About Hans Michiels

Hans is an Independent Business Intelligence and Data warehouse Consultant, working in the Netherlands. He works in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on data warehouse and business intelligence projects using Microsoft technology, preferably a Data Vault and Kimball architecture. He has a special interest in Data warehouse Automation and Metadata driven solutions. * Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0) * Certified in MS SQL Server: MCSA (Microsoft Certified Solutions Associate) SQL Server 2012, - MCITP Business Intelligence Developer 2005/2008, MCITP Database Developer 2005/2008, MCITP Database Administrator 2005/2008 His web site and blog is at www.hansmichiels.com, where you can find other contact and social media details. View all posts by Hans Michiels