CODEWITHSUNDEEP
powerbipowerbi-datasource
Joon
Joon

Reputation: 2147

Power BI Workspace - dataset credential patch receives 401 Unauthorized

I am trying to programatically deploy a Power BI Report and dataset from one workspace to another, using a mix of PowerShell and the PowerBI REST API. In the new workspace, I am updating a dataset parameters to point to a new DB name.

The dataset is pointed to an Azure SQL DB, and in my DEV workspace (the source for the clone), the dataset passes the accessing user's credential through to the DB.

I am authenticating with a Service Principal that I created and then added to the dataset as an Administrator.

This is the PowerShell code that I wrote to do this:

$config = gc .\EnvConfig.json -raw | ConvertFrom-Json

$envSettings = $config.Dev
$toEnvSettings = $config.QA

# Convert to SecureString
[securestring]$secStringPassword = ConvertTo-SecureString $config.ServicePrincipalSecret -AsPlainText -Force
$userId = "$($config.ServicePrincipalId)@$($config.ServicePrincipalTenant)"
[pscredential]$credObject = New-Object System.Management.Automation.PSCredential ($userId, $secStringPassword)

Connect-PowerBIServiceAccount -Tenant $config.ServicePrincipalTenantName -ServicePrincipal -Credential $credObject

Get-PowerBIReport -WorkspaceId $envSettings.PBIWorkspaceId | ForEach-Object {
    $filename ="c:\temp\$($_.Name).pbix"
    Remove-Item $filename
    Invoke-PowerBIRestMethod -Method GET `
        -Url "https://api.powerbi.com/v1.0/myorg/groups/$($envSettings.PBIWorkspaceId)/reports/$($_.Id)/Export" `
        -ContentType "application/zip" -OutFile $filename

    New-PowerBIReport -WorkspaceId $toEnvSettings.PBIWorkspaceId -ConflictAction CreateOrOverwrite -Path $filename
}

$datasets = Get-PowerBIDataset -WorkspaceId $toEnvSettings.PBIWorkspaceId
$datasetId = $datasets[0].Id

$updateDBParam = "{`"updateDetails`": [ { `"name`": `"DBName`", `"newValue`": `"$($toEnvSettings.DBName)`" }]}"
$updateUri = "https://api.powerbi.com/v1.0/myorg/groups/$($toEnvSettings.PBIWorkspaceId)/datasets/$datasetId/Default.UpdateParameters"
Invoke-PowerBIRestMethod -Method POST -Url $updateUri -Body $updateDBParam

When I have cloned the report and dataset, when I open the report in the new workspace I see an error that the dataset does not have credentials: Error on report header that the dataset does not have credentials

If I take over this dataset with my personal login, then the report loads. This is not sufficient, I want to set the credential to pass through the user's id programatically.

I found this discussion on the PowerBI site, where they say you can use the dataset ID and gateway ID from the dataset, and send a PATCH request to https://api.powerbi.com/v1.0/myorg/gateways/[gateway id]/datasources/[datasource id]

I suspect that is only relevant to "My Workspace" datasets, not datasets in a workspace.

When I try and send that patch request with a gateway and datasource ID that I got from performing a GET on https://api.powerbi.com/v1.0/myorg/groups/[workspace id]/datasets/[dataset id]/datasources, I get a 401 error. I have tried posting with my own PowerBI Tenant Admin login, as well as with an Admin app I created through the PowerBI app registration tool, and also I added a tenant level PowerBI Read / Write permission in the AAD portal for my service principal. Nothing works, I keep getting a 401.

Two questions:

  1. Can I set the credentials on a dataset in a workspace?
  2. If not, how can I clone the dataset between workspaces so that it has the credential passthrough to start with?

Upvotes: 0

Views: 845

Answers (2)

Greg
Greg

Reputation: 11

@Joon: I wanted to leave a comment but am not allowed. I'm in the same boat with getting the 401 errors. But I'm not following your resolution; did you change any logic, or you changed the user account being used? We're using the PBI AAD account that is the Admin of that workspace where dataset resides. Here's the code I'm using, which is based on this: https://martinschoombee.com/2020/10/20/automating-power-bi-deployments-change-data-source-credentials/

$ApiRequestBody = @"
    {
        "credentialDetails": {
            "credentialType": "Basic", 
            "credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"$FormattedDataSourceUser\"},{\"name\":\"password\", \"value\":\"$FormattedDataSourcePassword\"}]}",
            "encryptedConnection": "Encrypted",
            "encryptionAlgorithm": "None",
            "privacyLevel": "None"
        }
    }
"@
#. . . (tried other values for "privacyLevel")
#Update username & password
Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body ("$ApiRequestBody")

Upvotes: 1

Joon
Joon

Reputation: 2147

Solved the problem.

The 401 error was originating from the credential I was posting itself, not from me not having permissions to post. I was using the OAuth credential method, and the token I was passing was invalid. The response from the PowerBI API is just a bare 401 error, nothing tells the user that the problem is that the API validated the OAuth token and that failed.

I tested with an invalid basic credential, and in that case you get a 400 Bad Request error, which makes more sense.

Upvotes: 0

Related Questions