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.
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.