Always Encrypted Column with Azure Function App & Azure Key Vault-CodePudding

I've created and published (to Azure) a working and fairly simple Azure Function App (HTTP Trigger) which inserts data retrieved from an API call to an Azure SQL database after authenticating via User identity (if testing locally) or Managed Identity (when running on VM).

Everything is functional, however, I now need to encrypt one of the SQL table columns which I have done using SSMS. The next step as I understand it is authenticating a provider to access the CMK via Azure Key Vault (I'm following this Microsoft guide).

I'm wondering in the following code how to InitializeAzureKeyVaultProvider without using applicationId and clientKey from Azure App registration resource, but with a user or managed identity role. Or, if there's any other way to get/use applicationId, and clientKey without creating/using an Azure App registration resource.

Is there a newer / easier way to access Azure Key Vault for Always Encrypted Columns sql queries?

static void InitializeAzureKeyVaultProvider() {
            _clientCredential = new ClientCredential(applicationId, clientKey);

            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

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

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

Here is the other way I've been attempting, however, upon installing and using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider, I get the error following this code sample:

        private static bool EncryptionProviderInitialized = false;

        private static void InitializeAzureKeyVaultProvider()
        {
            if (!EncryptionProviderInitialized)
            {
                SqlColumnEncryptionAzureKeyVaultProvider akvProvider = null;
#if DEBUG
                if (Debugger.IsAttached)
                {
                    Console.WriteLine("Debugger attached - configuring KeyVaultProvider via VisualStudioCredential");
                    akvProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new VisualStudioCredential());
                }
                if (akvProvider == null)
#endif
                    akvProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new ManagedIdentityCredential());
                SqlConnection.RegisterColumnEncryptionKeyStoreProviders(customProviders: new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>(capacity: 1, comparer: StringComparer.OrdinalIgnoreCase)
                    {
                        { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, akvProvider}
                    });
                EncryptionProviderInitialized = true;
            }
        }

ERROR:
[2021-10-08T01:36:24.209Z] Executed 'EncryptedSQLMigration' (Failed, Id=1323dcbb-e671-4ed4-8a7c-6259447326c5, Duration=537ms)
[2021-10-08T01:36:24.209Z] System.Private.CoreLib: Exception while executing function: EncryptedSQLMigration. FreshFirstApproach: Method not found: 'Microsoft.Extensions.Primitives.StringValues Microsoft.AspNetCore.Http.IQueryCollection.get_Item(System.String)'.

Initially, I got that same error, however, with Microsoft.Extensions.Logging.Abstractions - upon removing the ILogger from my main function just for the sake of moving on as I wasn't able to solve that issue either, I now get this Microsoft.Extensions exception.

Any help with my goal of using Always Encrypted Columns with Azure Function App and Azure Key Vault is very much appreciated!

Thank you very much.

CodePudding user response：

It is not possible to use applicationId and clientKey without creating or using an Azure App registration resource. There is an alternative way where you can pass clientId and clientSecret as shown below, but here also you will need application registration.

static void InitializeAzureKeyVaultProvider()
{
    string clientId = ConfigurationManager.AppSettings["AuthClientId"];
    string clientSecret = ConfigurationManager.AppSettings["AuthClientSecret"];
    _clientCredential = new ClientCredential(clientId, clientSecret);
    ....
    ....
}

As for user or managed identity, if you check this document you won't find the Azure Key Vault in the list of services that support managed resources.

So managed service identity should be created for Azure Function instead of Azure Key Vault. Check this Retrieve Azure Key Vault Secrets using Azure Functions and Managed Service Identity document for more information.