Forum Discussion

GrowthModel's avatar
GrowthModel
Regular Visitor
9 months ago

PowerBI <> Synapse Serverless <> Azure Function app (Python)

Hi everyone, I am having issues while developing an Azure Function app (Python) that creates a fully automated flow that does the following, WITHOUT ANY MANUAL INTERACTION ... Create a Synapse Se...
Need Help
v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
9 months ago

Hi GrowthModel,

Thank you for your enquiry on the Microsoft Fabric Community Forum.

Based on my understanding, the issue arises because Power BI is identifying your Azure Synapse Serverless connection as a generic SQL data source rather than recognising it as a cloud Synapse source. When Power BI classifies it as SQL, it automatically requires a data gateway. Consequently, your attempt to patch REST API credentials fails, although entering credentials manually through the UI succeeds.

Please follow the steps below, which may help to resolve the issue:

  1. Confirm that your Power BI dataset is recognised as a cloud Synapse source by calling the REST API: GET /groups/{groupId}/datasets/{datasetId}/datasources. If the response shows connectionType: Sql and includes a gatewayId, Power BI is treating it as an on‑premises SQL Server. This is why credentials cannot be set programmatically.
  2. Always use yourworkspace-ondemand.sql.azuresynapse.net, not *.database.windows.net. This ensures Power BI recognises it as Synapse Serverless.
  3. In Power BI Desktop or during dataset creation, select Azure Synapse Analytics (Serverless) as the connector type. Redeploy to the Power BI Service, it should then appear as a cloud source and will not require a gateway.
  4. Once it is recognised as a cloud source, use the Update Datasources (In Group) Power BI REST API to set credentials using a Service Principal (OAuth2). This avoids the need to patch gateway credentials.
  5. Ensure the Service Principal has Power BI API access in the target tenant by enabling “Allow service principals to use Power BI APIs.” 
  6. Because your Function App runs in Tenant A while Power BI is in Tenant B, verify that the Service Principal from Tenant A is registered as an Enterprise Application in Tenant B with the necessary API and workspace permissions.
  7. After setting credentials successfully, use the standard refresh API to trigger dataset refresh.

Please refer to the relevant documentation links for further details:
Datasets - Update Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Datasets - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Tutorial: Connect serverless SQL pool to Power BI Desktop & create report - Azure Synapse Analytics | Microsoft Learn
Datasets - Update Parameters - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Connection strings for Synapse SQL - Azure Synapse Analytics | Microsoft Learn

We hope this information helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

GrowthModel's avatar
GrowthModel
Regular Visitor
9 months ago

Hi,  thank you so much for taking the time to look into my issue in depth, I really appreciate it !!

I already tried most of the things you mentioned and as you wrote : my data source is converted into a "Sql"-datasource although I have chosen the Azure SQL one(s).  Also thank you for the relevant documentation !

So the answers on your questions are:
1) unfortunately it still looks like a "Sql" (on prem ☹️) data source and a gateway appears in the JSON-result when getting the data source details.

2) thanks for confirming that sql.azuresynapse.net is the right server name. I chose that one and took the "Import data" option.

3) I am using the latest version of PBI Desktop (from the MS store) and DO NOT see the "Azure Synapse Analytics (SERVERLESS)" option. I have only two connectors with "Synapse" in the name and that's "Azure Synapse Analytics SQL" (no serverless in the name) and Azure Synapse Analytics workspace (Beta). But chosing one of those (I assume it's the first one) and using sql.azuresynapse... still results in a "Sql" connection type 😞
I redeployed my other PBI tenant to test, to make sure the connection didn't already exist.

4) What's your suggestion to check if it became a cloud data source? Is checking the JSON-response with the https://.../datasources GET result sufficient? (see attached results)

5) & 6) SP has API access, indeed. Since it has already created a PBI workspace and changed the prmDB parameter, I hope this rights are OK.
It's a cross domain SP.

7) I wish they were set succesfully 😏

Bottom line : How do I get rid of the "Sql" connection (and non-cloud gateway) ? Because I assume that's the reason of failure.