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,

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's avatar
    GrowthModel
    Regular Visitor
    9 months ago

    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