CODEWITHSUNDEEP
entity-frameworkentity-framework-6azure-sql-databaseentity-framework-migrations
user1857450
user1857450

Reputation: 561

How to fix 'CREATE SCHEMA' failed error when running EF Code First migrations with Azure Managed Identity

I am trying to run EF6 Code First migrations against an Azure SQL database connecting via Azure Managed Identity. There are several migrations to run. The last migration fails. This migration is different to the others because it creates tables in a new schema.

This is the error which is returned when running the Entity Framework Code First migration which creates the new schema:

System.Data.SqlClient.SqlException: The specified schema name "uuid@uuid" either does not exist or you do not have permission to use it.
CREATE SCHEMA failed due to previous errors.

I have logged the SQL commands on the database and the command which appears to be failing is

IF schema_id('MyNewSchema') IS NULL
EXECUTE('CREATE SCHEMA [MyNewSchema]')

With the error: 

<batch_information><failure_reason>Err 2759, Level 16, Server mysqldbname
CREATE SCHEMA failed due to previous errors.</failure_reason></batch_information>

Here are some details on the system:

Things I've tried

1. Adding roles

The main thing I've tried is using the Microsoft permissions docs to work out what permissions are needed. So far I have added the following roles to the contained user which the App Service running migrations connects as:

db_ddladmin
db_datareader
db_datawriter
db_securityadmin
db_owner
db_accessadmin

(Note that the other migrations worked fine with just db_ddladmin, db_datareader and db_datawriter)

2. Running migrations as server admin

I have tried running the migrations as the SQL Server admin user. This works, but we are not allowed to connect as the SQL Server admin user for the production system.

Upvotes: 0

Views: 6214

Answers (2)

Nisd
Nisd

Reputation: 1133

If you add AUTHORIZATION to the CREATE SCHEMA command, it works.

So the statement Entity Framework creates need's to look like this instead:

IF schema_id('MyNewSchema') IS NULL
EXECUTE('CREATE SCHEMA [MyNewSchema] AUTHORIZATION [dbo]')

You can do this by overwriting ApplicationSqlServerMigrationsSqlGenerator

    protected override void Generate(EnsureSchemaOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        if (operation is null)
            throw new ArgumentNullException(nameof(operation));
        if (builder is null)
            throw new ArgumentNullException(nameof(builder));
        if (string.Equals(operation.Name, "dbo", StringComparison.OrdinalIgnoreCase))
        {
            return;
        }
        var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
        builder
            .Append("IF SCHEMA_ID(")
            .Append(stringTypeMapping.GenerateSqlLiteral(operation.Name))
            .Append(") IS NULL EXEC(")
            .Append(
                stringTypeMapping.GenerateSqlLiteral(
                    "CREATE SCHEMA "
                    + Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name)
                    + " AUTHORIZATION "
                    + Dependencies.SqlGenerationHelper.DelimitIdentifier("dbo")
                    + Dependencies.SqlGenerationHelper.StatementTerminator))
            .Append(")")
            .AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
            .EndCommand();
    }

And then registering it:

        services.AddDbContextPool<ApplicationDbContext>(options =>
        {
            options.UseSqlServer("Your connection string");
            options.ReplaceService<IMigrationsSqlGenerator, ApplicationSqlServerMigrationsSqlGenerator>();
        });

(The code above is for EF Core, as I had the issue there)

Upvotes: 2

Muzzar
Muzzar

Reputation: 26

I've just run into the same issue using asp.net core 2.2 and EF core. I was trying to use a managed identity to create the schema and got the same error. I found this article which indicates it is a bug - https://techcommunity.microsoft.com/t5/Azure-Database-Support-Blog/Lesson-Learned-54-The-specified-schema-name-name-domain-com/ba-p/369152.

My only workaround has been to create the schema with server admin which isn't ideal.

Upvotes: 1

Related Questions