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

Created on 11 Sep 2019  路  2Comments  路  Source: dotnet/efcore


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

``` C#
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 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 parameterValues)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary parameterValues)
Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary 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

closed-question customer-reported
Source
picture multco-malan

Most helpful comment

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.

picture multco-malan on 17 Sep 2019
👍3 🎉1

All 2 comments

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 picture multco-malan on 17 Sep 2019
👍3 🎉1

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

multco-malan picture multco-malan on 18 Sep 2019
Was this page helpful?
0 / 5 - 0 ratings

Related issues

pachecosoftware picture pachecosoftware  路  3Comments
bgribaudo picture bgribaudo  路  3Comments
julienshepherd picture julienshepherd  路  3Comments
HappyNomad picture HappyNomad  路  3Comments
mohsin91 picture mohsin91  路  3Comments