I have a Azure SQL Db with encrypted columns (Always Encrypted with Azure KeyVault). I can access this db from SSMS and I can see the decrypted data.
I also have a web app made with .Net Core 5.0 which is deployed to Azure App Service. The app service has Managed Identity turned on and Key Vault that has enc/dec keys for that SQL Db has access policy setting to permit this app service to decrypt the data.
The web app works with managed identity as I can see that not encrypted data is retrieved without any issue.
Also, connection string does include Column Encryption Setting=enabled;. Here's the connection string:
Server=tcp:server.database.windows.net,1433;Database=somedb;Column Encryption Setting=enabled;
The problem is I can't find ANY samples with this kind of set up. I found some and I understand I need to register SqlColumnEncryptionAzureKeyVaultProvider. Here's my code to obtain 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;
}
}
This code does not throw any exceptions, and it works for non-encrypted queries. But for encrypted queries I get the following error:
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.
It seems like the key vault provider is not registered.
What should I do to make it work to query encrypted data?
<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" />
Turns out, it is impossible to read decrypted data in .NET 5 when MSI is used. There is a bug in MS packages and the app service is never authorized.
You have to use Service Principal. This works like a charm!
I have to thank to MS engineers that offered a working solution:
public static async Task<string> KeyVaultAuthenticationCallback(string authority, string resource, string scope)
{
return await Task.Run(() => new ManagedIdentityCredential().GetToken(new TokenRequestContext(new string [] {"https://vault.azure.net/.default"})).Token);
/********************** Alternatively, to use User Assigned Managed Identity ****************/
// var clientId = {clientId_of_UserAssigned_Identity};
// return await Task.Run(() => new ManagedIdentityCredential(clientId).GetToken(new TokenRequestContext(new string [] {"https://vault.azure.net/.default"})).Token);
}