Azure Managed Service Identity in C# to connect to Azure SQL Server

Question

I'm running one Microsoft doc tutorial on how to set up MSI access to Azure SQL. This article: https://docs.microsoft.com/en-gb/azure/app-service/app-service-web-tutorial-connect-msi

I succesfully get the connection string from my Azure web config manager

public MyDatabaseContext(SqlConnection conn) : base(conn, true)
{
  conn.ConnectionString = WebConfigurationManager.ConnectionStrings["dbConnectionName"].ConnectionString;

  // DataSource != LocalDB means app is running in Azure with the SQLDB connection string you configured
  if (conn.DataSource != "(localdb)\\MSSQLLocalDB")
            conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

  Database.SetInitializer<MyDatabaseContext>(null);
}

Which I use in my controller using

private MyDatabaseContext db = new MyDatabaseContext(new SqlConnection());

When I finally run a call e.g.:

var sample = (from c in _context.Customer where c.Abbreviation == abbrev.Trim() select c).FirstOrDefault();

I get an error System.Data.Entity.Core.EntityException: 'The underlying provider failed on Open.' "SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

This happens even if I do (https://docs.microsoft.com/en-gb/azure/app-service/app-service-managed-service-identity#obtaining-tokens-for-azure-resources)

using Microsoft.Azure.Services.AppAuthentication;
using Microsoft.Azure.KeyVault;
// ...
var azureServiceTokenProvider = new AzureServiceTokenProvider();
string accessToken = await 
azureServiceTokenProvider.GetAccessTokenAsync("https://vault.azure.net");
// OR
var kv = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));

And populate a proper access token in my SqlConnection.

Any help would be appreciated

I assume you are trying to connect from the app service. Have you ensured the app service plan has MSI enabled, then, have you created an Azure AD Group, added the MSI to it and then granted access to the group in SQL? Is the C# .net core? — Murray Foxcroft
Can you connect from your dev workstation with an AccessToken? If you're logged in locally using an AD identity mapped to an AAD user with access to the database, then AzureServiceTokenProvider should give you a valid token. — David Browne - Microsoft

Simon Bourdeau Simon Bourdeau · Accepted Answer · 2018-10-15T16:09:44

For anyone interested the problem was a delay I think.

Once I ran;

az webapp identity poweshell command

and added a connection string;

az webapp config connection-string set

it worked but it took some time

Azure Managed Service Identity in C# to connect to Azure SQL Server

1 Answers