Entity Framework code first migration is not working when authenticating SQL database using Azure Active Directory Application

1
votes

I am using Entity Framework code first with db migration for our Azure SQL Server. Recently I tried to use Azure Active Directory authentication using this article https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure. I am passing SQL connection to Entity Framework that is constructed using application access token. Everything works as expected but DB migration. When migrating DB, I get following error. Any help on this is appreciated.

System.InvalidOperationException HResult=0x80131509 Message=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. Source=EntityFramework.SqlServer StackTrace: at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) at System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) at System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase() at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.MigrateDatabaseToLatestVersion2.InitializeDatabase(TContext context) at System.Data.Entity.Internal.InternalContext.<>c__DisplayClassf1.b__e() at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() at System.Data.Entity.Internal.LazyInternalContext.b__4(InternalContext c) at System.Data.Entity.Internal.RetryAction1.PerformAction(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action1 action) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() at System.Data.Entity.Internal.InternalContext.Initialize() at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) at System.Data.Entity.Internal.Linq.InternalSet1.Initialize() at System.Data.Entity.Internal.Linq.InternalSet1.get_InternalContext() at System.Data.Entity.Internal.Linq.InternalSet1.ActOnSet(Action action, EntityState newState, Object entity, String methodName) at System.Data.Entity.Internal.Linq.InternalSet1.Add(Object entity) at System.Data.Entity.DbSet`1.Add(TEntity entity)

Inner Exception 1: SqlException: Login failed for user ''.

2
entity-frameworkazureazure-active-directory

2 Answers

0
votes

In looking through this documentation, I think the Modify connection string step needs to be executed twice, once for the application database and another for access to the master database as you cannot simply browse to master without reauthenticating or the credential used has not been given permissions for access to the master database.

I think it's key to include the following line after you assign your token, disable it and the app start working as expected.

Database.SetInitializer<MyDatabaseContext>(null);
0
votes

I was trying to do the same thing in EF6 and found that it's not possible with current EF6 library. The issue is that DbMigrator always tries to open SQL connection with connection string, and it does not allow injection of an open SQL connection. https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.dbmigrator?view=entity-framework-6.2.0