I have a Databricks App built using FastAPI. Users access this App after authenticating with Microsoft MFA on Databricks Azure Cloud. The App connects to a SQL Server Managed Instance (MI) that also supports Microsoft MFA.

I want the authenticated user's credentials to be passed to the App and used for database connections. In the development environment, this works fine since I am logged in with my Microsoft MFA. Can this be achieved in a Databricks App?

Below is a snippet of the code I am using with SQLAlchemy and PyODBC

from sqlalchemy import create_engine, event, text
from azure.identity import DefaultAzureCredential
import struct
import pyodbc

try:
    azure_credentials = DefaultAzureCredential()
    
    TOKEN_URL = "/"
    SQL_COPT_SS_ACCESS_TOKEN = 1256  

    server = 'azsql-mi-xyz.database.windows'
    port = "1234"
    database = 'database'
    driver = "ODBC Driver 18 for SQL Server"

    conn_str = f"mssql+pyodbc://@{server}:{port}/{database}?driver={driver}"
    engine = create_engine(conn_str)

    @event.listens_for(engine, "do_connect")
    def provide_token(dialect, conn_rec, cargs, cparams):
        # Remove "Trusted_Connection" parameter added by SQLAlchemy
        cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")

        # Generate token credential
        raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le")
        token_struct = struct.pack(f"<I{len(raw_token)}s", len(raw_token), raw_token)

        # Apply it to connection parameters
        cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}

    # Test the connection
    with engine.connect() as connection:
        query = text("SELECT * FROM tablex")
        rows = connection.execute(query).fetchall()
    
    data = [dict(row._mapping) for row in rows]
    return {"data": data}

except Exception as e:
    raise HTTPException(status_code=500, detail=f"General Error: {str(e)}")

Below is the error message from the application

General Error: DefaultAzureCredential failed to retrieve a token from the included credentials.
Attempted credentials:
EnvironmentCredential: EnvironmentCredential authentication unavailable. Environment variables are not fully configured.
Visit to troubleshoot this issue.\n\tManagedIdentityCredential: ManagedIdentityCredential authentication unavailable, no response from the IMDS endpoint.
SharedTokenCacheCredential: SharedTokenCacheCredential authentication unavailable. No accounts were found in the cache.
AzureCliCredential: Azure CLI not found on path
AzurePowerShellCredential: PowerShell is not installed
AzureDeveloperCliCredential: Azure Developer CLI could not be found. Please visit for installation instructions and then,once installed, authenticate to your Azure account using 'azd auth login'.
To mitigate this issue, please refer to the troubleshooting guidelines here at
.

I have a Databricks App built using FastAPI. Users access this App after authenticating with Microsoft MFA on Databricks Azure Cloud. The App connects to a SQL Server Managed Instance (MI) that also supports Microsoft MFA.

I want the authenticated user's credentials to be passed to the App and used for database connections. In the development environment, this works fine since I am logged in with my Microsoft MFA. Can this be achieved in a Databricks App?

Below is a snippet of the code I am using with SQLAlchemy and PyODBC

from sqlalchemy import create_engine, event, text
from azure.identity import DefaultAzureCredential
import struct
import pyodbc

try:
    azure_credentials = DefaultAzureCredential()
    
    TOKEN_URL = "https://database.windows/"
    SQL_COPT_SS_ACCESS_TOKEN = 1256  

    server = 'azsql-mi-xyz.database.windows'
    port = "1234"
    database = 'database'
    driver = "ODBC Driver 18 for SQL Server"

    conn_str = f"mssql+pyodbc://@{server}:{port}/{database}?driver={driver}"
    engine = create_engine(conn_str)

    @event.listens_for(engine, "do_connect")
    def provide_token(dialect, conn_rec, cargs, cparams):
        # Remove "Trusted_Connection" parameter added by SQLAlchemy
        cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")

        # Generate token credential
        raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le")
        token_struct = struct.pack(f"<I{len(raw_token)}s", len(raw_token), raw_token)

        # Apply it to connection parameters
        cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}

    # Test the connection
    with engine.connect() as connection:
        query = text("SELECT * FROM tablex")
        rows = connection.execute(query).fetchall()
    
    data = [dict(row._mapping) for row in rows]
    return {"data": data}

except Exception as e:
    raise HTTPException(status_code=500, detail=f"General Error: {str(e)}")

Below is the error message from the application

General Error: DefaultAzureCredential failed to retrieve a token from the included credentials.
Attempted credentials:
EnvironmentCredential: EnvironmentCredential authentication unavailable. Environment variables are not fully configured.
Visit https://aka.ms/azsdk/python/identity/environmentcredential/troubleshoot to troubleshoot this issue.\n\tManagedIdentityCredential: ManagedIdentityCredential authentication unavailable, no response from the IMDS endpoint.
SharedTokenCacheCredential: SharedTokenCacheCredential authentication unavailable. No accounts were found in the cache.
AzureCliCredential: Azure CLI not found on path
AzurePowerShellCredential: PowerShell is not installed
AzureDeveloperCliCredential: Azure Developer CLI could not be found. Please visit https://aka.ms/azure-dev for installation instructions and then,once installed, authenticate to your Azure account using 'azd auth login'.
To mitigate this issue, please refer to the troubleshooting guidelines here at
https://aka.ms/azsdk/python/identity/defaultazurecredential/troubleshoot.

• databricks
• azure-databricks

• Exactly where you are running this code? is it like any azure environment like vm? – JayashankarGS Commented Mar 17 at 9:50
• @JayashankarGS The App runs on Databricks clusters, which I think are composed of Azure Virtual Machines (VMs) and managed resources. – Obirieni Simeo Commented Mar 18 at 10:13

1 Answer 1

The DefaultAzureCredential() tries to fetch the credential in following order, it stops once it gets any one of the credentials.

• EnvironmentCredential
• WorkloadIdentityCredential
• ManagedIdentityCredential
• SharedTokenCacheCredential
• VisualStudioCredential
• VisualStudioCodeCredential
• AzureCliCredential
• AzurePowerShellCredential
• AzureDeveloperCliCredential
• InteractiveBrowserCredential

So, if it's in azure hosted environment then enable the managed identity.

If it's service principal, then configure below environment variables like mentioned here

AZURE_TENANT_ID, AZURE_CLIENT_ID, AZURE_CLIENT_SECRET

Make sure you assign required roles to your managed identity or service principal in sql server resource.