Is ‘Always Encrypted’ SQL Server 2016’s most widely important new feature? It is significant that ‘Always Encrypted’ in SQL Server is in all editions of SQL Server. Because of the increasing importance of encryption to data governance, it allows encryption for the sensitive application data for everywhere beyond the application’s client connection, including network, server, database and storage. Robert Sheldon explains what it is, why you should try it out, and how to set about it.
Always Encrypted is a new feature included in SQL Server 2016 for encrypting column data at rest and in motion. This represents an important difference from the original column-level encryption, which is concerned only with data at rest. Always Encrypted also differs from Transparent Data Encryption (TDE), which is also limited to data at rest. In addition, TDE can be applied only to the database as a whole, not to individual columns.
With Always Encrypted, the client application handles the actual data encryption and decryption outside of the SQL Server environment. In this way, you can better control who can access the data in an unencrypted state, allowing you to enforce separation of roles and minimize the risks to sensitive data.
To be able to encrypt and decrypt the data, the application must use an Always Encrypted-enabled driver that interfaces with SQL Server 2016. It is this driver that carries out the actual encryption and decryption processes, rewriting the T-SQL queries as necessary, while keeping these operations transparent to the application.
To implement Always Encrypted on a column, you need to generate a column encryption key and a column master key. The column encryption key encrypts the column data, and the master key encrypts the column encryption key.
The database engine stores the column encryption key on the SQL Server instance where Always Encrypted is implemented. For the master key, the database engine stores only metadata that points to the key’s location. The actual master key is saved to a trusted external key store, such as the Windows certificate store. At no time does the database engine use or store either key in plain text.
You’ll get a better sense of how all this works as we go through the article’s examples, which walk you through the process of implementing Always Encrypted in the test database. The article focuses primarily on the SQL Server side of the equation, demonstrating how to create the two encryption keys and encrypt the columns.
Note that this is the third article in a series on SQL Server encryption. The first two cover basic column-level encryption and TDE. You can access the articles through the following links:
- Encrypting SQL Server: Using an Encryption Hierarchy to Protect Column Data
- Encrypting SQL Server: Transparent Data Encryption (TDE)
For information about enabling Always Encrypted in client applications and their drivers, refer to the MSDN article Always Encrypted (client development), which points you to details about several drivers, including .NET Framework Data Provider for SQL Server, Microsoft JDBC Driver for SQL Server, and ODBC Driver for SQL Server.
Prepare for Always Encrypted
When SQL Server 2016 was first released, the Always Encrypted feature was available only to the Enterprise and Developer editions, but with the release of SQL Server 2016 Service Pack 1, Always Encrypted is now available to all editions.
There’s not much you need to do to prepare a database for enabling Always Encrypted, other than to be running an instance of SQL Server 2016, with SP1 installed if necessary. However, before you try to implement Always Encrypted, you should be aware of the many limitations that come with this feature.
To begin with, you cannot use Always Encrypted to protect columns configured with the following data types:
- XML
- ROWVERSIONTIMESTAMP
- IMAGE
- TEXTNTEXT
- SQL_VARIANT
- HIERARCHYID
- GEOGRAPHY
- GEOMETRY
Always Encrypted also comes with a number of other restrictions. For example, you cannot encrypt columns that use aliases or user-defined data types or are configured with default constraints or the ROWGUID property. And you’ll find other limitations as well. A good place to start for understanding when you can and cannot use Always Encrypted is with the MSDN article Always Encrypted (Database Engine).
For the examples in this article I created a database that contains one table and populated the table with data from the AdventureWorks2014 database (installed on a local instance of SQL Server 2016). To create these objects, I used the following T-SQL script:
That said, you do need to be working in SQL Server Management Studio (SSMS) to follow along. The reason we’re using SSMS is because of additional limitations with Always Encrypted. To carry out all the tasks that go with enabling Always Encrypted, we must use either the SSMS interface or PowerShell. We can carry out some of these tasks with T-SQL alone, but not all of them, and even SQL Server Data Tools (SSDT) can’t help us here.
For this reason, the article focuses primarily on how to use the SSMS interface to implement Always Encrypted, with a smattering of T-SQL thrown in for good measure. We’ll leave PowerShell for a different article. Note that Microsoft strongly recommends you configure Always Encrypted on a computer separate from where the database resides to prevent the keys from leaking to the server environment. For testing purposes, however, we can use a single machine, as I’ve done for these examples.
Run the Always Encrypted wizard
The simplest way to implement Always Encrypted is to run the SSMS Always Encrypted wizard, which steps you through the process of applying encryption to one or more existing columns within a database’s tables. To launch the wizard, right-click the database in Object Explorer, point to Tasks, and then click Encrypt Columns. By default, when you first launch the wizard, it displays the Introduction page, as shown in the following figure.