topic Re: Powershell API Update datasource in Developer

Powershell API Update datasource

KoenVdB — Tue, 29 Oct 2019 21:22:20 GMT

Hi,

we're creating poweshell script for the deployment of our datasets/reports to different environments.

Later will use this scripts in Azure DevOps for automatic deployment.

A ServicePrincipal is created for the deployments (Create service principle) . Deployment is working fine but we've an issue when we want to update the datasource.

We're connecting to a Azure SQL database.

Everytime we receive the error below when calling the command: "

Invoke-PowerBIRestMethod -Url "groups/$($destinationWorkspace.Id)/datasets/$($datasetid)/Default.UpdateDatasources" -Method POST -Body $NewConnectionBody | ConvertFrom-Json"

The value of the $NewConnectionBody looks like

Is there a solution that we can change the datasource using powershell?

Re: Powershell API Update datasource

gauravkh — Wed, 30 Oct 2019 04:35:57 GMT

Try using "Invoke-RestMethod" with complete URL
I suppose you are also adding "Content-Type" and header with "Authorization"

Re: Powershell API Update datasource

Jayendran — Wed, 30 Oct 2019 07:34:14 GMT

Hi @KoenVdB ,

Here the possible solutions to try

Did you add the SPN as an admin to the work space ?
Try using the fiddler to get the exact error message.
Did you Take Over the data set using the SPN ?- This requires if you need to modify the data sources in the dataset
You can look into my article which I gave the detailed step by step to achieve this

https://community.powerbi.com/t5/Community-Blog/PowerBI-CICD-using-Azure-DevOps/ba-p/769244

Re: Powershell API Update datasource

KoenVdB — Wed, 30 Oct 2019 12:42:10 GMT

Hi @Jayendran

The SPN had admin rights to the workspace, the spn has take over the data set.

i created the body that needs to be provide manually like you see below, and it looks oke. I also placed the result of the json below

when doing this fiddler is giving me the following message:

5B
{"error":{"code":"InvalidRequest","message":"Operation is not supported for selector #28"}}
0

Re: Powershell API Update datasource

Jayendran — Wed, 30 Oct 2019 13:05:00 GMT

Hi @KoenVdB ,

Please see this similar thread

https://community.powerbi.com/t5/Developer/quot-Operation-is-not-supported-for-selector-quot-when-using/td-p/372441

Re: Powershell API Update datasource

Anonymous — Wed, 30 Oct 2019 14:03:59 GMT

Would you mind sharing how you manage to get the Bearer token using Service Principal and Powershell?

I have created a SP, added it as admin to the workspace and also added the requiered delegated permissions in Azure (Power BI Service - https://analysis.windows.net/powerbi/api/)

I can use the .Net nuget client package to do work on the workspace, updating reports/datasets and so on. But I can't manage to get the authorization token when I use either powershell, Invoke-RestMethod, or Postman.

function getBearer([string]$TenantID, [string]$ClientID, [string]$ClientSecret) { $TokenEndpoint = {https://login.microsoftonline.com/{0}/oauth2/token} -f $TenantID #$ARMResource = "https://analysis.windows.net/powerbi/api/"; $ARMResource = "https://api.powerbi.com/"; $Body = @{ 'resource'= $ARMResource 'client_id' = $ClientID 'grant_type' = 'client_credentials' 'client_secret' = $ClientSecret } $params = @{ ContentType = 'application/x-www-form-urlencoded' Headers = @{'accept'='application/json'} Body = $Body Method = 'Post' URI = $TokenEndpoint } $token = Invoke-RestMethod @params Return "Bearer " + ($token.access_token).ToString() }

That's how I try to acquire the token but I keep getting "AADSTS500011: The resource principal named https://api.powerbi.com/ was not found in the tenant named ... " (It's the same with https://analysis.windows.net/powerbi/api/) however https://management.azure.com/ returns a bearer token.

Thank you in advance !

Re: Powershell API Update datasource

KoenVdB — Wed, 30 Oct 2019 15:27:14 GMT

Hi @Anonymous

i'm not using bearer token.

But maybe this can help you, this is how i connect:

$PowerBIServiceApplicationKey ="xxx" $PowerBIServiceApplicationID ="xxx" $TenantID = "yyy" #Install Power Bi Modules foreach ($moduleName in @("MicrosoftPowerBIMgmt.Reports", "MicrosoftPowerBIMgmt.Workspaces", "MicrosoftPowerBIMgmt.Profile","MicrosoftPowerBIMgmt.Data")) { if ((Get-Module $moduleName) -eq $null) { Write-Host "Installing $moduleName" Install-Module -Name $moduleName -Force } } securedPassword = $PowerBIServiceApplicationKey | ConvertTo-SecureString -asPlainText -Force $credential = New-Object System.Management.Automation.PSCredential($PowerBIServiceApplicationID,$securedPassword) #Connect to PowerBI Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -Tenant $TenantID

Maybe u can use the command

Get-PowerBIAccessToken

to retrieve what you need after the connect?

Re: Powershell API Update datasource

Anonymous — Thu, 31 Oct 2019 06:24:42 GMT

Of course , why I didn't try with the actual Power BI packages is beyond me...

Thank you very much @KoenVdB !

Re: Powershell API Update datasource

Anonymous — Fri, 15 Nov 2019 13:00:39 GMT

So apparantly the trailing slash in the resource URL was the problem, removed it and now I get the token back. So "https://analysis.windows.net/powerbi/api/" didn't work but "https://analysis.windows.net/powerbi/api" works.

Now that I know that of course I notice the Microsoft guide doesn't have a trailing slash but the URL where you add permissions to the service principal have the trailing slash.

A bit frustrated that I didn't notice/tested this but I simply didn't even notice/think about the slash....