New Enhancements in Always Encrypted

[Jakub Szymaszek]

First published on MSDN on Oct 28, 2015

The new version of SQL Server Management Studio (October 2015 Preview – build 13.0.700.242) and . NET Framework 4.6.1 RC bring several exciting enhancements to Always Encrypted. In the next few articles, we will cover these enhancements in details. For now, we will provide a brief summary of what is new.

Note: If you used Always Encrypted in CTP2 of SQL Server 2016, please note that in CTP3, the “DEFINITION” keyword has been removed from the DDL statement for creating column master keys. The related catalog views have been also impacted. Please refer to SQL Server 2016 Release Notes for details.

New Always Encrypted Wizard

Getting started with Always Encrypted is now easier than ever due to the new Always Encrypted wizard in SSMS that allows you to provision new keys and encrypt selected columns in an existing database in just a few easy steps. You can also use the wizard to change encryption settings, such as encryption type or a column encryption key, or to decrypt selected columns.

You can launch the wizard from multiple entry points:

• To encrypt columns located in multiple tables, right-click your database in Object Explorer and select Tasks>Encrypt Columns… .

• To encrypt columns located in one particular table, navigate to your table in Object Explorer, right click on it and select Encrypt Columns… .


• To encrypt one particular column, navigate to the column in Object Explorer, right click on the column and select Encrypt Column… .

For more details, please see SSMS Encryption Wizard - Enabling Always Encrypted in a Few Easy Steps.

Column Master Key Rotation Made Easy

You can now rotate a column master key using the UI.

1. Create a new column master key.

Using Object Explorer, navigate to Security/Always Encrypted Keys under your database, right-click the Column Master Keys folder and select New Column Master Key , to provision the new key and define it in the database.

2. Initiate the rotation.

In Object Explorer, right-click on your old column master key and select Rotate , select the new column master key and click OK . This will re-encrypt your column encryption keys with the new column master key.

3. Make your new column master key available to your client applications.

4. Clean up old column encryption key values. In Object Explorer, right-click on your old column master key and select Cleanup click OK . This will remove old values of column encryption keys encrypted with the old column master key.

For more details, please see Column Master Key Rotation and Cleanup .

Support for Azure Key Vault

The new column encryption wizard and key management dialogs support column master keys stored in Azure Key Vault.

In the previous article, Creating Custom Key Store Providers for Always Encrypted (Azure Key Vault Example) , we shared an example of a client application that uses column master keys stored in Azure Key Vault. The example included the code of the Azure Key Vault column master key store provider. We have now published the Azure Key Vault provider as a Nuget package at http://www.nuget.org/packages/Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvi... , to make app development easier.

Support for Hardware Security Modules

.NET Framework 4.6.1 includes two new column master key store provider classes, SqlColumnEncryptionCspProvider and SqlColumnEncryptionCngProvider , which enable encrypting and decrypting column encryption keys using Microsoft Cryptography API (CAPI) and Microsoft Cryptography: Next Generation (CNG) API. These two provider classes make it possible to use hardware security modules (HSMs) to store column master keys. To use an HSM-stored column master key, you need to:

1. Install .NET 4.6.1 on your client machines.


2. Install your CSP or CNG crypto provider from your HSM vendor on your client machines.


3. Create an RSA key in your HSM.


4. Define your column master key in the database (please, see CREATE COLUM MASTER KEY for details).

CREATE COLUMN MASTER KEY MyCMK
WITH (
KEY_STORE_PROVIDER_NAME = N'MSSQL_CSP_PROVIDER',
KEY_PATH = N'My HSM CSP Provider/AlwaysEncryptedKey'
);


Or:

CREATE COLUMN MASTER KEY MyCMK
WITH (
KEY_STORE_PROVIDER_NAME = N'MSSQL_CNG_STORE',
KEY_PATH = N'My HSM CNG Provider/AlwaysEncryptedKey'
);

When your app starts querying encrypted columns, SqlClient in ADO.NET 4.6.1 will automatically use the right column master key provider class to contact your HSM (via the CSP or CNG crypto provider) to encrypt/decrypt column encryption keys protected with the HSM-stored column master key.

Please note the new providers are not supported yet in SSMS – you cannot create or manage column master keys stored in HSMs using key management dialog in SSMS in the current version.

Conclusion

We are really excited about the new functionality. As you can see, it is all about the ease of use:

• The new UI in SSMS dramatically simplifies common tasks for Always Encrypted, to make it much easier to start using the feature.


• While it was possible to store column master keys in HSMs and in Azure Key Vault before, the set of new providers make this task easier than ever.

Please give the new capabilities a try, and let us know what you think!

Known issues

• On some displays, the new column encryption wizard (as well as other existing SSMS UI components) are not displayed properly with the default DPI scaling level value. A workaround is to set a lower scaling level in your display settings.