Connecting Semantic Model to Cloud Data Source via API

[accountingbob]

Hi All,

My company uses an automated process using the PowerBI Rest API to create workspaces for our clients. Previously, when we created a new workspace, the server would upload the Semantic Model files and then update the usernames and passwords that the model used to connect to the database.

However, this meant that each model had its own copy of the usernames and passwords, so a recent password change for the database user took us down for about a day, so a co-worker pointed all of the semantic models to use Cloud Datasource Connections as seen here in the semantic model settings:

The problem is that I cannot figure out a way to set these values via the PowerBI rest api. I fear I may be out of luck for now since the header for the "Manage data connections and gateways" page has the word "(Preview)". 

I have tried to use my browser's dev tools to reverse engineer what the power bi site is doing to set these values, but when I click the apply button it doesn't call into https://api.powerbi.com like the other endpoints, but instead calls https://wabi-us-central-a-primary-redirect.analysis.windows.net/metadata/datamovement/models/<someIn...

I would just try calling this api using the same bearer token from the reguular api, but I have no idea where to get the <someIntegerModelIdNumber> since the only model identifiers I have are all guids and this seems more like a database row id. Plus I need to find some way to get the id's of the Blob and Database connections.

Anyone know how to get around this?

[hmayer156789]

Same Problem here...

How can it be you can do a thing via UI, but not via Automated fashion?

Where is the REST API to get all Cloud Connections.

And where is the rest API to set it to a Models DataSource?

[v-nuoc-msft]

Hi @accountingbob 

As of the last update, the official Power BI REST API documentation does not explicitly document the process of managing cloud data source connections directly through the Power BI REST API, including the process of programmatically assigning those connections to semantic models.

While there may not be documentation support for the specific endpoint you want to query.

However, the Power BI REST API provides the following methods for updating data source credentials and updating data sources.

Gateways - Update Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Datasets - Update Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Learn

I also found some relevant documentation on how to create and share cloud data sources and connect to cloud data sources in power bi service.

Create and share cloud data sources in the Power BI service - Power BI | Microsoft Learn

Connect to cloud data sources in the Power BI service - Power BI | Microsoft Learn

I hope you find it helpful.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[accountingbob]

Thank you for your reply. I have been using the update datasource enpoints to update the username and password previously, but I can't find a way to list the "gateways" for the cloud data sources. I have tried the https://api.powerbi.com/v1.0/myorg/gateways endpoint and it returns an empty list. If I pull a semantic model which is already connected to the cloud datasource, the result does contain a gatewayId, but when I then try to get that gateway using https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}, I get a 404 result.

[mforte]

Same situation, did you find a solution?

[jacopoberta]

any update on this? We've exactly the same problem: it seems to be impossible to assign/set up a cloud connection to a semantic model through api.

[foodd]

Issues

If you have found a true bug or issue, you can make a post to the forum to let others know (and that would be very nice of you to do so). But if you want to get it fixed, you should post it in the Issues/Ideas (it varies) area of the community. Specifically, you should post it here:

Power BI - https://community.powerbi.com/t5/Issues/idb-p/Issues
PowerAutomate - https://powerusers.microsoft.com/t5/I-Found-A-Bug/bd-p/BugFound
PowerApps - https://ideas.powerapps.com


Ideas

If you have a new idea for functionality or how something should work, you can certainly start a thread on the community to solicit feedback on your idea, but you should also post the idea in the ideas section of the website and solicit others to vote on your idea. The development teams use the Ideas area of the communities to build their development pipeline and a significant amount of weight is placed on Ideas that have lots of votes versus those that do not. The ideas areas of the communities are here:

Power BI - https://ideas.powerbi.com
PowerAutomate - https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas
PowerApps - https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

[tlecomte]

We are having that problem here as well.

Our use case is about updating credentials for a shared cloud connection that is used in a OneLake shortcut. We would like to do that programmatically, on a schedule. For non-shared connections, so far we were successful with https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource, but not for shared cloud connections.

We've looked at the network requests and responses that happen when we navigate the Power BI UI at https://app.powerbi.com/groups/me/gateways . We noticed that there is gatewayId that is used for all of them, for a gateway that is of the "TenantCloud" type, and each of these shared cloud connections has a datasourceId. However, as mentioned by @accountingbob, https://api.powerbi.com/v1.0/myorg/gateways does not return that gateway id. We also tried to use these gateway id and datasource id in https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource, but that leads to an Unauthorized error.

So we are in the same situation: we cannot use the existing API to update the credentials of a shared cloud connection. The impact for us is that it is blocking us from using OneLake shortcuts, since we would have to rely on someone manually updating credentials in the UI, and that's not robust enough.