Using Azure Service Token Provider for Entity Framework 6 is breaking migration tools

Question

I am following the instructions at https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi to transition from username/password connection strings to authenticate using MSI. The application runs fine and authenticates to the SQL server both for local development and when running deployed on Azure App Service.

However, now when I try and add/get migrations via the Package Manager Console it no longer works.

Get-Migrations now produces:
"No migrations have been applied to the target database."

I tried switching back to my old username/password based connection string and I still get the same output. However, if I comment out the section of web.config that registers the SqlAuthenticationProviders:

<SqlAuthenticationProviders>  
	    <providers>  
		    <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />  
	    </providers>  
    </SqlAuthenticationProviders>  

Then Get-Migrations starts working fine with my old connection string. How can I have this authentication provider configured and allow "Add-Migration" and "Get-Migrations" tools to work?

I have Entity Framework updated to 6.4.4 - .NET Framework - MVC

Thanks

Answer 1

The problem ended up being twofold:

1) We had the EF context in a separate project from the web project. I had to add the Microsoft.Azure.Services.AppAuthentication package and config changes to enable the SQL authentication provider to the project containing the context and migrations.

2) After resolving (1), username/password connection strings would work again even with the auth provider registered however Azure AD token authentication still didn't work with the EF package manager console tools. This ended up being due to a dll (Microsoft.IdentityModel.Clients.ActiveDirectory) failing to load due to a version mismatch. I made both projects use the same version of this package and corrected binding redirects and then AD authentication worked.

The key was that Get-Migrations did not provide any error output - it just said there were no migrations. However, running Update-Database was much more enlightening as it actually provided an exception that indicated what was failing in the authentication.

I believe the issue revolves around the fact that the application was working correctly based on the web application projects packages/configuration whereas it appears the EF cli tools were using the other projects DLL directly, with the web.config from the web project, which opened it up to inconsistency.

thank you to @ajkuma for the assistance

Answer 2

@Gavin H , Thanks for posting this good question.

Just to clarify, in the the MyDbConnection connection string isn't used at all because the local development environment uses a Sqlite database file, and the Azure production environment uses a connection string from App Service. With Active Directory authentication, you want both environments to use the same connection string.

In appsettings.json, replace the value of the MyDbConnection connection string with, kindly refer this doc- Next, you can supply the Entity Framework database context with the access token for the SQL Database.

In Data\MyDatabaseContext.cs, add the following code inside the curly braces of the empty MyDatabaseContext (DbContextOptions<MyDatabaseContext> options) constructor.