SQL Server Always Encrypted feature with KeyVault and MSI

[VenkataMadana]

Question

We are trying to use SQL Server Always Encrypted feature with KeyVault and MSI. Azure App Service MSI feature is used on the database on KeyVault. Please provide an example without keyvault ClientId and ClientSecret.

We have a working solution based on keyvault ClientId and ClientSecret, But we are checking a solution without using ClientId and ClientSecret.

SQLServerColumnEncryptionAzureKeyVaultProvider akvProvider = new SQLServerColumnEncryptionAzureKeyVaultProvider(alwaysOnEncyrptionClientId, alwaysOnEncyrptionClientSecret);
	    Map<String, SQLServerColumnEncryptionKeyStoreProvider> keyStoreMap = new HashMap<>();
	    keyStoreMap.put(akvProvider.getName(), akvProvider);
	    SQLServerConnection.registerColumnEncryptionKeyStoreProviders(keyStoreMap);

Relevant Issues and Pull Requests

[cheenamalhotra]

Hi @venkatareddym

In order to work with Always Encrypted feature, you can choose one of three methods:

• Windows Cert Store
• Java Key Store (JKS)
• Azure Key Vault (AKV)

Documentation Reference:
https://docs.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-2017#using-built-in-column-master-key-store-providers

[VenkataMadana]

We already using an Always Encrypted feature with Key Vault with ClientId and ClientSecret. We want to eliminate the ClientId and ClientSecret with the Azure app services MSI feature. For SQL server we already using Azure App services MSI feature.

Our main goal is to eliminate the secrets and passwords on config files. We are checking examples for SQLServerKeyVaultAuthenticationCallback with Keyvalut MSI ( Managed Service Identity )features.

https://github.com/microsoft/mssql-jdbc/blob/master/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerColumnEncryptionAzureKeyVaultProvider.java

[cheenamalhotra]

Hi @venkatareddym

Yes, that is not supported by driver yet. As of now MSI is only supported for acquiring access token for Azure database connection. I will keep the issue open until we support Azure Key Vault access with MSI in the driver.

[ronneyramon]

Hi,

@venkatareddym, you can get the Azure Key Vault access token using the AzureServiceTokenProvider (from Microsoft.Azure.Services.AppAuthentication 1.2.0 preview).

The GetToken method:

public static Task<string> GetToken(string authority, string resource, string scope)
{
	return (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://vault.azure.net");
}

SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

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

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

[manikandanramaswami]

@cheenamalhotra @venkatareddym @ronneyramon I too like this feature to be implemented in JDBC driver. This is analogous to IntegratedSecurity=true in a connection string which automatically initiates Windows Security based connection to SQL Server. Like wise, If a Connection String Keyword IntegratedKeyVaultSecurity=true (or any meaningful keyword) is introduced to wrap registration of KeyVaultProvider (GetToken callback) it would be of great help.

From JDBC driver version 7.4.1 onwards, AzureKeyVaultProvider is automatically registered if two Connection String Keywords present keyVaultProviderClientId, keyVaultProviderClientKey (#902) eliminating the need for writing the below code.
This is great usability improvement in terms of using JDBC under Cold Fusion environment.

SQLServerColumnEncryptionAzureKeyVaultProvider akvProvider = new SQLServerColumnEncryptionAzureKeyVaultProvider(alwaysOnEncyrptionClientId, alwaysOnEncyrptionClientSecret);
	    Map<String, SQLServerColumnEncryptionKeyStoreProvider> keyStoreMap = new HashMap<>();
	    keyStoreMap.put(akvProvider.getName(), akvProvider);
	    SQLServerConnection.registerColumnEncryptionKeyStoreProviders(keyStoreMap);

[lilgreenbird]

hi all, as of the latest 8.3.0 preview release the driver added support for authentication to AKV using Managed Identity. Here is a wiki on how to use this feature. Please give this a try and let us know if you have any questions.

I'll close this feature request issue now if any questions or problems please open a new issue. Thanks..