With the General Data Protection Regulation (GDPR) becoming a focus point for many organisations, data owners are investing in ways to better protect their customers' data. Microsoft has invested heavily in a range of new security capabilities in SQL Server 2016 such as Row-Level Security, Dynamic Data Masking and enhancements to Transparent Data Encryption (TDE). Over the course of two blog posts, we'll explore another new feature called Always Encrypted. In this blog post, we'll look at what it is, how it compares with other features, how it hangs together and why you would use it.

What is it?

Always Encrypted is arguably Microsoft’s flag ship security investment in SQL Server 2016. It provides column-level protection through a key management hierarchy, where the encryption and decryption of data is performed entirely by an Always Encrypted enabled driver on the client. Crucially, this ensures the encrypted data is protected end-to-end; from the client, back to the database server. It also means the protected data will never appear in plaintext on the database server! Let's think about that for a second… this is significant because no single feature within SQL Server could achieve this scope of protection before.

Looking at the table below, we can clearly see how Always Encrypted stands out from the rest.

*By installing SQL Server 2016 Service Pack 1, the Always Encrypted feature will be enabled in Standard, Web, Express and Local DB editions. This is great news for many who can now take further advantage of their investments in the Microsoft data platform. You can find out more about the changes introduced in Service Pack 1 by reading this Microsoft blog post.

Always Encrypted is also available in Azure SQL Database. This is Microsoft’s Platform-as-a-Service (PaaS) offering for SQL Server databases in Microsoft Azure, perfect for transactional workloads with structured data stores.

How it hangs together

While the encryption and decryption process is transparent to the application, Always Encrypted is not a feature that you simply switch on as some believe. It requires careful planning, schema modifications and application level changes.

Always Encrypted uses a Column Encryption Key (CEK) to encrypt the contents of the column using an encrypted value, which is stored with CEK metadata in the user database. A Column Master Key (CMK), is then needed to protect the CEK. This CMK can be stored in the Windows Certificate Store, Hardware Security Module (HSM) or Azure Key Vault. To prevent the exposure of keys in the event the user database is compromised, only the metadata associated with the CMK location and key store type are stored in the user database, the physical key is secured in the key store.

Always Encrypted also supports key rotation for organisational compliance and security purposes. To allow for CMK rotation, each CEK can have up to two encrypted values, each encrypted with a different CMK. The CEK can also be rotated but requires existing column data to be decrypted and then re-encrypted with the new CEK.

It’s important to remember that the database engine is not actually responsible for provisioning these keys. The benefit of Always Encrypted ensures the keys are never presented in plaintext on the database server. Microsoft recommends provisioning the keys on a separate computer with SQL Server Management Studio (SSMS) or PowerShell to mitigate the risk of an attacker retrieving the keys from memory on the database server.

It's beyond the scope of this blog post to discuss the creation and management of these keys with role separation, by that we mean the separation of tasks performed by a Security Administrator versus a DBA. You can find out more by reading this Microsoft article.

Once the keys have been provisioned, tables can be created or altered to allow sensitive columns to use the CEK for encryption and decryption purposes. At this point, we have two options for encrypting columns; deterministic and randomised. The former will generate the same encrypted value for a string of plain text time and time again, whilst the latter will produce a different encrypted value for the same plain text. Deterministic encryption is perfect for columns that are used for lookups in WHERE clauses or GROUP BY operations. However, since randomised encryption generates a different encrypted value for the same plain text, these columns cannot be used for point look ups and are therefore suited to sensitive comments or descriptive columns which won't be looked up against in queries.

What's the catch? Well, there are a few restrictions using Always Encrypted, which is why it's important to plan your implementation carefully. Non-BIN2 collations for columns using deterministic encryption, columns referenced by check constraints, columns with default constraints, FILESTREAM columns are just a few examples where Always Encrypted is not supported. For a detailed list of unsupported features, take a look here.

Once the keys have been provisioned and the schema modified to use the CEK, our attention now turns to the application layer. Microsoft has introduced a new connection string parameter called "Column Encryption Setting=Enabled". This ensures Always Encrypted functionality is enabled for all connections from the application to the database server. However, this might be overkill if your application doesn't query encrypted columns very often. Thankfully, Microsoft has exposed properties on the .NET SqlCommand object to enable Always Encrypted functionality at the individual query level. There are also enhancements for Always Encrypted in .NET Framework 4.6.2, which we'll go on to explore in part two.

To use this new parameter, you'll need to update the relevant provider on the client to use:

• .NET Framework 4.6 Data Provider for SQL Server
• ODBC Driver 13.1 for SQL Server
• Microsoft JDBC Driver 6.0

Why would I use it?

With the advent of Cloud and Hybrid architectures, there's a growing need to differentiate between individuals who manage the data and those that make use of the data. If you're an organisation that outsources the management of your data platform or wants to protect certain columns from being viewed by administrators or non-privileged users, Always Encrypted will do just that, whilst at the same time ensuring privileged data consumers can continue to work with the protected data.

What can we take away from all of this? Well, Always Encrypted is a great new feature that provides a level of functionality we haven't yet seen within SQL Server. However, applications and schemas should be designed around Always Encrypted. Whilst it's not impossible to integrate existing applications and schemas with Always Encrypted, the practicality of retrofitting a complex application and schema might mean it's too restrictive for some.

If you'd like to find out more, stay tuned for part two where we'll create a .NET solution that leverages Always Encrypted along with Azure Key Vault for key management.