azure - .Net Core 5.0 - Sql Azure + 始终加密 + 托管身份

Question

我有一个带有加密列的 Azure SQL 数据库(始终使用 Azure KeyVault 加密)。我可以从 SSMS 访问此数据库，并且可以看到解密的数据。

我还有一个使用 .Net Core 5.0 制作的 Web 应用程序，已部署到 Azure 应用服务。应用服务已打开托管标识，并且具有该 SQL 数据库的 enc/dec key 的 Key Vault 具有访问策略设置，以允许此应用服务解密数据。

该网络应用程序使用托管身份，因为我可以看到检索未加密的数据没有任何问题。

此外，连接字符串确实包含Column Encryption Setting=enabled;。这是连接字符串:

Server=tcp:server.database.windows.net,1433;Database=somedb;Column Encryption Setting=enabled;

问题是我找不到任何具有这种设置的示例。我发现了一些，并且我知道我需要注册 SqlColumnEncryptionAzureKeyVaultProvider。这是我获取 SqlConnection 的代码:

    internal static class AzureSqlConnection
    {
        private static bool _isInitialized;

        private static void InitKeyVaultProvider(ILogger logger)
        {
            /*
             * from here - https://github.com/dotnet/SqlClient/blob/master/release-notes/add-ons/AzureKeyVaultProvider/1.2/1.2.0.md
             *      and  - https://github.com/dotnet/SqlClient/blob/master/doc/samples/AzureKeyVaultProviderExample.cs
             *
             */

            try
            {
                // Initialize AKV provider
                SqlColumnEncryptionAzureKeyVaultProvider sqlColumnEncryptionAzureKeyVaultProvider =
                    new SqlColumnEncryptionAzureKeyVaultProvider(AzureActiveDirectoryAuthenticationCallback);

                // Register AKV provider
                SqlConnection.RegisterColumnEncryptionKeyStoreProviders(
                    new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>(1, StringComparer.OrdinalIgnoreCase)
                    {
                        {SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, sqlColumnEncryptionAzureKeyVaultProvider}
                    });

                _isInitialized = true;
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Could not register SqlColumnEncryptionAzureKeyVaultProvider");
                throw;
            }
        }

        internal static async Task<SqlConnection> GetSqlConnection(string connectionString, ILogger logger)
        {
            if (!_isInitialized) InitKeyVaultProvider(logger);

            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                /*
                         * This is Managed Identity (not Always Encrypted)
                         *  https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#modify-aspnet-core
                         *
                         */
#if !DEBUG
                conn.AccessToken = await new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/");
                logger.LogInformation($"token: {conn.AccessToken}");
#endif
                await conn.OpenAsync();
                return conn;
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Could not establish a connection to SQL Server");
                throw;
            }
        }

        private static async Task<string> AzureActiveDirectoryAuthenticationCallback(string authority, string resource, string scope)
        {
            return await new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/");

            //AuthenticationContext? authContext = new AuthenticationContext(authority);
            //ClientCredential clientCred = new ClientCredential(s_clientId, s_clientSecret);
            //AuthenticationResult result = await authContext.AcquireTokenAsync(resource, clientCred);
            //if (result == null)
            //{
            //    throw new InvalidOperationException($"Failed to retrieve an access token for {resource}");
            //}

            //return result.AccessToken;
        }
    }

此代码不会引发任何异常，并且适用于非加密查询。但对于加密查询，我收到以下错误:

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. 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.

key 保管库提供商似乎未注册。

我应该怎么做才能查询加密数据？

使用的包

    <PackageReference Include="Microsoft.Azure.Services.AppAuthentication" Version="1.6.0" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.0" />
    <PackageReference Include="Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider" Version="1.2.0" />
    <PackageReference Include="Microsoft.Extensions.Hosting" Version="5.0.0" />

Answer 1

我能够使用此代码，该代码向 SqlColumnEncryption 提供程序提供 TokenCredential。当部署为应用服务时，DefaultAzureCredential 返回托管标识:

            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new DefaultAzureCredential());
            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>
            {
                { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider }
            };
            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);

从startup.Configure方法中调用它。