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?

[SachinNandanwar]

I was able to find the solution,

I have blogged on the issue.

https://www.azureguru.net/set-fabric-semantic-model-datasource-credentials-via-power-bi-rest-api

[jaryszek]

The same issue here

[hmayer156789]

I know already that it is possible with this API for regular DirectQuery / Import Datasets.
It is the BindToGateway API
Datasets - Bind To Gateway In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

For Direct lake its failing because it has an issue - but I had a issue ongoing and I got told its fixed but is still pending deployment (end of Nov most likeley)

In the End you post something like

{

    "gatewayObjectId": "00000000-0000-0000-0000-000000000000",

    "datasourceObjectIds": ["xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"]

}
where the gatewayObjectId tells it to not use a gateway but an SCC, but the datasourceobjectid is the Id of the Cloud connection. You can get if from the Id from the PowerBI Web UI (or only via unsupported APIs I think)

I found it via some fabric docu links in the end then there

There they set it via a Fabric Notebooks rest client.

Analysis-Services/pbidevmode/fabricps-pbip at master · microsoft/Analysis-Services · GitHub

[hmayer156789]

This issue is now resolved and should work now for all dataset kinds.

[ddelapasse]

Anyone have progress on this?  I was assured by one of our Microsoft reps that this it's possible to programatically create a semantic model and bind it to an existing gateway, but I see here under Limitations that it's only for on-prem and our gateways are connected to Azure Sql Servers.   This is a huge problem for us if we cannot do this through the api.

[ddelapasse]

Update: The MS guy just replied saying "Unfortunately, our marketing team picked a confusing name for "On Premises Data Gateways" — while these gateways are often deployed on premises, the gateway does not literally have to be on premises. You can think of it as a gateway between your resources that reside in your private (or firewall-protected) network and the Fabric service. Your network can be on premises, in Azure, in a third-party cloud, etc. "

So this definitely SHOULD work, but doesn't for me.  My details are here if anyone is curious or looking to perform good deeds :-).

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

[Pseud0nym]

I found that when I already have a semantic model connected to my cloud connection I can call Datasets - Get Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn and get both the datasourceId and the gatewayId (gateway here is a misnomer as it is just an id for the cloud connection).

I can then use these values on any semantic model I like using Datasets - Bind To Gateway In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn. If the sql server and database are the same as in that first semantic model, it will have the same datasourceId for that data source. This means I only have to set it manually in one of my semantic models then I can use the api to deploy any other semantic model using the same source.

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