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

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.