First of all I'm not a SQL expert....
My assumption is: Don't let any application user access masterdb.
At least that's the default setup I usually run on our shared Azure SaaS SQL host.
I ready various suggestions which tell you to do that:
Database.SetInitializer<AppDataContext>(null);
To my understanding this however disables automatic migrations entirely.
I want automatic (schema) migrations - I only don't want EF to create a new database (or check database existence against masterdb).
What's the proper solution to that?
Update 1: I didn't decompile the sources but looked at the mono implementation of MigrateDatabaseToLatestVersion
instead.
/// <inheritdoc />
public void InitializeDatabase(TContext context)
{
Check.NotNull(context, "context");
var migrator = new DbMigrator(_config);
migrator.Update();
}
...and migrator.Update(); will do this... with targetMigration being null.
public override void Update(string targetMigration)
{
base.EnsureDatabaseExists(() => UpdateInternal(targetMigration));
}
This will basically call UpdateInternal() - which only takes care of migrations. No database existence call, right? I will look into that.
Update 2:
I can see the following call:
IF db_id(N'my-database') IS NOT NULL SELECT 1 ELSE SELECT Count(*) FROM sys.databases WHERE [name]=N'my-database'
But that's fine - no master database access is required for that.
My understanding is that this is a bug in Entity Framework. See the bug report here from MS employee on their GitHub
"... it seems that there is an issue with the database existence check that is done by the DbMigrator—it is attempting to connect to the master database, which fails. We had updated other existence checks to not require this, but it looks like DbMigrator was either missed or there is some reason it couldn’t be done here..."