Forum Discussion

BIService's avatar
BIService
Frequent Visitor
1 year ago
Solved

Can't connect to a SQL endpoint from MS Fabric Lakehouse

Hi, i'm trying to connect to Fabric Lakehouse with SQL endpoint. Here's the example code:

import pyodbc
import struct
import msal

# Configuración de la aplicación
TENANT_ID = "xxxxxxxxxxxxxxxxxxxx"
CLIENT_ID = "xxxxxxxxxxxxxxxxxxxxxxx"
CLIENT_SECRET = "xxxxxxxxxxxxxxxxxxxx"

# URL de autoridad (directorio)
AUTHORITY = f"https://login.microsoftonline.com/{TENANT_ID}"

# Scope (debe terminar en .default para client_credentials)
SCOPE = ["https://graph.microsoft.com/.default"]

# Crear la aplicación confidencial
app = msal.ConfidentialClientApplication(
    CLIENT_ID,
    authority=AUTHORITY,
    client_credential=CLIENT_SECRET
)

# Obtener el token
result = app.acquire_token_for_client(scopes=SCOPE)
if "access_token" in result:
    print("✅ Token obtenido:")
    print(result["access_token"])
else:
    print("❌ Error al obtener token")
    print(result.get("error"))
    print(result.get("error_description"))

access_token = result["access_token"]
# Configuración
SQL_SERVER = "xxxxxxxxxxxxxxxxxxxxxxxxxx"  # Copiado del endpoint SQL
SQL_DATABASE = "BD_Cluster_Estrategia_Consumo"

# Parámetros de conexión ODBC
conn_str = (
    f"Driver={{ODBC Driver 18 for SQL Server}};"
    f"Server={SQL_SERVER};"
    f"Database={SQL_DATABASE};"
    f"Encrypt=yes;"
    f"TrustServerCertificate=no;"
)

# Conectar con el token
conn = pyodbc.connect(conn_str, attrs_before={1256: access_token},autocommit=True)
cursor = conn.cursor()

cursor.execute("SELECT TOP 10 * FROM sys.tables")
for row in cursor.fetchall():
    print(row)

But always throws this error:

pyodbc.Error: ('HY000', 'The driver did not supply an error!')

 

I think it could be an issue related to the app registration API permissions, but even if i got all the possible permissions to Power BI Service, the connection still doesn't work. I also think it could be related with the fact i'm using a trial account and it needs a paid one. Does anybody know something about that? What permissions the app registration need? I need to use a paid account?

 

  • Hi BIService,

    Thanks for the update. It is good to hear that the SQL endpoint is working when you connect through SSMS with MFA that confirms the endpoint itself is fine.

    Since the connection is still failing when using pyodbc with the access token, the issue might be related to the type of token you are using. SSMS uses your user identity through an interactive login (with MFA), while your Python script is using an application only token (from the client credentials flow). In some cases, especially in Fabric trial environments this type of token may not have the right permissions to connect to the Lakehouse SQL endpoint.

    Here are a couple of things you can try: Test with a user-based token Instead of using client credentials, try getting a token using an interactive method like device code flow. This will help us determine if the issue is specific to the app-only token.

    check the token itself: You can decode the token using jwt.ms and confirm that the aud (audience) claim is set to

    https://database.windows.net/ 

    and see if it includes the expected permissions (roles or scp).

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Fabric Community Forum.

6 Replies

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Icon for Community Support rankCommunity Support

    Hi BIService,
    Thank you for reaching out to the community and sharing the details of your scenario.

    The issue you are facing is due to the incorrect scope used for token generation. You’re requesting an access token for Microsoft Graph, but to connect to a Fabric Lakehouse via the SQL endpoint, the token must target the SQL resource.

    Replace your scope with: 

    ["https://database.windows.net/.default"]

    Also, ensure:

    • You are using the correct SQL endpoint from the Lakehouse.
    • Your app registration has application permissions.
    • Your trial account allows SQL endpoint access (not always the case).

    If you follow the corrected script above and still face issues, the likely cause is trial limitations in that case, a paid license (like F64 capacity) may be required for full SQL endpoint support.

    Kindly refer to the below mentioned documentation link for better understanding:
    What is the SQL analytics endpoint for a Lakehouse? - Microsoft Fabric | Microsoft Learn
    Using Microsoft Entra ID with the ODBC Driver - ODBC Driver for SQL Server | Microsoft Learn
    OAuth 2.0 client credentials flow on the Microsoft identity platform - Microsoft identity platform | Microsoft Learn
    Warehouse Connectivity - Microsoft Fabric | Microsoft Learn

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Fabric Community Forum.

    • BIService's avatar
      BIService
      Frequent Visitor

      Hi. Thank you for your answer. I already change the scope with the new link, but still doesn't work. Also, i tried using Microsoft SQL Server Management Studio (with MFA) and the connection works fine. So i'm not sure if the problem is the trial capacity. The app registration has all the application permissions, btw.

      • v-kpoloju-msft's avatar
        v-kpoloju-msft
        Icon for Community Support rankCommunity Support

        Hi BIService,

        Thanks for the update. It is good to hear that the SQL endpoint is working when you connect through SSMS with MFA that confirms the endpoint itself is fine.

        Since the connection is still failing when using pyodbc with the access token, the issue might be related to the type of token you are using. SSMS uses your user identity through an interactive login (with MFA), while your Python script is using an application only token (from the client credentials flow). In some cases, especially in Fabric trial environments this type of token may not have the right permissions to connect to the Lakehouse SQL endpoint.

        Here are a couple of things you can try: Test with a user-based token Instead of using client credentials, try getting a token using an interactive method like device code flow. This will help us determine if the issue is specific to the app-only token.

        check the token itself: You can decode the token using jwt.ms and confirm that the aud (audience) claim is set to

        https://database.windows.net/ 

        and see if it includes the expected permissions (roles or scp).

        Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

        Thank you for using the Microsoft Fabric Community Forum.