Sql database in Fabric

[SaharRad]

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 connectivity tests...

Attempting connection

[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot open server

Here’s what I’ve done so far:

1. Tried various authentication methods, including Microsoft Entra ID Password Authentication, but the connection still fails. In this method, I provided my login ID and password, but the issue persists.
2. Confirmed that my email is added to the SQL database security settings in Microsoft Fabric.
3. Verified the server name matches the connection string provided in Fabric.

Despite trying multiple configurations, the connection test continues to fail. Is there something I might be missing in the setup, or are there additional steps to enable external ODBC connections to Microsoft Fabric SQL databases?

Any advice or guidance would be greatly appreciated!

Thank you in advance!

When I tried to access the Azure portal using the business email account I created specifically for using Fabric, I encountered this error. It might be related to the issue I had when connecting to the server in the SQL database within Fabric.

[OliverO]

I also added myself as a db_owner and db_datareader...

[OliverO]

might be easier to read

Might it be an MFA issue? 

[OliverO]

Hi Mr Levy

This is the error code from trying the above code, after editing for my server and db.

If I run the AZ login and select my tenant from the VS Code terminal, but am not logged into the corporate domain should it work?

Thanks

Oliver

pyodbc.Error: ('FA004', "[FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' 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 '' in Entra ID (Authentication option is 'ActiveDirectoryInteractive').\r\nError code 0x534; state 11\r\n(null) (0)")

[dlevy]

Hi @OliverO - You have to be logged in for integrated auth to work. Running az login will get you a logged in session. If you open a new command window, run az login then run your python script in the same window I'd expect it to just work. That is actually how I do testing against our various test tenants. 

[OliverO]

Should I open a support case?

[dlevy]

Yes, creating a support case will be fastest/safest. 

[OliverO]

thanks - submitted

[OliverO]

Thanks!

Hmm, same issue using CMD session...

[OliverO]

Hi Mr Levy

I am a Power BI developer for a design company and look after their Power BI data in the Power BI Service.

I do not log in to their domain from my machine, but log in to app.powerbi.com from my browser and import data through the data gateway.

I have also installed the CLI Azure login tool and have logged in from the VS Code terminal.

Should this be sufficient to connect to a Fabric SQL Database?  Are there any settings I need to make in the Fabric SQL Database?  I have added myself explicitly as a db owner and also a reader.  Not sure if that is necessary, but I thought I may as well do it anyway whilst trying to get it to connect.

I am pretty good at SQL coding, but not at the workings of Entra!

Many thanks for your help, Oliver

[OliverO]

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 'blah.blah@blah.com' 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 'blah.blah@blah.com' 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]

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]

Thanks Mr Levy! I will try this tomorrow

[dlevy]

Hi @SaharRad - Sorry to hear it's giving you trouble! If you are trying to run on a Windows machine the easiest way to get up and running quickly is to set up a DSN. That will help you work out an connectivity issues between your machine and the database. 

What language are you using? We have a bunch of samples out there that I could point you toward.

[SaharRad]

Hi, thanks for following up! I tried setting up a DSN as suggested, but unfortunately, I still couldn't connect to the server. I'm currently using Python for this project. If there are any specific samples or additional tips you could share for this language, that would be really helpful. Looking forward to your suggestions!

[dlevy]

If the ODBC GUI is not working to set up a connection then you may have something else going on.

Some key things are:

1. You need the whole server name but should omit any leading characters, like "tcp:". The ",1433" at the end is optional.
2. You need the whole database name, GUID and all. 
3. Many drivers require the az cli to be installed https://learn.microsoft.com/en-us/cli/azure/install-azure-cli-windows?tabs=azure-cli. Make sure to run an 'az login' after installing it.

Here's a basic python sample that will use the logged in users entra id: 

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

connectionString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=<server>;Database=<database>;Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryInteractive'
conn = pyodbc.connect(connectionString)

SQL_QUERY = """
select Opportunity_Number, ProductID, Opportunity_Created_On, Quantity, Comments from Opportunity where Opportunity_number = 'OPP-500'
"""

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

records = cursor.fetchall()
for r in records:
    print(f"{r.Opportunity_Number}\t{r.ProductID}\t{r.Comments}")

[SaharRad · Accepted solution]

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.

[dlevy]

Oh, yep...that will do it. 😀

If it is a personal tenant then it is easy enough to create a subscription. If you are worried about accidental spending then just use a visa gift card to get started. You can always switch it out later.

For a work tenant that's probably going to require a meeting or at least a few well worded emails.