Azure managed identity: Passwordless connection string to Azure SQL database connection error

Question

Microsoft Azure provides a secure, credential-less connection string to Azure SQL databases using its managed identity feature. This feature enables users to securely connect to their Azure SQL database without having to use passwords. It also allows them to store and manage their secrets in the key vault, which is a secure storage service provided by Microsoft. Additionally, it allows them to access third party APIs securely without having to worry about the security of their credentials.

This illustrates well the current context:

The problem is that when trying to access the API running locally, without the App Service, trying to access the SQL Server database that is hosted in Azure, the request returns an error while trying to authenticate to the database it seems while returning this error: SqlException: Login failed for user '<token-identified principal>'.

Also getting the error when trying to connect to the DB using SSMS:

I am wondering what steps I should take to get this to work locally before testing that it works when packaged in a container and deployed to App Service.

Here is my code in Program.cs:

var keyVaultEndpoint = builder.Configuration.GetSection("KeyVault").GetValue<string>("VaultURI");

if (!string.IsNullOrEmpty(keyVaultEndpoint))
{
    var azureServiceTokenProvider = new AzureServiceTokenProvider();
    var keyVaultClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
    builder.Configuration.AddAzureKeyVault(keyVaultEndpoint, keyVaultClient, new DefaultKeyVaultSecretManager());
}


// Get the migrations assembly name using reflection
var migrationsAssemblyName = typeof(Program).GetTypeInfo().Assembly.GetName().Name;

// Add the database context, in this case Sql Server + Entity Framework Core DbContext
builder.Services.AddSqlServerDatabase(builder.Configuration.GetConnectionString("AzureDbConnection"), migrationsAssemblyName);

Here is my appsettings.json config:

Answer 1

For managed idnetity follow the following steps:

• Enable managed idenity in your web app see the instructions here.
• In your Azure SQL allow Azure services to access the resource. See here
• The last step is to assign permissions against your identity in your SQL DB. Run the following commands:

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

Add appropriate roles as per your need. Here [<identity-name>] is the name of your managed identity.

You can find more detailed instructions here