Always Encrypted In SQL Server 2016 - Step By Step Guide - Part Two

This is part two of "Always Encrypted In SQL Server 2016 - Step By Step Guide" series. In this article, we are going to see the sample queries on encrypted columns and limitations of the Always Encrypted feature. In case, if you have missed out the first part of the series, you could read the article from here.

1. Always Encrypted In SQL Server 2016 - Step By Step Guide - Part One

Sample Queries

 

Let's write a simple query to find out the employees whose last name is "Smith". When you execute this query, the expected output won't be displayed and instead, the below error message will appear.

 

 

You can't directly query the encrypted column like this once it is encrypted using the Always Encrypted feature. SQL Server expects us to send a parameterized object instead of a constant value. So, to fix this, declare a varchar variable and apply it to the select query as shown below. 

Declare @LastName Varchar(20) = 'Smith'

Select * From EmployeeDetails Where LastName = @LastName

You would still not see the expected output and below prompt would appear.

 

 

 

Oh! OK. It warns us for parameterization needs to be enabled. Right-click on the query window, select Query Options -> Encryption -> Advanced -> check the "Enable Parameterization for Always Encrypted" option. Alright, let's re-run the query and finally, you would see the expected output as shown below. You can turn it OFF anytime in future.

 

 

If you mouse-hover on the variable declaration statement, you would be seeing the below message. Parameterization is the feature of SSMS which will convert the declared local varibale into SqlParameter object for the query execution. Variable value will be encrypted before sending it to the database.
 

 

Now, let's see about copying the data to a temp table or table variable. Once the columns are encrypted, you can't copy the table data to a regular table variable and doing so, you would be prompted with the below message.

 

 

Ok, then let's see whether we can copy the data to the temp table. When you try to copy the data to the temp table, you will be prompted with a message saying the column encryption key does not exist or you do not have permissions since the keys were created on EmployeeDB.

 

 

Let's script the CMK and CEK from EmployeeDB and run it on the tempdb database. Still, you won't be able to see the expected output in SSMS and instead, below message will be displayed.

 

 

After researching the online forums, it is observed that as of now, we can't copy the data from encrypted table to a temp table in SSMS though there is a workaround posted saying that using SQL Bulk Insert option, we could achieve this in C#.NET. 

 

Limitations

 

It's time to see the limitations of encrypting the column using the deterministic or randomized encryption types. Both of them have their own limitations and I would like to highlight just a few of them.

• Can't perform inequality, greater/less than, arithmetic operations or LIKE queries on columns encrypted using the Deterministic type. This is a major drawback for our project where we have requirements to filter records using these type of queries.
• Can't perform any of these operations on columns encrypted using the Randomized type. In addition, you can't perform searching, grouping and join on the encrypted columns.
• Deterministic encryption supports indexing on encrypted column whereas it's not supported with Randomized type.

And the list goes big. You could refer the below-listed posts for the complete information. 

1. Always Encrypted 
2. Always Encrypted Limitations

Summary 

In this article, we have covered the basic queries to filter records, saw the restrictions on copying the data to a temp table and the limitations of the Always Encrypted feature. Thanks for reading the article. Please provide your feedback and comments.