Powershell Rest Api - Taking over dataset and updating datasource

[heylumi]

Hey guys

Just wondering if anyone has any idea why the very last line in the below powershell script would fail assuming that that one is able to get a valid GatewayId and datasourceId? I know there are potentially many reasons but wondering if there are some obvious ones to look out for  potentially?

For background - I'm attempting the call using a service principal which has admin access to the gateway (on-premise SQL source) and workspace in question. 

I came across the code in a PBI Dev Camp video (link below) and tried modifying the body used in the post request to closely match the microsoft documentation but with no luck so far. The actual code used features at around 57:10 in the below video.

PowerBI Dev Camp Video 

#These are populated earlier in the script and seem to contain values so everything up until this point seems fine,

$datasource = $datasourceResult.value[0]
$GatewayId = $datasource.GatewayId
$datasourceId = $datasource.DatasourceId
$datasourcePatchURL = "gateways/$GatewayId/datasources/$datasourceId"

#I've attempted playing with the encryptedConnection and encryptionAlgorithm values but no luck
$patchBody = @{
"credentialDetails"=@{
"credentialType" = "Basic"
"credentials" = "{""credentialData"":[{""name"":""username"",""value"":""XXX""},{""name"":""password"",""value"":""XXX""}]}"
"encryptedConnection"="Encrypted"
"encryptionAlgorithm"="RSA-OAEP"
"privacyLevel"="Organizational"
"useEndUserOAuth2Credentials"="False"
}
}

$patchBodyJson = ConvertTo-Json -InputObject $patchBody -Depth 6 -Compress
Write-Host $patchBodyJson |fl

#This is the rest call that fails and am struggling to find a meaningful error message
Invoke-PowerBIRestMethod -Method Patch -Url $datasourcePatchURL -Body $patchBodyJson

Thank you for any assistance!

[v-rzhou-msft]

Hi @heylumi 

Use Invoke-PowerBIRestMethod function and add the URL you want.

For example:

Use Rest API to get Groups by Powershell.

...
Invoke-PowerBIRestMethod -Url 'Groups/GroupID...' -Method Get

Invoke-PowerBIRestMethod

You may refer to this video for more details about how to call Power BI Rest API by Powershell.

Best Regards,
Rico Zhou

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

[heylumi · Accepted solution]

Hey @v-rzhou-msft 

Thank you very much for your input. I was largely able to make rest api calls using the Invoke-PowerBIRestMethod so this in itself wasn't the problem. I think my issue mostly related to a lack of understanding on how to programatically make use of an existing gateway to do the refresh via the service principal which has a couple of gotchas that were not immediately obvious (in my case at least).

It turns out that the service principal that was added to a 365 group (group was granted access to the datasource via the PowerShell UI) didn't actually have access to the datasource in the gateway even though the group was added and appears in the list of users. I needed to add the service principal account directly to get it working as expected using the Datagateway commandlets via Powershell 7 (this was the gotcha for me)

There is another video by Guy In A Cube which explains the process rather well if anyone is interested in getting this working. See here: PowerShell Service Principal Refresh