"Login failed for user ''." SqlException when accessing Azure SQL DB via MSI with a EF code-first implementation

[- Doctor Proctor -]

I am trying to get an Azure App Service application to utilize a managed service identity (MSI) and connect to an Azure SQL Database, but the .NET Framework 4.7 application fails at startup with the following error:

System.Data.SqlClient.SqlException: Login failed for user ''.

System.InvalidOperationException: This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection. ---> System.Data.SqlClient.SqlException: Login failed for user ''.


I had added the Microsoft.Azure.Services.AppAuthentication and Microsoft.IdentityModel.Clients.ActiveDirectory nuget packages.

I had enabled a system-assigned identity for the app service in the portal.

I set an Active Directory user group as the Active Directory admin for the Azure SQL server.

I executed a CREATE USER "<user here>" FROM EXTERNAL PROVIDER command on the database. There is a chance I may have forgotten to add/modify the roles for the database user (MSI). However, I wouldn't expect that error if that was the case.

The application's DbContext has two constructors, one of which follows:

public ApplicationDbContext(SqlConnection sqlConnection) : base(sqlConnection, true)
{
var isRunningLocal = string.IsNullOrEmpty(Environment.GetEnvironmentVariable("WEBSITE_SITE_NAME"));

sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings[nameof(ApplicationDbContext)].ConnectionString;

if (!isRunningLocal)
{
sqlConnection.AccessToken = new AzureServiceTokenProvider("RunAs=App").GetAccessTokenAsync("https://database.windows.net/").Result;
}

Database.SetInitializer<ApplicationDbContext>(null);
//Database.SetInitializer(new MigrateDatabaseToLatestVersion<ApplicationDbContext, Migrations.Configuration>());
}

Note there are two Database.SetInitializer() calls, one with a null argument and the other with a MigrateDatabaseToLatestVersion argument and commented-out. The application runs when using the former option. If I comment-out the former and uncomment the latter, the exception is raised at application startup.

Why does the exception description contain an empty ('') user? I suspect that Entity Framework (EF) migrations are conflicting with the authentication of the MSI on the database. I came across one of a just a few search engine results that relate to my problem, but the answer is insufficient:

Errors e.g. Login failed for user '' · Issue #13801 · MicrosoftDocs/azure-docs

I'd like to continue using database migrations as my team always has in the past. The issue appears to occur even if the database already exists.

Any help would be greatly appreciated. Thanks!

Continue reading...