EnsureDatabaseExistsAsync breaks in environments where "master" access disallowed

[mikeredick]

In companies with stronger security policies, like mine, it may not be possible to give the application login access to the master database. We can run the scripts to manually create the schema/tables but EnsureDatabaseExistsAsync will fail to log into the master database preventing the listeners from starting up:

Microsoft.Azure.WebJobs.Host.Listeners.FunctionListenerException : The listener for function 'MyFunction' was unable to start. ---> Microsoft.Data.SqlClient.SqlException : Login failed for user '<token-identified principal>'.
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject,DbConnectionOptions userOptions,DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject,DbConnectionOptions userOptions,DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,UInt32 waitForMultipleObjectsTimeout,Boolean allowCreate,Boolean onlyOneCheckConnection,DbConnectionOptions userOptions,DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at async DurableTask.SqlServer.SqlDbManager.EnsureDatabaseExistsAsync() at /_/src/DurableTask.SqlServer/SqlDbManager.cs : 183
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at async DurableTask.SqlServer.SqlDbManager.AcquireDatabaseLockAsync(Boolean createDatabaseIfNotExists) at /_/src/DurableTask.SqlServer/SqlDbManager.cs : 137
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at async DurableTask.SqlServer.SqlDbManager.CreateOrUpgradeSchemaAsync(Boolean recreateIfExists) at /_/src/DurableTask.SqlServer/SqlDbManager.cs : 33
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at async Microsoft.Azure.WebJobs.Extensions.DurableTask.DurableTaskExtension.StartTaskHubWorkerIfNotStartedAsync() at D:\a\_work\1\s\src\WebJobs.Extensions.DurableTask\DurableTaskExtension.cs : 1414
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at async Microsoft.Azure.WebJobs.Host.Listeners.FunctionListener.StartAsync(??) at D:\a\_work\1\s\src\Microsoft.Azure.WebJobs.Host\Listeners\FunctionListener.cs : 68
   End of inner exception```

[cgillum]

@mikeredick I've created a draft PR to fix this, but I don't have cycles to test it quite yet. Your scenario is supposed to work but I think it was regressed in a recent release.

If you can provide detailed repro steps, then I think I can validate and release the fix much faster.

[mikeredick]

Is this good enough?

To reproduce:

1. Create database manually
2. Run the setup scripts from github
3. Create login/user for the database, ensure it does NOT have access to log in to master
4. Add user to dt_runtime role
5. Configure durable task project to use the database
6. Run it, it should show the errors as it tries to start the event listeners

[cgillum]

The main thing I was looking for is any SQL commands for step 3 in your list, but I can try to figure it out when I have cycles.

[mikeredick]

I've asked our infrastructure team as they created the login and user. I'll let you know when I hear back from them.

[mikeredick]

Ended up having to dig through their pipeline code to find the command, looks like it is just logging into the target database and running:

CREATE USER username FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER username;
ALTER ROLE db_datawriter ADD MEMBER username;
ALTER ROLE dt_runtime ADD MEMBER username;