Recently, my good friend and co-worker Aaron Bertrand and I were producing a video for the upcoming PASS Summit 2015 event. (Learn more about the international SQL Server professional association and its huge annual conference at http://sqlpass.org.) In this video, we covered a cool new feature coming out in SQL Server 2016 called "Always Encrypted."
Prior to SQL Server 2016, currently in CTP, your main method for encrypting a SQL Server application was to use a feature called Transparent Data Encryption. TDE provides strong encryption, but with some shortcomings. First, you have to encrypt an entire database. No granularity is offered at a lower level, such as encrypting specific tables or certain data within a table. Second, TDE encrypts only data at rest, in files. Data in memory or in-flight between the application and server are unencrypted.
Enter Always Encrypted. Always Encrypted allows very granular encryption, all the way down to individual columns. Always Encrypted also fully encrypts data at rest, in memory, and in-flight. Basically, Always Encrypted is just what it says. Your data is always encrypted, no matter what form it takes or where it resides.
Always Encrypted works by requiring a column master key (CMK) and a column encryption key (CEK). I like to think of the CMK as a means of grouping the CEKs, which specifically define which columns to encrypt, and associating them to a particular application or functionality. For example, you might have a big, important database containing medical data. You might create one CMK called Practitioners for the actual medical staff, another called Admin for reports, and a last one called Clerk for the data entry personnel. Then, you can assign a specific CEK to any columns in the tables of the database so that only designated CMKs can see the data. (You’ll also need to address CEKs in your client connection strings.)
When defining the keys, the AEAD_AES_256_CBC_HMAC_SHA_256 encryption algorithm is currently your only option. Furthermore, when defining the CEK, you may specify them as either deterministic or random. Deterministic is the setting you’d use for things such as indexed columns, constraints, foreign keys, and so forth. (BTW, the only collation available for deterministic encrypted columns is _BIN2.) Random is the setting you’d use for data with the highest security requirements, since random encrypted columns cannot be used in WHERE or JOIN clauses.
As a rule of thumb, you should think of CEKs as usable only on “vanilla” columns or in “vanilla” code. No special cases are available here. Identity columns, special data types (text, ntext, image, XML, hierarchy, temporal, geography, etc.) and computed columns are all disallowed. The same applies to code and feature sets such as full-text search, user-
?defined types, change data capture, replication, and so forth. Some features, including triggers and sparse columns, work normally as long as they do not reference the encrypted column, even indirectly using commands such as SELECT * FROM foo".
Get started building out your own encrypted SQL Server 2016 database by reading the blog entry here.
Watch a video about Always Encrypted here.
There’s also the full Books On-Line reference for Always Encrypted here.
Finally, if you want detailed information about the performance and overhead of Always Encrypted, check out Aaron Bertrand’s blog entries at http://sqlperformance.com/?s=always+encrypted.
Give it a try and let me know what you think.