Changing Semantic Model Connection Mapping for Fabric Lakehouse SQL Endpoint Using REST API

[nipunm]

Hello Power BI Community,

I have published a Power BI model using the REST API with an AAD/SPN. However, the model is currently pointing to a Fabric Lakehouse SQL Endpoint, which defaults to single sign-on (SSO). Due to this, I am encountering the following refresh error:

"We cannot refresh this semantic model because this semantic model uses a default data connection without explicit connection credentials. Please replace the default data connection in the semantic model settings with an explicit cloud or gateway data connection."

I have created a connection to the Lakehouse SQL Endpoint and need to update the semantic model connection mapping to use this connection instead of the SSO option.

The issue is that only the SPN has access to the workspace, so I cannot make this change manually. Is there any other way to update the semantic model connection mapping using the REST API or another automated approach?

Any help or guidance would be greatly appreciated!

Thank you.

[v-linyulu-msft]

Hi, @nipunm 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.You may attempt to update the data source credentials using the following API:

POST https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/Default.UpdateParameters

2.Below is an example request body:

{
  "updateDetails": [
    {
      "datasourceType": "Sql",
      "connectionDetails": {
        "server": "your-lakehouse-sql-endpoint-server",
        "database": "your-database-name"
      },
      "credentialDetails": {
        "credentialType": "Basic",
        "credentials": {
          "username": "your-username",
          "password": "your-password"
        }
      }
    }
  ]
}

3.Is that if you are using the Default UpdateDatasources, you may need to pay attention to is:

4.For further details, please refer to:

Datasets - Update Parameters In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

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

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.

[nipunm]

@v-linyulu-msft ,

thanks for the response.

1. 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.
2. 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.UpdateParamet...'
   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.

[v-linyulu-msft · Accepted solution]

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": "john@contoso.com",
  "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.

[nipunm]

@v-linyulu-msft, 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.