Powershell API Update datasource

[KoenVdB]

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? 

[Anonymous]

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 ! 

[KoenVdB]

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?

[Anonymous]

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

Thank you very much @KoenVdB  !

[Anonymous]

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.... 

[Jayendran]

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

[KoenVdB]

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

[Jayendran]

Hi @KoenVdB ,

Please see this similar thread

https://community.powerbi.com/t5/Developer/quot-Operation-is-not-supported-for-selector-quot-when-us...

[gauravkh]

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