Forum Discussion
Connecting Semantic Model to Cloud Data Source via API
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/<someIntegerModelIdNumber>/explicitBind?api-version=10.0
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?
14 Replies
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-apiAs 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.
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.
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.
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?
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.
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 :-).
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 LearnFor 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 · GitHubThis issue is now resolved and should work now for all dataset kinds.
The same issue here
