topic Re: Sql database in Fabric in SQL database

Sql database in Fabric

SaharRad — Fri, 10 Jan 2025 19:36:15 GMT

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:

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.
Confirmed that my email is added to the SQL database security settings in Microsoft Fabric.
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.

Re: Sql database in Fabric

dlevy — Fri, 10 Jan 2025 19:42:45 GMT

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.

Re: Sql database in Fabric

SaharRad — Fri, 10 Jan 2025 20:03:26 GMT

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!

Re: Sql database in Fabric

dlevy — Fri, 10 Jan 2025 22:32:40 GMT

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

Some key things are:

You need the whole server name but should omit any leading characters, like "tcp:". The ",1433" at the end is optional.
You need the whole database name, GUID and all.
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}")

Re: Sql database in Fabric

SaharRad — Sat, 11 Jan 2025 04:26:45 GMT

Thank you for the response.

I followed all the suggested steps:

Confirmed DNS and network connectivity (nslookup, Test-NetConnection).
Verified I have ODBC Driver 18 and pyodbc installed.
Ran my Python script with ActiveDirectoryInteractive and also tried other auth methods.
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.

Re: Sql database in Fabric

dlevy — Mon, 13 Jan 2025 17:18:32 GMT

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.

Re: Sql database in Fabric

OliverO — Wed, 21 May 2025 16:26:27 GMT

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

Re: Sql database in Fabric

dlevy — Wed, 21 May 2025 17:12:51 GMT

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.

Re: Sql database in Fabric

OliverO — Wed, 21 May 2025 17:58:41 GMT

Thanks Mr Levy! I will try this tomorrow

Re: Sql database in Fabric

OliverO — Wed, 21 May 2025 22:09:28 GMT

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

Re: Sql database in Fabric

OliverO — Thu, 22 May 2025 09:59:19 GMT

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)")

Re: Sql database in Fabric

OliverO — Thu, 22 May 2025 10:18:44 GMT

might be easier to read

Might it be an MFA issue?

Re: Sql database in Fabric

OliverO — Thu, 22 May 2025 10:36:15 GMT

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

Re: Sql database in Fabric

dlevy — Thu, 22 May 2025 14:15:52 GMT

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.

Re: Sql database in Fabric

OliverO — Thu, 22 May 2025 14:24:05 GMT

Thanks!

Hmm, same issue using CMD session...

Re: Sql database in Fabric

OliverO — Thu, 22 May 2025 14:25:04 GMT

Should I open a support case?

Re: Sql database in Fabric

dlevy — Thu, 22 May 2025 14:28:42 GMT

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

Re: Sql database in Fabric

OliverO — Thu, 22 May 2025 15:13:10 GMT

thanks - submitted