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

[frederiksoftware]

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.