SQL Server 2016 Express SP1 – Always Encrypted

Developer Support

This post is provided by Senior App Dev Manager, Chris Tjoumas who provides an overview of Always Encrypted with SQL Server 2016.

Introduction

In this post, I want to focus on the Always Encrypted security feature of SQL Server 2016 SP1, specifically with the Express edition. This post is motivated by a customer who was hoping to understand a bit more about the different encryption options, specifically column-level encryption, and whether or not the data remains encrypted when exported or backed up.

Which Encryption Options are Available for SQL Server 2016 SP1 Express?

As shown in the list of security features, you can see that the Express edition supports Row-level security, Always Encrypted, Dynamic data masking, and Auditing (most of which require SP1). The question I usually get is “I heard that SQL Server 2016 supports column-level encryption, but I only see row-level”. The reason why it’s not seen at first glance is because the Always Encrypted feature is what provides the column-level security.

What is Always Encrypted?

Always Encrypted helps protect sensitive data, such as social security numbers, inside of a database (Azure SQL or SQL Server) by offering column-level encryption. Always Encrypted relies on the necessary keys to decrypt data to be stored client-side and these keys are never available to the Database Engine. This provides a separation between those who own the data and those who manage the data, ensuring that a database administrator who is not authorized to view the data cannot access the encrypted data.

How do you configure Always Encrypted?

You can configure Always Encrypted for individual columns in your database which contain sensitive data. Using the example above with the Social Security Number, lets assume you have a Patients database where info such as First Name, Last Name, and SSN are stored. To encrypt the SSN column, you would use the Always Encrypted Wizard to set the desired encryption configuration for this column (and any other column you choose). For details on this process, see this article.

Encrypted Backups in SQL Server 2016 Express, Explained

One of the points of confusion when looking over the security features of SQL Server 2016 Express may be that it shows that Express does not provide encryption for backups. Does this mean that if I have my data encrypted and I export the database, the data is no longer encrypted? The short answer is no – the data remains encrypted. What the “Encryption for backups” is talking about is the actual backup file; while the backup file is not encrypted, the data within the backup remains encrypted.

Digging a bit more into this, Encrypted Backups use a certificate or an asymmetric key along with an encryption algorithm to encrypt the backup file when a backup is performed. This only affects the backup file, not the actual data in the database. As it was explained above, Always Encrypted works by having the keys to decrypt on the client-side. This means that when the backup is performed, SQL Server doesn’t have access to the keys and, therefore, cannot decrypt the data.

Seeing the Encrypted Data in Action

What does the data look like when I retrieve it? Let’s first make sure we have Always Encrypted disabled:

• In your query window, right-click and select Connection > Change Connection…
• Click the Options >> button
• Select the Additional Connection Parameters tab
• In the textbox, enter Column Encryption Setting=Disabled

This doesn’t remove the encryption from the column we just encrypted, it simply tells the client (SQL Server Management Studio (SSMS), in this case) to not decrypt the data. So, what we will see is the encrypted SSNs when we run the following query:

What happened here? The SSN data was encrypted with Always Encrypted, running through the wizard as discussed earlier. When we tell the client that we are not decrypting the data, by setting Always Encrypted to disabled, we are asking for the binary (encrypted) values. A key point here when going back to the backing up of the database; I ran the export on this database, re-imported it to a new database and ran the same query above and got the same encrypted results, showing that the data in the backup remained encrypted throughout the backup/restore process.

Ok, but I want to see the actual values. As you probably guessed, we now want to enable Always Encrypted by setting additional properties of the connection options to Column Encryption Setting=Enabled. Once this is done, re-run the query and see the plaintext values:

You can also run queries targeting encrypted columns. For this to work, you need to ensure Always Encrypted is enabled, all keys are accessible (in this case, I’m using SSMS and my column master key was created as a certificate on the same machine), and you ensure Parameterization for Always Encrypted is enabled for the Query Editor window, which requires SSMS version 17.0. This will automatically convert Transact-SQL variables into query parameters, allowing the underlying .NET Framework Data Provider for SQL Server to detect data targeting encrypted columns, and to encrypt this data before sending it to the server. To enable this:

• Right-click on the query window and select Query Options…
• Select Execution > Advanced
• Select Enable Parameterization for Always Encrypted

Now, let’s run a query to find the name of the patient whose SSN is “555-55-5555”:

An important, which may trip you up: Always Encrypted supports only a limited subset of type conversions. So, when you create your Transact-SQL variable, it’s best to make this the same as the target column in the database. As an example, if by default your SSN column is set to nchar and you attempt to execute the above statement, it will not convert char to nchar and the query will fail. In this case, my SSN is of type char so I’m able to run this query.

Conclusion

As I set out to initially show that SQL Server 2016 Express SP1 did indeed support column-level encryption and retained that encryption during backups, the key management and separation of roles together help to show how powerful of a tool this is. The Express edition offers more than people may think at first glance, so it’s worth digging a bit deeper into this as I’ve started to do. There are many industries which require information to be kept safe, such as patient data, but also want to keep costs down by utilizing the Express version of SQL. My goal is to scratch the surface on what is offered and provide the information needed to show that these requirements can be met with this tool.

Premier Support for Developers provides strategic technology guidance, critical support coverage, and a range of essential services to help teams optimize development lifecycles and improve software quality.  Contact your Application Development Manager (ADM) or email us to learn more about what we can do for you.

Developer Support App Dev Customer Success Account Manager, Microsoft Developer Support

Follow