Update Dataset Connections using Power BI APIs

[LJonesStripe]

I am trying to update the connection of a Power BI dataset after uploading the Power BI report. I have tested the connection and it works when I manually select the connection through the GUI, but I can't work out the parameters I need to send in the post request to do this programmatically. My connection is a cloud connection so i don't think I need a gateway, I don't when I do it through the GUI and the data source type is an Azure SQL Database.

API Endpoint: $url = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$DatasetId/Default.UpdateDatasources"

My Request: Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($ConnectionDetailsObject | ConvertTo-Json -Depth 10) -ContentType "application/json"

The API Documentation I am working from

I have already created my connection in the Power BI web app. For the purposes of this post lets pretend the ID of that connection is XYZ123.

In my PowerShell script I have created a class which outputs the following JSON for the request body. To the best of my knowledge this is correct, the request does not send an error back, but the data source does not update.

{
  "updateDetails": [
    {
      "DatasourceType": "Sql",
      "ConnectionDetails": {
        "database": "NAME_OF_DATABASE",
        "server": "NAME_OF_SERVER:1433"
      },
      "DatasourceId": "XYZ123",
      "GatewayId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxbc7de9"
    }
  ]
}

The GatewayId value I obtained from inspecting the network tab of the request that's sent when i update the value manually through the GUI. I don't know if this is correct, but it seems like a generic id for the 'cloud gateway'.

What should the body of my request look like to update the connection to the parameter i want?

[v-nmadadi-msft]

Hi @LJonesStripe ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[LJonesStripe]

Hi @v-nmadadi-msft, no the solutions provided did not work. I had a support case with Microsoft open for a long time. But what I am currently trying to do is not technically possible at this time via the API.

This support issue can be closed. Thank you for your help

[v-nmadadi-msft]

Hi @LJonesStripe ,

We really appreciate your efforts and thank you for letting us know the update on the issue.

Please continue using fabric community forum for any further assistance.

Thanks and Regards

[v-nmadadi-msft]

Hi @LJonesStripe ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thanks and Regards

[v-nmadadi-msft]

Hi @LJonesStripe ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[FarhanJeelani · Accepted solution]

Your JSON request body should look like this:

{
"updateDetails": [
{
"datasourceType": "Sql",
"connectionDetails": {
"database": "NAME_OF_DATABASE",
"server": "NAME_OF_SERVER.database.windows.net"
},
"datasourceId": "XYZ123"
}
]
}

 For cloud sources like Azure SQL Database, leave this field empty or remove it. The API does not require a gateway for cloud connections.

PowerShell Script:

# Parameters
$workspaceId = "YOUR_WORKSPACE_ID"
$datasetId = "YOUR_DATASET_ID"
$url = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.UpdateDatasources"

# Connection Details
$ConnectionDetailsObject = @{
    updateDetails = @(
        @{
            datasourceType = "Sql"
            connectionDetails = @{
                database = "NAME_OF_DATABASE"
                server = "NAME_OF_SERVER.database.windows.net"
            }
        }
    )
}

# Convert to JSON and Make the API Call
Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($ConnectionDetailsObject | ConvertTo-Json -Depth 10) -ContentType "application/json"

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[LJonesStripe]

Hi, thanks for your response. I get the following error message:

Invoke-PowerBIRestMethod: 
Line |
6 | Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($Connectio …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| One or more errors occurred. ({
"code": "InvalidRequest",
"message": "The given connection details for selector # are invalid"
})
Invoke-PowerBIRestMethod: 
Line |
6 | Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($Connectio …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Encountered errors when invoking the command: {
"code": "InvalidRequest",
"message": "The given connection details for selector # are invalid"
}

I have verified by printing out the JSON that the ConnectionDetialsObject is formatted as expected.

[v-nmadadi-msft]

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

Please ensure that:
1) The original data source and the new data source have the exact same schema.
2) You are the dataset owner
3) your token is not expired, Tokens are typically valid for 1 hour. If you receive an authentication error, refresh the token by re-running Connect-PowerBIServiceAccount or obtaining a new token programmatically.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to give "Kudos"

Thanks and Regards

[LJonesStripe]

Hi,

I beleive the data sources have the exact same schema. How do I verify this?

I am the dataset owner, In my powershell script I am authenticating using oAuth to the API and authenticating using the same account. Can I just verify that I am using the correct endpoint?

In the GUI I am trying to update this section:

I am using this API: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-datasources-in-group

[v-nmadadi-msft]

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

1) To check for the schemas, you have to connect to the SQL database and check the columns of the table for which you made the connection.

2) Please try to run some other api call to check if there is no issue with authentication like for example
Datasets - Get Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn
which returns the specified dataset from the specified workspace.

3) If that is working you can rule out any issue with authentication and then try to check if there is any errors with connection details and JSON payload

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards