How to get started with Always Encrypted for Beginners Part 3: One Two Punch

A few weeks ago, I wrote Part 1 and 2 of this series, which was a beginner’s guide to Always Encrypted. In part 3, I am going to go over what road blocks I ran into when trying to implement this solution for a client. The goal of the project was to prevent the DBA from being able to view salary information, while still allowing the application to function without issue.

We were able to encrypt the data easily, but the entire process was not without issues. We had to remove default constraint values to get it to implemented. Always Encrypted does not currently support constraints. Fortunately, the defaulted zero value they had in place was easily fixed and we were off and running. Select statements from SSMS were returning encrypted values, and the application was able to read the encrypted values and returned the real values.

The real challenges started when the client began to test their application code. The first thing we hit was triggers.

The table had several insert triggers associated with the columns that were now encrypted. Since the data was now encrypted the insert triggers would fail. Again, we lucked out and they were able to recode somethings in order to remove the triggers. Of course, since troubles always come in threes, this was no different. First the constraint problem, then the triggers, then we hit the biggest road block that halted our Always Encrypted implementation.

SELECT SUM(Salary) as TotalSalary from Table

Always encrypted does not currently support aggregations. Unfortunately, this is salary data and the code is full of aggregations. There was no way around, and it was not feasible at this point to move the aggregations to the application side. Sadly, we had to abandon this solution and purpose another route. The good news is that this issue will be fixed in the next version of SQL Server. You can learn about secure enclaves technology here.

In researching different possible roadblocks of Always Encrypted I came across as great blog post by Aaron Bertrand (B|T) where he lists several gotcha’s, if you are looking to implement Always Encrypted, I highly recommend starting by reading his post to discover what kind of issues you may run into.

Additionally, here is the official list directly from MSDN of what Is not supported.

Always Encrypted is not supported for the columns with the below characteristics (for example, the Encrypted WITH clause cannot be used in CREATE TABLE/ALTER TABLE for a column, if any of the following conditions apply to the column)

  • Columns using one of the following datatypes: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types.
  • FILESTREAM columns
  • Columns with the IDENTITY property
  • Columns with ROWGUIDCOL property
  • String (varchar, char, etc.) columns with non-bin2 collations
  • Columns that are keys for nonclustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
  • Columns that are keys for clustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
  • Columns that are keys for fulltext indices containing encrypted columns both randomized and deterministic
  • Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted)
  • Sparse column set
  • Columns that are referenced by statistics
  • Columns using alias type
  • Partitioning columns
  • Columns with default constraints
  • Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported)
  • Primary key columns when using randomized encryption (deterministic encryption is supported)
  • Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms
  • Columns referenced by check constraints
  • Columns in tables that use change data capture
  • Primary key columns on tables that have change tracking
  • Columns that are masked (using Dynamic Data Masking)
  • Columns in Stretch Database tables. (Tables with columns encrypted with Always Encrypted can be enabled for Stretch.)
  • Columns in external (PolyBase) tables (note: using 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:

  • FOR JSON PATH
  • FOR XML

Although I did take a few punches with this implementation I think Always Encrypted is a great choice to keep your data secure, and I will definitely use it in the future. I am looking forward to seeing how this option improves in the next versions of SQL Server.

****Securing your environment is important no matter what option you implement. Learn more about database security. Register for the Denny Cherry and Associates webcast on Friday January 19th, 2018 where we talk about Database Security including Spectre and Meltdown.****

It’s All in the Name, Index Naming Conventions

Awhile back, if you are on Twitter, you can probably recall my ranting about the 949 indexes I was reviewing. The process was to determine duplicate indexes and consolidate them or discard the unneeded ones. My ranting was not about the duplicates per se it was about the index names. It only takes a second to name an object with some name that tells what the thing is. Below I will show you some examples and give you an easy script that will help you generate your index names. Taking a little time to name things appropriately can go a long way, it can not only be time saving but can help to reduce redundancy.

The DONT’s

As you can see from above, none of the names gave a complete indication of what the index encompassed. The first one did indicate it was a Non Clustered Index so that was good, but includes the date which to me is not needed. At least I knew it was not a Clustered Index. The second index did note it is a “Covering Index” which gave some indication that many columns could be included I also know it was created with the Data Tuning Advisor due to the dta prefix. The third index was also created with dta but it was left with the default dta naming convention. Like the first one I know the date it was created but instead of the word Cover2, I know there are 16 key columns are noted by the “K#” and regular numbers tell me those are included columns. However, I still have no idea exactly what these numbers denote without looking deeper into the index. The last index is closer to what I like however the name only tells me one column name when in fact it encompasses five key columns and two included columns.

The DO’s

Above we see a few good examples with varying naming conventions, but each tell me a much more than what we saw in the “Donts” list. The first one I know right away is a non-clustered index with two fields. The second is a clustered index with one field. The third is an index that has 9 fields, probably a covering index of some sort, which tells me that it is probably important to a specific query or procedure. Index four uses the name of the table and the field, which does give me more information but given the name of indexes are limited to 128 characters I prefer to leave that out. The last one closer to one of my favorites, because it does give more information. The name lets us know that it has an included column of Birthdate.

The Script

Here is the script I use when creating indexes. It will go thru and identify a missing index and create Index statement using a standard name convention.
NOTE: This modified version of what we use at DCAC is for just showing you how I include and create a standard statement in my code, this is not to be used to identify missing indexes, as it is not the purpose of my post. I have removed pieces of that from this script.

Create Statement Output

This statement gives the proper database context and create statement syntax, it adds all the needed key columns within the () and separated by commas. In addition, it adds the word INCLUDE and encompasses the included columns also in () and comma separated. Note the index name only includes the Key columns, which is just my preference.

Summary

Now everyone has their own naming conventions. You do you, however should stay consistent and give some meaning to it. When others look at the objects we should be able to know what it’s doing or be given a good clue as to what it’s for. This not only helps to quickly identity its definition but also keep you from creating duplicates. By looking at names you can tell with columns you need are already included in other indexes. Naturally you can’t just trust the name you have to dig deeper while examining your indexes but it at least will give you a realistic starting point.