Support Always encryption on Azure Sql DB by storing key in Azure Key Vault

AMIT VERMA 1 Reputation point
2021-02-04T12:26:36.477+00:00

Hi All,
We have a requirement for adding "Always encryption" for certain columns in Aure sql db. For that We using Azure key vault for storing column master key.
We have created the Azure Key Vault service and generated the keys there. Also created an App registration in Azure Active Directory for access the data with the java client.

We have written the following code for access data :

String clientID = "xxxx";
String clientKey = "xxxx";
SQLServerColumnEncryptionAzureKeyVaultProvider akvProvider = new SQLServerColumnEncryptionAzureKeyVaultProvider(clientID, clientKey);
Map<String, SQLServerColumnEncryptionKeyStoreProvider> keyStoreMap = new HashMap<String, SQLServerColumnEncryptionKeyStoreProvider>();
keyStoreMap.put(akvProvider.getName(), akvProvider);
SQLServerConnection.registerColumnEncryptionKeyStoreProviders(keyStoreMap);

String connectionUrl = xxxx ;user=*****;password=****;columnEncryptionSetting=Enabled;";
try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
     PreparedStatement insertStatement = sourceConnection.prepareStatement("INSERT INTO [dbo].[Patients] VALUES (?, ?, ?, ?)")) {
  insertStatement.setString(1, "795-73-9838");
  insertStatement.setString(2, "Catherine");
  insertStatement.setString(3, "Abel");
  insertStatement.setDate(4, Date.valueOf("1996-09-10"));
  insertStatement.executeUpdate();
  System.out.println("1 record inserted.\n");
}
catch (SQLException e) {
  e.printStackTrace();
}

When We run this code. I got the following error

com.microsoft.sqlserver.jdbc.SQLServerException: Failed to decrypt a column encryption key. Invalid key store provider name: xyz. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key provider names are: . Valid (currently registered) custom key store provider names are: [AZURE_KEY_VAULT]. 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.

Azure Key Vault
Azure Key Vault
An Azure service that is used to manage and protect cryptographic keys and other secrets used by cloud apps and services.
1,141 questions
Azure SQL Database
Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
6,984 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,767 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-02-05T12:59:16.063+00:00

    @AMIT VERMA Looks like an incorrect permissions configuration to me.
    On your KeyVault, please check if the Application has "Get", "Decrypt", "Sign" and "UnwrapKey" permissions on the Keys

    64538-image.png

    Do let us know if you have any further questions.

    ----------

    If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.