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

[GrowthModel]

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 Serverless DB (built-in), by example with database_name = '10000000000000000000000000000001', setting up the right security for a couple of (auto generated) Entra accounts + Security group. 
• Create a PowerBI workspace and setup access for a security SG + user SG
• Deploy a PowerBI semantic model from a BLOB storage
• Change the database name that is used in M-Query, by editing the PowerBI Semantic Model parameter prmDB
• >> Set credentials for the data source (not automatically created while deploying by the function app) (***)
• >> Refresh dataset by the function app

Interesting to know: 

• When deploying the PBIx manually and editing the semantic model credentials manually in the web browser, I can refresh the dataset.  Important to know since it shows me (?) that it isn't a SQL login issue.
• I tried to change the credentials with SQL user/pwd AND also Service Principal by code.  If I do this manually, it works.
• I am not user Fabric or PPU capacity, I created a PBI Pro workspace in the steps above
• All steps are succesfully executred, except the "set credentials" and "refresh dataset" (which might work after succeeding the set credentials step ?)
• Ownership of the Semantic Model settings = Service Principal
• Since the Connection Type became "Sql", it requires a (cloud) gateway.  It is stored in python parameter : PBIGATEWAYID

Extra complexity : 

• Function app is running in Tenant A and PowerBI is located in Tenant B, a cross tenant Service Principal / Enterprise application is used.  I don't see any issue here, since I can create workspaces & deploy a PBI report, but like to mention it 
• PowerBI desktop doesn't have the AzureSQLDB / Synapse DB connection type anymore.  You can chose it when creating a data source, but when deploying, the type becomes "Sql"

Questions:

• How do I make PBI understand that I am connecting to a Synapse Serverless DB (Not 'sql')
• Do I use : ***-ondemand.database.windows.net or ***-ondemand.sql.azuresynapse.net as server name ?
• Why are all steps succeeding, but not the credentials setup (although the mentioned user DOES have access to the Synapse serverless DB) ?  What options do I have to make it work ? 
  Please help !

(***)  Hereby the code that fails :

    patch_url = f"https://api.powerbi.com/v1.0/myorg/gateways/{PBIGATEWAYID}/datasources/{datasource_id}"

    payload = {

        "credentialDetails": {

            "credentialType": "Basic",

            "credentials": {

                "username": SQLADMINLogin,

                "password": SQLADMINPwd

            },

            "privacyLevel": "Organizational",

            "encryptedConnection": "Encrypted",

            "useEndUserOAuth2Credentials": False,

            "skipTestConnection": True

        }

    }

      r_patch = requests.patch(patch_url, headers=pbi_headers, json=payload, timeout=60)

      

I also do have a version with OAUTH/Service Principal, which looks safer than user/pwd but also doesn't work.

Looking forward to anyone that can help me resolving this issue !

Many thanks to the saving angel 😉

[v-pnaroju-msft]

Hi GrowthModel,

Thankyou for the update.

Please review the proposed suggestions. As requested, kindly raise a Microsoft Support ticket using the following link: Microsoft Fabric Support and Status | Microsoft Fabric

If you require native support for automated credential configuration for Synapse Serverless connections, please submit a request in the Power BI Ideas Forum via this link: Fabric Ideas - Microsoft Fabric Community

Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[GrowthModel]

Thank you, I launched the request as Fabric Idea 

[v-pnaroju-msft]

Hi GrowthModel,

Thankyou for the update.

Based on my understanding, Power BI Service automatically converts Azure Synapse Serverless and Azure SQL Database connections to a generic “SQL” connection type when a dataset is published. Power BI stores these connections internally as type "Sql" even if the Azure Synapse or Azure SQL connector was selected in Power BI Desktop. Because of this behaviour, a gateway is automatically associated with the dataset even though Synapse is a cloud source. Credentials cannot be programmatically applied using REST APIs after deployment, which results in the “Failed to test connection” error.

Please consider the following approaches, which may help to resolve the issue:

1. Set the credentials manually once in Power BI Service for that data source. Power BI will reuse these credentials across future deployments in the same workspace.

2. Consider using Microsoft Fabric Dataflows Gen2 or Fabric Lakehouse SQL Endpoints, which natively retain credentials and support automated refresh without manual intervention.

3. Fully automating credential setup for Synapse Serverless datasets through REST APIs is not currently supported. Power BI will continue to display the connection as "Sql" and will require credentials to be provided through the user interface.

To further investigate your specific tenant configuration, please raise a Microsoft Support ticket using the following link:Microsoft Fabric Support and Status | Microsoft Fabric

Additionally, if you need native support for automated credential configuration for Synapse Serverless connections, please raise a request in the Power BI Ideas Forum using the following link:Fabric Ideas - Microsoft Fabric Community

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

Thank you.

[GrowthModel]

Thanks again for the detailed explanation !

A few clarifications from my side:

1) Manually entering credentials unfortunately isn’t an option in my case, as I regularly create new workspaces and need the process to be fully automated.

2) I will need to review the licensing aspect — PPU might be feasible, but anything above that is not cost-effective for our setup.

3) It’s disappointing to hear that full automation of credentials for Synapse Serverless isn’t currently supported. That is exactly what I have been trying to achieve for quite some time:

On the one hand, getting away from the generic "Sql" connection type (I’ve seen references to "AzureSQLDb", but that doesn’t seem to be supported anymore).  On the other hand, being able to update the "Sql" connection credentials programmatically (avoiding gateway complexities).

Given the current limitations, I’ll proceed with your remaining suggestions:

* I’ll double-check whether there is anything misconfigured on the gateway side (though I suspect I already know the answer…), and

* I will raise the issue in the Fabric Community.  Not sure how many others are running into the same problem, but it’s worth trying. Thanks again for your support and clarification.

>> If there is still a way to obtain a true "AzureSQLDb" or Synapse Serverless connection type, I would be very interested to hear about it !  (chosing other connection type, a manual fix in the pbix/connection file, ...) <<

Kind regards 

[GrowthModel]

UPDATE : hereby the less complex situation: forget the Function app.
As suggested, I followed the steps in this Microsoft tutorial : https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/tutorial-connect-power-bi-desktop

> I have created a PBIx with >>> "Azure SQL DB connection" <<< using the Demo Database method
> It all works on my desktop, but when deploying it to a cloud PBI workspace, the credentials are removed.

> All 3 connection methods work on my computer : OAUTH, sql credentials & service principal.

Two remaining questions :
* how do I get it work WITHOUT editing the credentials manually afterwards (it works like that, but I do want a solution without other manual interactions) ?

* how do I get this work WHILE editing the credentials in an Azure Function app (Python) ? Please know that the type is "Sql" while I expected "AzureSQLDb"
> so as written before: THAT should be the reason of failure...

[v-pnaroju-msft]

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 ...
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]

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.