Unable to connect to Fabric SQL Endpoint using pseudo user via MSAL in Fabric Notebook (AADSTS50079

[sarav_s]

I’m trying to connect to a Fabric SQL Endpoint from a Fabric notebook using a pseudo user account (assigned with admin on fabric workspace roles where the lakehouse resides one I am connecting to).

I’m using the MSAL PublicClientApplication flow to acquire an access token and authenticate via pyodbc. 

Code snippet: 

import msal, struct, pyodbc

tenant_id = "<tenant-id>"
client_id = "04b07795-8ddb-461a-bbee-02f9e1bf7b46" #MS public client
username = "test@tenant.onmicrosoft.com"
password = "password"
sql_endpoint = "<sql endpoint of the lh I m connecting to>"
database = "<lakehouse name>"

app = msal.PublicClientApplication(
client_id=client_id,
authority=f"https://login.microsoftonline.com/{tenant_id}"
)

result = app.acquire_token_by_username_password(
username=username,
password=password,
scopes=["https://database.windows.net/.default"]
)

if "access_token" not in result:
    raise Exception(f"Token error: {result.get('error_description')}")

token = result["access_token"].encode("utf-16-le")
token_struct = struct.pack("=i", len(token)) + token

conn_str = (
    f"Driver={{ODBC Driver 18 for SQL Server}};"
    f"Server=tcp:{sql_endpoint},1433;"
    f"Database={catalog};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
)

conn = pyodbc.connect(conn_str, attrs_before={1256: token_struct})
print("Connected")

But I get ,

Exception: Token error: AADSTS50079: Due to a configuration change made by your administrator, or because you moved to a new location, you must enroll in multi-factor authentication to access

Note :
- I was able to work with Service principal until it had issues in running DCL commands 
- I tried directly omiting access_token with user/password with Authentication type - 

ActiveDirectoryPassword aswell - Failed too 😞

Any insights if I have missed something .

[svenchio · Accepted solution]

Hi @sarav_s , there's many moving part on authentication, but one that just happedn recently (I think i saw the warning last week or so) is that mutli-factor authentication is being ENFORCED on Azure Entra ID, It was a gradual plan, but destinally is finally here! So, I guess the user you created (and worked for a while) was one that has been excluded from mutli-factor authentication, correct? and for a while it worked just fine, until the MSFT Entra ID MFA enforce policy maybe got in effect ... I would suggeste you to get in contact with your MSFT Entra ID Admin (or perhaps you have that role) and confirm is the MFA enforcment went into effect in your tenant, I'm  pretty confident that happend to you ... that answer your initial query regarding "any insights if I have missed something", please check and let me know if this is the root cause of your issue and if so, the next would be alternatives for solution, i would happily suggest a few. All the best, kudos if you find this info useful or mark as solution. 

[sarav_s]

The admin team disabled the user account's MFA . And it went through . 

With MFA removed, It went through with 'ActiveDirectoryPassword' authentication as well.  

odbc_driver = "{ODBC Driver 18 for SQL Server}"

conn_str = (
    f"DRIVER={odbc_driver};"
    f"SERVER={SQL_END_POINT};"
    f"DATABASE={catalog};"
    f"UID={username};"
    f"PWD={password};"
    "Authentication=ActiveDirectoryPassword;"
    "Encrypt=yes;"
    "TrustServerCertificate=no;" 
    "Connection Timeout=60;"
)

try:
    conn = pyodbc.connect(conn_str,autocommit=True)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sys.database_principals")
    rows = cursor.fetchall()
    print("Connection successful. Fetched rows:")
    for row in rows:
        print(row)

    cursor.close()
    conn.close()

except pyodbc.Error as ex:
    # sqlstate = ex.args[0]
    print(f"Connection Failed: {ex}")

 
Now that this works , just curious to know what are other alternatives that you were about to suggest. 

[Vinodh247]

tldr: username/password + MFA → unsupported. Use interactive, managed identity, or service principal instead.

----

you are hitting AADSTS50079 because your pseudo user account is MFA enforced or conditional access–protected, and acquire_token_by_username_password() does not support MFA or modern auth. That flow works only for non-interactive, non-MFA accounts (which Microsoft discourages).

Key points:

• MSAL username/password flow cannot bypass MFA.

• Fabric SQL endpoint enforces interactive or managed identity–based auth.

• Service principals are limited (no DCL), and pseudo users with MFA cannot use password-based auth.

Workarounds: 

1. Use interactive device flow in MSAL (works in notebooks): result = app.acquire_token_interactive(scopes=["https://database.windows.net/.default"])

2. Use MI if running in Fabric (preferred).

3. Use Entra App + Certificate or Secret instead of pseudo user.