Forum Discussion
Sql database in Fabric
- 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.
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.
17 Replies
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.
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!
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}")
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
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.
Thanks Mr Levy! I will try this tomorrow
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
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)")
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.
Thanks!
Hmm, same issue using CMD session...
might be easier to read
Might it be an MFA issue?
I also added myself as a db_owner and db_datareader...
