Running migration on Azure not working: The specified schema name x either does not exist or you do not have permission to use it

[multco-malan]

dot net core web application running in Azure is not successfully running migrations.
The complete error is:
The specified schema name "95318cf6-5c65-4ed7-9263-45161eee8e07@0c0989e5-5cb9-442f-af4f-3a43092b154a" either does not exist or you do not have permission to use it

I have no idea where the migrator is getting that schema name.
I'm using the default dbo schema for all objects.
Running migrations locally from Package Manager Console works fine.
The applications' db context works fine unless I include the Migrate() function.

Steps to reproduce

Azure SQL Database

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDistributedMemoryCache();
            services.AddHttpClient();

            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

            services.AddOptions();

            // Token acquisition service based on MSAL.NET and chosen token cache implementation
            services.AddAzureAdV2Authentication(Configuration)
                    .AddMsal(new string[] { Configuration["GraphAdGroups:GraphAdGroupsScope"] })
                    .AddInMemoryTokenCaches()
                    ;

            services.Configure<CookieAuthenticationOptions>(AzureADDefaults.CookieScheme, o => o.AccessDeniedPath = "/General/HeyAccessDenied");

            services.AddTodoListService(Configuration);
            services.AddGraphAdGroupsService(Configuration);

            services.Configure<OpenIdConnectOptions>(AzureADDefaults.OpenIdScheme, options =>
            {
                Configuration.GetSection("AzureAd").Bind(options);
                var existingOnAuthorizationCodeReceivedHandler = options.Events.OnAuthorizationCodeReceived;
                options.Events.OnAuthorizationCodeReceived = async context =>
                {
                    await existingOnAuthorizationCodeReceivedHandler(context);
                    await OnAuthorizationCodeReceived(context);
                };

            });

            services.AddAuthorization(options =>
            {
                options.AddPolicy(Policies.Admin, policy => policy.RequireClaim(ClaimTypes.Role, ActiveDirectoryGroupName_ADMIN));
                options.AddPolicy(Policies.Manager, policy => policy.RequireClaim(ClaimTypes.Role, ActiveDirectoryGroupName_MANAGER));
                options.AddPolicy(Policies.Nobody, policy => policy.RequireClaim(ClaimTypes.Role, "NOBODY"));
            });

            services.AddMvc(options =>
            {
                var policy = new AuthorizationPolicyBuilder()
                    .RequireAuthenticatedUser()
                    .Build();
                options.Filters.Add(new AuthorizeFilter(policy));
            })
                .AddRazorPagesOptions(rpos =>
                {
                    rpos.Conventions.AuthorizeFolder("/Admin", Policies.Admin); // e.g. folder \web\Pages\Admin\*
                    rpos.Conventions.AuthorizeFolder("/Manager", Policies.Manager); // e.g. folder \web\Pages\Manager\*
                    rpos.Conventions.AuthorizeFolder("/Nobody", Policies.Nobody);  // e.g. folder \web\Pages\Nobocy\*
                })
                .SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

            services.AddDbContext<SmsTextingContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("SmsTextingContext")));

            // adapted from https://blog.johnnyreilly.com/2018/06/vsts-and-ef-core-migrations.html
            // instead of services.BuildServiceProvider().GetService<SmsTextingContext>().Database.Migrate();
            try
            {
                var optionsBuilder = new DbContextOptionsBuilder<SmsTextingContext>();
                optionsBuilder.UseSqlServer(Configuration.GetConnectionString("SmsTextingContext"));
                using (var migrationContext = new SmsTextingContext(optionsBuilder.Options))
                {
                    migrationContext.Database.Migrate();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Failed to apply migrations!", ex);
            }
        }

public class SmsTextingContext : DbContext
    {
        public SmsTextingContext (DbContextOptions<SmsTextingContext> options)
            : base(options)
        {
            var conn = (System.Data.SqlClient.SqlConnection)Database.GetDbConnection();

            // for Azure Public: https://docs.microsoft.com/en-us/azure/azure-government/documentation-government-services-database
            //conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

            // for Azure Government:
            conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.usgovcloudapi.net/").Result;
        }

        public DbSet<web.Models.SmsTexting.Channel> Channel { get; set; }
        public DbSet<web.Models.SmsTexting.ToBeDeleted> ToBeDeleteds { get; set; }
    }

Got Exceptions? YEP

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action wrapCloseInAction)
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, bool breakConnection, Action wrapCloseInAction)
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose)
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady)
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(string methodName, bool async, int timeout, bool asyncWrite)
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource completion, bool sendToPipe, int timeout, bool asyncWrite, string methodName)
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable migrationCommands, IRelationalConnection connection)
Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(string targetMigration)
Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
web.Startup.ConfigureServices(IServiceCollection services) in Startup.cs

Further technical details

Azure SQL Database exists.
Web application connecting to SQL database works. (when i don't include migrate)
[dbo].[__EFMigrationsHistory] table exists.
If I run the web app locally the migration works.

EF Core version: 2.2.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 2.2
Operating system: Azure US Government
IDE: Visual Studio Enterprise 2019 16.2.2

[multco-malan]

Hi All,
Discovered what was wrong with my configuration.
I had followed the steps here, https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities
and created an AAD group created a contained SQL user utilizing this new AAD group.

I had created the contained SQL user using this sql:
CREATE USER [<name>AzureSQLDBAccessGroup] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [<name>AzureSQLDBAccessGroup]; ALTER ROLE db_datawriter ADD MEMBER [<name>AzureSQLDBAccessGroup];

which does not set a default schema for this user.

Discovered that by running this sql:
select default_schema_name , type_desc , name , create_date from sys.database_principals order by default_schema_name , type_desc , name

as saw that the default_schema_name for the user [AzureSQLDBAccessGroup]
was NULL.

So I altered this users schema by running:
ALTER USER [<name>AzureSQLDBAccessGroup] WITH DEFAULT_SCHEMA=[dbo]

and now migrations successfully run on my web app deployed to Azure.

[multco-malan]

For future reference I was also following along this tutorial in setting up my application:
https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#modify-aspnet-core