Feature support request: SQL Server and Azure SQL Database "Always Encrypted" support

fritz_lowrey

All,
We have started working with the "Always Encrypted" (AE) feature of SQL Server, which allows data to be stored encrypted using a key set that can be retrieved via the client (.Net 4.6.1 and above) and thus permits the encryption to be "transparent" to the client application. This takes some doing, but is very clean once established.

The problem, from a LinqPad perspective, is that using a non-AE-aware connection to the DB sees all encrypted fields as bytearrays... actually, somewhat confusingly, they are byte arrays that are cast to their declared data type (e.g. "nchar(max)" or whatever). This causes any query on a table with an AE field to throw an exception.

The solution for LinqPad would be to prompt for the AE credential data (certificate, KeyVault details, etc.) so that it can set up the ADO.Net connection correctly. It'd be .Net 4.6 and Sql Server 2014(+) specific, but would prevent me from suffering with SSMS.

Thanks,
Fritz

JoeAlbahari

Does it fix the problem if you right-click the connection, click Advanced, and enter the following into the additional connection string parameters:

Column Encryption Setting=Enabled

Or is there more to it than that?

In terms of prompting for credential data, I can't find documentation on how to implement this. It seems that the user is supposed to install certificates on their local machine; is there more to it than this?

fritz_lowrey

We are using the "store certificates in a key vault" solution to this, not storing the certificate locally to the machine. Thus our application has key vault id and credentials that are required to setup in order to pull the CEK from the vault. Storing certs locally isn't an option for our environment.

https://azure.microsoft.com/en-us/documentation/articles/sql-database-always-encrypted-azure-key-vault/

Thanks for the tip on the connection string addition I'll give that a try but I don't think that it's the whole answer.

F

fritz_lowrey

An update to this:
I am using LinqPad 5 to access an Azure SQL Database with Always Encrypted set for several columns; the Column Master Key (CMK) is stored in a Key Vault, I have set the appropriate connection string values in the connection UI:

Column Encryption Setting=Enabled

The .Net SQL client 4.6.x client should "see" that the metadata for the table indicates the encrypted setting and the name of the key vault from which the CMK can be retrieved.

When I query an encrypted table I get the following result:

Failed to decrypt column 'EmailAddress'.
Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.

This indicates that LinqPad does not have a key vault provider configured. The URL I provided in my last email provides the code snippet required to register a KV provider with the driver:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault

private static ClientCredential _clientCredential;

static void InitializeAzureKeyVaultProvider()
{
   _clientCredential = new ClientCredential(clientId, clientSecret);

   SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
      new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

   Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers =
      new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

   providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
   SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
}

Any input on whether LinqPad will allow me to support data with the Always Encrypted w/ Key Vault CMK?

Fritz

geluvac

A little late to the party, but still posting my article with .Net Core updates: https://www.codeproject.com/Articles/5355073/Full-Tutorial-on-using-Always-Encrypted-with-Azure