Forum Discussion
Changing Semantic Model Connection Mapping for Fabric Lakehouse SQL Endpoint Using REST API
Hi, nipunm
Thank you for your prompt response. I now have a better understanding of your requirements and have made timely adjustments to the solution:
1.Firstly, I created the following example, with its relational view as shown below:
It encountered a similar issue to yours:
2.Secondly, we can observe that the creatable gateway connection is a cloud gateway.
Currently, using the API to create a data connection requires a gatewayid, and most REST APIs do not support cloud gateways and virtual gateways. Below is a screenshot from the official documentation:
For further details, please refer to:
Gateways - Create Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Therefore, directly using the API to create a data connection is not feasible, which was a misunderstanding on my part regarding your issue.
3.We recommend using the following API to add users to your workspace and manually add the gateway:
POST https://api.powerbi.com/v1.0/myorg/groups/{groupId}/usersBelow is the request body:
{ "emailAddress": "[email protected]", "groupUserAccessRight": "Admin" }4.The corresponding content to be added is:
You may need to ensure that your service principal is assigned a role in the workspace where your lakehouse is located.
5.Below are the final results:
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous ,
thanks for the response.
- I do have connection string parameters in the Power BI report, but I don't think there is currently any way to add credentials as parameters in the Power BI report. The "UpdateParameters" API will only update the parameters available in the model.
- I created a report without connection string parameters to use the "UpdateDatasources" API. I was able to update the connection string, but I am still unable to set/update the credentials even after passing the details in the API body.
Here is the API Call code
url = f'https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/Default.UpdateParameters'
headers = {
'Authorization': f'Bearer {access_token}',
'Content-Type': 'application/json'
}
data ={
"updateDetails": [
{
"datasourceType": "Sql",
"connectionDetails": {
"server": "<<new_Server connection string >>",
"database": "<<DB Name>>"
},
"credentialDetails": {
"credentialType": "ServicePrincipal",
"credentials": {
"applicationId": "<<SPN ID>>",
"applicationSecret": "<<SPN_Key>>",
"tenantId": "<<tenantID>>"
}
}
}
]
}
response = requests.post(url, headers=headers, json=data)
if response.status_code == 200:
try:
return response.json()
except ValueError:
return {"message": "Parameters updated successfully, but no content returned"}
else:
print("Fail")
return response.json()
Could you please provide any guidance or alternative methods to set/update the credentials for the data source in the Power BI report using the REST API?
Thank you.
Hi, nipunm
Thank you for your prompt response. I now have a better understanding of your requirements and have made timely adjustments to the solution:
1.Firstly, I created the following example, with its relational view as shown below:
It encountered a similar issue to yours:
2.Secondly, we can observe that the creatable gateway connection is a cloud gateway.
Currently, using the API to create a data connection requires a gatewayid, and most REST APIs do not support cloud gateways and virtual gateways. Below is a screenshot from the official documentation:
For further details, please refer to:
Gateways - Create Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Therefore, directly using the API to create a data connection is not feasible, which was a misunderstanding on my part regarding your issue.
3.We recommend using the following API to add users to your workspace and manually add the gateway:
POST https://api.powerbi.com/v1.0/myorg/groups/{groupId}/users
Below is the request body:
{
"emailAddress": "[email protected]",
"groupUserAccessRight": "Admin"
}
4.The corresponding content to be added is:
You may need to ensure that your service principal is assigned a role in the workspace where your lakehouse is located.
5.Below are the final results:
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous, thanks for doing all the test/POC.
Not adding user to workspace is not a limitation but a requirement, though thanks for the clarification. this will not work for me, but this a workable solution.
Will accept this as Solution, it would be helpful for others.