How can we query lakehosue table to get all records using Azure function

[laka7]

In order to create jsm data connection, I tried to use azure functino to expose the content of fabric tables which will be used to update jsm dropdowns, however i don't seem to have the correct endpoint to access table level.
Does any one know this? The documentation is more focusing on monitoring lakehouse, get list of tables etc here https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-api?source=docs 
but something like this doesn't seem to work

"https://api.fabric.microsoft.com/v1/workspaces/{workspaceID}/lakehouses/{lakehouseID}/"sql query to...


Thanks 

[Rufyda]

Hi @laka7 , You cannot use the Lakehouse REST API to directly fetch table data.

The practical solution is to connect to the Lakehouse SQL Endpoint from an Azure Function and retrieve the data using a SQL query.

[laka7]

Thank you for your answer.
I am still getting errors and not be able to access correctly the sql endpoints, any setting or credntials, I may have missed, I already added teh service principal and used the correct connection string...

[v-menakakota · Accepted solution]

Hi   @laka7   ,
Thanks for reaching out to the Microsoft fabric community forum. 

 

The SQL analytics endpoint only works with Microsoft Entra authentication (user with MFA or a service principal). A couple of things to check:

• Workspace access: Make sure your service principal is added directly to the Fabric workspace(Contribute role).
• SQL endpoint access: Once added, the service principal should automatically get access. If login still fails, you can confirm it’s recognized in the SQL endpoint by checking sys.database_principals with a user account.
  
  If I misunderstand your needs or you still have problems on it, please feel free to let us know.   
  Best Regards, 
  Community Support Team  

[v-menakakota]

Hi @laka7 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team

[v-menakakota]

Hi @laka7 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team

[v-menakakota]

Hi @laka7 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team

[tayloramy]

Hi @laka7,

You can't query a Lakehouse table by calling the Fabric REST endpoint directly. The Lakehouse REST API is for management and metadata (list items, list tables, start loads, etc.), not for executing SQL. That is why URLs like https://api.fabric.microsoft.com/v1/workspaces/{id}/lakehouses/{id}/"sql query..." do not work. See the Lakehouse API overview and the Tables List/Load docs for what is actually supported Lakehouse REST API, List Tables, Load Table.

How to do it instead

Query your Lakehouse via its SQL analytics endpoint using a normal SQL client from your Azure Function. Every Lakehouse has a read-only SQL analytics endpoint for T-SQL queries; this is the supported way to SELECT data from Lakehouse Delta tables What is the SQL analytics endpoint for a lakehouse?. Connectivity is over the standard SQL Server TDS protocol on port 1433 with Microsoft Entra ID auth (user or service principal/managed identity) Warehouse connectivity. You can find and copy the SQL connection string from the Lakehouse item in the Fabric UI and use it from any app or function Connect to Fabric Data Warehouse, Query the SQL analytics endpoint or Warehouse.

If this helps, consider giving some Kudos. If it solves your problem, please mark it as the solution.

[laka7]

Thank you @tayloramy for your answer, I am trying to getit to work with SQL endpoint, I am having trouble to set up this, I tried to use the connection string from sql endpoint on fabric to set up 

SQL_SERVER, SQL_USER, and  SQL_Password, but this gives error for  wrong credentials... Do you know if the connection string is the correct one or there is somethign else to check from the settings in fabric to reteive these credentials, please? 

[tayloramy]

Hi @laka7, 

To connect to the SQL Endpoint, you need to use Microsoft Entra auth, either Microsoft Entra MFA for a user account, or Microsoft Entra Service Principal for a service principal. 

Service Principals in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

How are you trying to do this, are you using notebooks? What language are you using? 

In Python, I am able to connect to the endpoint using a service principal with this connection string: 

    conn_str = (
        "Driver={ODBC Driver 17 for SQL Server};"
        f"Server={FABRIC_SQL_SERVER};"
        f"Database={FABRIC_SQL_DATABASE};"
        f"UID={CLIENT_ID};PWD={CLIENT_SECRET};"
        "Authentication=ActiveDirectoryServicePrincipal;"
        "Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
    )

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

[laka7]

Hi @tayloramy , 
Indeed I have tried similar cnc_string structure, I am using python running locally not inside fabric.
Still 

 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not login because the authentication failed

 getting this after double checking the credentials, adding permissions in workspace etc 

[tayloramy]

Hi @laka7, 

My code snippet is also running locally, not in Fabric. 

What permissions does your service proncipal have on the workspace? 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.