Connecting On-Prem Django App with Microsoft Fabric DWH fails: Invalid connection string attribute

[FredFred]

I'm trying to connect my Django app to Microsoft Fabric warehouse but I get the following error messages:

django.db.utils.Error: ('01S00', '[01S00] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0) (SQLDriverConnect); [01S00] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)')

I tried various combinations to build the connection string in the settings.py. My first attempt was:

    'FabricDW': {
        'ENGINE': 'mssql',
        'NAME': CONFIG["WAREHOUSE_DB_NAME"],
        'USER': CONFIG["AZURE_CLIENT_ID"],  # Azure AD user
        'PASSWORD': CONFIG["AZURE_CLIENT_SECRET"],  # Azure AD password
        'HOST': CONFIG["WAREHOUSE_HOST"],  # Server hostname
        'PORT': '1433',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
            'extra_params': 'Authentication=ActiveDirectoryServicePrincipal',
            'host_is_server': True,
        },
    }

I looked for solutions and tried various different approaches (such as trying to use different authentication methods) or using DSN (connection itself is working fine). Nothing helps.

When I use connection strings like this in usual python scripts everything works like a charm:

server_name = CONFIG["WAREHOUSE_DB_NAME"]
database_name = CONFIG["WAREHOUSE_DB_NAME"]
client_secret = CONFIG["AZURE_CLIENT_SECRET"]
tenant_id = CONFIG["AZURE_TENANT_ID"]
client_id = CONFIG["AZURE_CLIENT_ID"]
connection_string = f'Driver={{ODBC Driver 17 for SQL Server}};' \
                    f'Server={server_name};Database={database_name};' \
                    f'UID={client_id}@{tenant_id};PWD={client_secret};' \
                    'Authentication=ActiveDirectoryServicePrincipal'
params = urllib.parse.quote_plus(connection_string)
engine = create_engine(f'mssql+pyodbc:///?odbc_connect={params}')

I don't know if there is some parsing complications that I overlook. Help would be great.

[FredFred · Accepted solution]

I found the solution.

I had to do two thing:

First, I had to change the settings.py entry to:

DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'NAME': CONFIG["WAREHOUSE_DB_NAME"],
        'USER': CONFIG["AZURE_CLIENT_ID"],  # Azure AD user
        'PASSWORD': CONFIG["AZURE_CLIENT_SECRET"],  # Azure AD password
        'HOST': CONFIG["WAREHOUSE_HOST"],  # Server hostname
        'PORT': '1433',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
            'extra_params': 'Authentication=ActiveDirectoryServicePrincipal',  # Logs ODBC driver-specific issues
        },
    }
}

And secondly, I had to change two lines of code in the base.py file of the mssql-django package. Namely, I had to comment out this part:

if ms_drivers.match(driver) and os.name == 'nt':
  cstr_parts['MARS_Connection'] = 'yes'

Now, the connection works.

Django still have problems with Fabric Warhouse because I guess some TABLE commands are not ready, yet.

This command:

python manage.py migrate

Leads to:

"django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The PRIMARY KEY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server. (24584) (SQLExecDirectW)'))"

But the initial problem was solved.

[FredFred]

Hi @Anonymous ,

thanks for your reply. But the same method is working fine with an azure sql database or an SQL Server. It is just when connecting to Fabric Warehouse.

So, from your post and the documentation I don't know what to do to solve the issue.

[Anonymous]

Hi @FredFred ,

The error message indicates that there's an invalid connection string attribute.

Make sure that all attributes in the connection string are specified correctly. Sometimes even a small error can cause problems.

For more detailed examples and configurations, see the official documentation below:

GitHub - microsoft/mssql-django: The Microsoft Django backend for SQL Server provides a connectivity...

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[R1k91]

I recently saw one person successfully demoing with python maybe this can help you

from sqlalchemy import create_engine
import sqlalchemy as sa

driver = '{ODBC Driver 18 for SQL Server}'
odbc_str =  sa.URL.create(
    "mssql+pyodbc",
    username=".....",
    password=".....",
    host="<your_host>.datawarehouse.fabric.microsoft.com",
    database="yourdb",
    query={"driver": "ODBC Driver 18 for SQL Server","Authentication":"ActiveDirectoryServicePrincipal"}
) 
print(odbc_str)
db_engine = create_engine(odbc_str)
connection = db_engine.raw_connection()

[FredFred]

Hi @R1k91 ,

as I stated in my initial post I also successfully use sqlalchemy. So, I also don't have any problem with the connection string with sqlalchemy. But in the context of the Django App I still don't see any working approach.

[R1k91]

sorry for not being helpful the only thing I see is the different provider.

17 against 18.

[FredFred]

Hi @R1k91

thank you, for your effort. I also tried the "18" as driver. Unfortunately, no difference.

Frederik

[FredFred]

Hi @Anonymous,

Unfortunately, the issue is still not solved. Is it even possible yet to connect Django with fabric warehouse via mssql-django? I never had any problem with azure sql databases or sql servers.

It is the last problem that I have to solve before being able to migrate our company to fabric. So, I urgently have to find a solution.

[FredFred]

I still don't see any solution to the problem. I tried everything. Has this ever been tried out to connect Django to Fabric Warehouse? Is there a solution that I can immitate? I tried to build a minimal example. Still does not work.

Help would be great @v-huijiey-msft.