Forbidden error on fetching dataset parameters

[Anonymous]

Please forgive me if I ask stupid quesions as I'm new to everything.

Essentially I'd like to copy a Power BI report from one workspace to another and then modify the data source's parameters. The datasource is backed by Databricks.

I've followed this article https://docs.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal to set up a service principle, then generated a secret so that I could authenticate with the Power BI API.

I then assigned the service principal as an Admin of the Power BI workspaces.

I was able to run the code snippet below to authenticate

$azureAplicationId = "<redacted>"
$azureTenantId = "<redacted>"
$servicePrincipalSecret = ConvertTo-SecureString "<redacted>" -AsPlainText -Force
$psCred = New-Object System.Management.Automation.PSCredential($azureAplicationId , $servicePrincipalSecret)
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $psCred -Tenant $azureTenantId

Then I was able to download a report from one workspace and copy it to another:

Export-PowerBIReport -Id $report.Id -WorkspaceId $ws.Id -OutFile "$($reportName).pbix"
New-PowerBIReport -Path "$($reportName).pbix" -WorkspaceId $targetWs.Id -Timeout 3600 -Name "$($reportName)._"

 However, I got the 403 error when I tried to fetch information of the dataset associated with a workspace

$dataset = Get-PowerBIDataset -Id $report.DatasetId -WorkspaceId $ws.Id
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/datasets/$($dataset.Id)/parameters" -Method Get

Invoke-PowerBIRestMethod: One or more errors occurred. (Response status code does not indicate success: 403 (Forbidden).)

A little bit more detailed of the error:

PS /home/ubuntu> Resolve-PowerBIError -Last

   HistoryId: 63

Message        : Response status code does not indicate success: 403 (Forbidden).
StackTrace     :    at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
                    at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.InvokeRestMethod(String url, String body, PowerBIWebRequestMethod requestType)
Exception      : System.Net.Http.HttpRequestException
InvocationInfo : {Invoke-PowerBIRestMethod}
Line           : Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/datasets/$($dataset.Id)/parameters" -Method Get
Position       : At line:1 char:1
                 + Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/dat …
                 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HistoryId      : 63

I'm not really sure what I've done wrong. Any help would be highly appreciated.

Thanks.

[Muesdo]

Hi,

maybe I could still help someone

I was facing the same Error :

Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/datasets/$datasetId/Default.UpdateDatasources" -Method POST -Body $postBodyJson

Invoke-PowerBIRestMethod: One or more errors occurred. (Response status code does not indicate success: 403 (Forbidden).)

What I have done was:

1. Verify the service principal's permissions: Ensure that the service principal has the required permissions to update data sources in the Power BI workspace. You can check the permissions by going to the Power BI Admin portal, selecting the "Tenant settings" tab, and then navigating to the "Developer settings" section. Verify that the service principal has the "Allow service principals to use Power BI APIs" setting enabled.
2. Check the service principal's role in the workspace: Make sure that the service principal has the appropriate role assigned to the workspace where the dataset resides. The service principal should have at least the "Contributor" role or higher to be able to update data sources.
3. Ensure that the service principal has the necessary API permissions: In the Azure Active Directory (AAD) portal, navigate to the service principal's "API permissions" section and verify that the necessary permissions are granted. The service principal should have the "Dataset.ReadWrite.All" and "Dataflow.ReadWrite.All" permissions.

But the Error was still the same. After this I found this PS Script: https://github.com/microsoft/PowerBI-Developer-Samples/blob/master/PowerShell%20Scripts/Update-Conne...

There I noticed they have build the URL different to mine. So I changed to:

$datasourePatchUrl = "groups/$workspaceId/datasets/$datasetId/Default.UpdateDatasources"
Invoke-PowerBIRestMethod -Method Post -Url $datasourePatchUrl -Body $postBodyJson

That works for me.

So to fetch information of the dataset associated with a workspace I think you could try this:

# get object for target workspace
$workspace = Get-PowerBIWorkspace -Name $workspaceName

# get object for new dataset
$dataset = Get-PowerBIDataset -WorkspaceId $workspace.Id | Where-Object Name -eq $datasetName

# get object for new SQL datasource
$datasource = Get-PowerBIDatasource -WorkspaceId $workspace.Id -DatasetId $dataset.Id

# parse REST to determine gateway Id and datasource Id
$workspaceId = $workspace.Id
$datasetId = $dataset.Id
$datasourceUrl = "groups/$workspaceId/datasets/$datasetId/datasources"

# execute REST call to determine gateway Id, datasource Id and current connection details
$datasourcesResult = Invoke-PowerBIRestMethod -Method Get -Url $datasourceUrl | ConvertFrom-Json

# parse REST URL used to patch datasource credentials
$datasource = $datasourcesResult.value[0]
$gatewayId = $datasource.gatewayId
$datasourceId = $datasource.datasourceId
$sqlDatabaseServerCurrent = $datasource.connectionDetails.server
$sqlDatabaseNameCurrent = $datasource.connectionDetails.database

[DemiHaung]

Hi @Anonymous 

how di you address the issue, cause right now im also experiencing the same issue.

Thanks

[Anonymous]

Hi @Anonymous ,

Did you check the considerations and limitations at the end of the article?

Here are some tips to try on the device:

1.Check the Requested URL
2.Clear Relevant Cookies
3.Clear the Cache
4.Log Out and Log In
5.Debugging Common Platforms
6.Rollback Recent Upgrades
7.Uninstall New Extensions, Modules, or Plugins
8.Check for Unexpected Database Changes
9.Confirm Proper File Permissions

Best Regards,

Stephen Tao

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

[Anonymous]

Anyone?