Forum Discussion

SaharRad's avatar
SaharRad
Regular Visitor
1 year ago
Solved

Sql database in Fabric

Hi everyone, I’m trying to connect an ODBC data source to a SQL Database in Microsoft Fabric, but I’m facing this error: Microsoft ODBC Driver for SQL Server Version 18.04.0001 Running conne...
General Question
Need Help
  • SaharRad's avatar
    SaharRad
    1 year ago

    Thank you for the response. 

    I followed all the suggested steps:

    1. Confirmed DNS and network connectivity (nslookup, Test-NetConnection).
    2. Verified I have ODBC Driver 18 and pyodbc installed.
    3. Ran my Python script with ActiveDirectoryInteractive and also tried other auth methods.
    4. Checked my Azure tenant and found I have no valid subscription assigned or active.

    It turns out my subscription status is causing the issue. I don’t have any active Azure subscription in my tenant.

     

OliverO's avatar
OliverO
Helper III
1 year ago

Hello!

I think I am having a similar issue.

I am trying to connect to a Fabric SQL Database from a Flask/Python web app and am hitting an error like this

 

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('FA004', "[FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '[email protected]' in Entra ID (Authentication option is 'ActiveDirectoryInteractive').\r\nError code 0x534; state 11\r\n(null) (0) (SQLDriverConnect); [FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '[email protected]' in Entra ID (Authentication option is 'ActiveDirectoryInteractive').\r\nError code 0x534; state 11\r\n(null) (0)")
(Background on this error at: https://sqlalche.me/e/20/dbapi)

 

I have now downloaded the ODBC Driver 18 to try to test the connection using that, but I can't get that to work either.

I am a database owner, a Capacity admin and an admin of the workspace too.

Would you be able to give a check list of things to test?

 

Many thanks

Oliver

 

 

  • dlevy's avatar
    dlevy
    Microsoft Employee
    1 year ago

    Hi OliverO - Here is an example using pyodbc but the connection string is the same for sqlalchemy.

     

    """
Connects to a SQL database using pyodbc
"""
import pyodbc

connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};Server=tcp:<redacted>.database.windows.net,1433;Database=<redacted>;Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryInteractive'
conn = pyodbc.connect(connectionString)

SQL_QUERY = """
SELECT 
TOP 5 c.CustomerID, 
c.CompanyName, 
COUNT(soh.SalesOrderID) AS OrderCount 
FROM 
SalesLT.Customer AS c 
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID 
GROUP BY 
c.CustomerID, 
c.CompanyName 
ORDER BY 
OrderCount DESC;
"""

cursor = conn.cursor()
cursor.execute(SQL_QUERY)

records = cursor.fetchall()
for r in records:
    print(f"{r.CustomerID}\t{r.OrderCount}\t{r.CompanyName}")

     

    This works with the AdventureWorks sample data. Make sure to include the entire server and database names, including GUIDs. 

    • OliverO's avatar
      OliverO
      Helper III
      1 year ago

      Thanks Mr Levy! I will try this tomorrow