Django MigrationSchemaMissing exception on Azure SQL using service principal auth-CodePudding

I'm actually working on a client's project we are connecting Azure SQL DB via service principal authentication. Our client has given us access to Azure SQL and they have given us the credential for it. so to connect to Azure SQL we're using Microsoft's mssql-django package.

so this is what the DB configurations look like.

DATABASES = {

'default': {
    'ENGINE': 'mssql',
    'NAME': <db_name>,  
    'USER': <db_user>,
    'PASSWORD': <db_password>,
    'HOST': "<url>.database.windows.net",
    'PORT': '1433',
    'OPTIONS': {
        'driver': 'ODBC Driver 17 for SQL Server',
        'connection_timeout':60,
        'connection_retries':3,
        "extra_params": "Authentication=ActiveDirectoryServicePrincipal",
    
    },
}

so when I'm trying to run the migrate command I'm getting the below error

raise MigrationSchemaMissing(
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The specified schema name "<db_user>@<azure_tenant_id>" either does not exist or you do not have permission to use it. (2760) (SQLExecDirectW)'))

by looking at the error we can definitely say that either the schema is not created or the DB user that they have given us does not have permission to create the tables in the DB.

can anyone please suggest what could be going wrong, this is definitely not an issue from the Django or from the django-mssql package but an issue from the Azure side. If it's an issue from the Azure side then I can reach out to our client and ask them to fix it from their end. As I do not have much experience with Azure/AzureSQL any advice would really help me.

Thank you in advance.

UPDATE 1:

when I logged into the database and without giving schema when I tried to create tables manually from the SQL server it gave me the permission error. My hunch is that with service principal when I run the migrate command it's not picking the default schema and maybe that's why it could be giving the error.

UPDATE 2

when I logged in through my user account and when I ran the select SCHEMA_NAME() SQL command it gave me the result as null. which means that there is no default schema that has been assigned to my user account. Similarly, I guess the user that the client has given will not have a default schema assigned and maybe that's why when I ran the Django migrate command it was unable to find a schema and gave the error - The specified schema name <db_user>@<azure_tenant_id> either does not exist.

But now my question is as you can see when I ran the Django migrate command, mssql-django with service principal actually looked for the schema db_user>@<azure_tenant_id>, does SQL server support this kind of schema name? Also when i checked the available schemas in the DB I did not see any schema with the name <db_user>@<azure_tenant_id>.

CodePudding user response：

Well, my guess was correct, there was no schema that was assigned to our service principal. I reached out to the infra team of our client and asked them to set dbo as the default schema for the AD group. After setting the dbo as the default schema, I was able to successfully run the initial migration in Django.