Forum Discussion
Upload BigQuery credentials in a Semantic model, which API to use
Context:
- I am trying to deploy PowerBI semantic model & report via API as part of a Ci-Cd on Azure Devops.
- I am using a Service Principal, and doing the following steps: (i) deploy, (ii) take-over, (iii) upload credentials and (iv) refresh.
- I am failing to upload credentials and thus can't refresh the semantic model (the refresh API calls itself works)
- The semantic model uses a BigQuery datasource, the GCP credentials I am using are functionnal (they work when I upload them in the web interface here: https://app.powerbi.com/groups/me/gateways?experience=power-bi , and am able to access the BQ ressources using a connection using those credentials)
- The azure service principal I am using has the Gateway.ReadWrite.All, Dataset.ReadWrite.All permissions and is admin of the workspace I am targeting.
I have tried the following API:
- Gateways - Update Datasource ( https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource )
Does not work, this is for On-prem only, I think. And I get a "DM_GWPipeline_Gateway_InvalidConnectionCredentials" error - Datasets - Update Datasources In Group ( https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-datasources-in-group )
Does not work, I don't think this works for BigQuery connection as specified in the limitation. And I get a "dataset doesn't contain a datasourcethat matches selector #1" error - Datasets - Update Parameters ( https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-parameters )
This API is recommanded in the limitation of "Datasets - Update Datasources"
I am able to use this API, but I don't see how I should upload credentials with it? Credentaisl are not a parameters
Question.
Which API endpoint should I use, given this is a connection to BigQuery? Am I approaching thing the right way? I do not know the PowerBI ecosystem well.
Hello Anonymous .
Thanks a lot for your answer. So was not able to push credentials even with the 'Connections - Update Connection" api. I ended up using 'Items - Bind Semantic Model Connection' [1].
So instead of pushing credentials. I just create a connection once and for all, then bind it everytime I publish a new semantic model.
[1] Items - Bind Semantic Model Connection - REST API (SemanticModel) | Microsoft Learn
4 Replies
Hi ryegavian ,
Thanks for reaching out to Microsoft Fabric Community and for the detailed explanation.
As you already mentioned, based on the current documentation for REST API support, the Datasets Update Datasources API does not include BigQuery in the supported connector list: Datasets - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn
The Gateways Update Datasource API targets on-premises gateway connections, which does not align with BigQuery as it is a cloud connector: Gateways - Update Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Learn
The BigQuery connector itself supports credential configuration directly through the Power BI or Fabric service UI as documented here: Google BigQuery connector - Power Query | Microsoft Learn.
At this point, that continues to be the supported way to enable authentication for refresh.
There is a newer Fabric endpoint for updating connection objects that includes credentialDetails in the request body: Connections - Update Connection - REST API (Core) | Microsoft Learn.
However I did not find any Microsoft documentation or confirmed examples showing this endpoint being used to update BigQuery service account credentials specifically for semantic model refresh.
In the similar community thread below, a credential error occurred when attempting this and the issue was resolved only after switching the JSON key to a raw string format instead of an f-string in the request: Solved: Cannot update Google BigQuery data source credenti... - Microsoft Fabric Community
Hope this helps. Please reach out for further assistance.
Thank you.
Hi ryegavian ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.
Hello Anonymous .
Thanks a lot for your answer. So was not able to push credentials even with the 'Connections - Update Connection" api. I ended up using 'Items - Bind Semantic Model Connection' [1].
So instead of pushing credentials. I just create a connection once and for all, then bind it everytime I publish a new semantic model.
[1] Items - Bind Semantic Model Connection - REST API (SemanticModel) | Microsoft LearnHi ryegavian ,
Thanks for the update. Creating the connection once and then binding it to each newly deployed semantic model is a valid approach, especially since the connection already contains the BigQuery credentials. Using Items – Bind Semantic Model Connection fits well in CI/CD scenarios and avoids the need to update credentials programmatically each time.
Thanks for sharing it here as well - this will help others who run into a similar situation.
Feel free to reach out for further assistance.
Thank you.
