Azure-docs: EF migrations not working with access token

[sergey-netdev]

While the approach described works fine in regular code EF tooling seems to be buggy. When running update-database the following error is generated:

"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."

even if the connection string contains Persist Security Info=True;
And it seems empty initializer is ignored (Database.SetInitializer<MyContext>(null);) , why?

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 ''.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<Open>b__36(DbConnection t, DbConnectionInterceptionContext c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.<UsingConnection>b__32()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action`1 act)
   at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act)
   --- End of inner exception stack trace ---
   at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act)
   at System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable`1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript)
   at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 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.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
   at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 31b0d33c-f88e-d50c-2926-7933940f3b92
• Version Independent ID: 4aaef906-f501-6947-5d23-1b704470f09d
• Content: Secure SQL Database connection with managed identity - Azure App Service
• Content Source: articles/app-service/app-service-web-tutorial-connect-msi.md
• Service: app-service-web
• GitHub Login: @cephalin
• Microsoft Alias: cephalin

[Mike-Ubezzi-MSFT]

@sergey-netdev Thank you very much for your interest in Azure cloud services. 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 will assign this to the content owner to evaluate and update as appropriate.

[cephalin]

@sergey-netdev The tutorial steps does not include the scenario of connecting to master database, but only the database with the business data. The error indicates that you're going beyond the scope of the tutorial already. As the tutorial is mainly about managed identity and not about how to connect to various databases, the best place to help debug your problem would be on the forums.

please-close

[sergey-netdev]

@cephalin The sample code from the tutorial is definitely for Entity Framework, isn't it? If I apply the code to my project it breaks the ability to run migrations so that whole MSI/token thing is _totally useless_ for those who employ code first approach.

[cephalin]

@sergey-netdev I apologize I read the question too fast and didn't realize it was about code-first migration, _not_ about master db. I think one way is to enable automatic migration in your project so that it gets automatically run after WebDeploy. I've been told that you can connect to Azure services using managed identities from Visual Studio by consulting the steps here, but I have not been able to make it work, nor have I been shown a working solution with an authenticated connection to SQLDB like in this tutorial.

[tomgallard]

@MicahMcKittrick-MSFT @cephalin - please can we reopen this one. I am running to problems with automatic migrations using MSI / access token - and I can't see a solution anywhere in this thread.

[cephalin]

@tomgallard @sergey-netdev this doc has been updated to use an Azure AD user as the admin, which, along with the new AppAuthentication version, allows for direct connectivity from VS, including EF migrations. #please-close

[royish]

I had the same issue,
my problem was that we changed the connection to the DB to no longer user username and password but a token instead, EF version 6.1.3 has a bug where it will not accept it,
in version 6.3.0 this was fixed and you can now pass a dbcontext to this function (that has the token inside),
hope this helps