cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ilav
Regular Visitor

Can't select Maps To in Gateway setting via API/Powershell

Hello - I have a dataset which successfully connects to an azure DB via gateway when configured manually. However, when trying to configure the dataset via powershell, unable to select the gateway in 'Maps To' dropdown under the Gateway connection section of the DataSet resulting in the refresh failing with this error "It looks like scheduled refresh failed because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources.". 

 

Note that the credentials are configured in gateway and did not have to be specified in each dataset. 

 

If anyone have used API/Powershell script to configure the 'maps to' successfully please share your thoughts. Have used below API's to configure dataset so far. 

 

Update DataSource - https://api.powerbi.com/v1.0/$groupsPath/datasets/$sourceDatasetId/Default.UpdateDatasources

Bind to Gateway - https://api.powerbi.com/v1.0/$groupsPath/datasets/$sourceDatasetId/BindToGateway

Refresh DataSet - https://api.powerbi.com/v1.0/$groupsPath/datasets/$sourceDatasetId/refreshes

 

6 REPLIES 6
ilav
Regular Visitor

If using Azure DevOps for deployment, in addition to using BindToGateway API, you would want to use a service account (an Azure 360 account and NOT Service Principal) that is a user on the gateway for bind and refresh to work. You need bearer token of that account to be able to accomplish the steps. This is because ServicePrincipals can not be added to powerbi gateways.

 

Copy below snippet to a PS file and pass in required parameters to bind gateway and refresh dataset. clientId is the Azure ServicePrincipal associated with your PowerBi workspace and pbiUserName is the sevice account that's a user on the gateway. Hope this helps!

 

param(
    
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $tenantId,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $clientId,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $pbiUsername,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $pbiPassword,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $groupID,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [String] $sourceDatasetName,
    
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $gatewayId,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $dataSourceId

)

Install-Module -Name MicrosoftPowerBIMgmt.Profile -Scope CurrentUser -Force
Import-Module AzureRm.Profile

####################################################################################################
# Get Auth Token and build Rest API header with authorization token
$body = @{
  "client_id" = $clientId;
  "grant_type" = "password";
  "username" = $pbiUsername;
  "password" = $pbiPassword;
  "scope" = "openid"
}
Write-Host $body

$authResponse = Invoke-RestMethod -Uri $authUrl -Method POST -Body $body
Write-Host "authResponse = $authResponse"

$Token = $authResponse.access_token
Write-Host "Token = $Token"

$AuthHeader = @{"Authorization" = "Bearer $Token"}
Write-Host "AuthHeader = $AuthHeader" 

####################################################################################################
# Get list of datasets from the workspace\group
Write-Host "sourceDatasetName = " $sourceDatasetName
$datasetsUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/datasets"
$datasets = Invoke-RestMethod -Headers $AuthHeader -Method 'GET' -Uri $datasetsUri -Verbose
$datasetsresponse = $response | ConvertTo-JSON
Write-Host $datasetsresponse

foreach ($dataset in $datasets.value)
{
  if ($dataset.name -eq $sourceDatasetName)
  { 
    $sourceDatasetId = $dataset.id
  }
}

Write-Host "dataset id to be used = " $sourceDatasetId

####################################################################################################
# Take Over Dataset
Write-Host "Start - Take over dataset"
  $takeOverUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/datasets/$sourceDatasetId/Default.TakeOver"
  Invoke-RestMethod -Headers $AuthHeader -Method 'POST' -Uri $takeOverUri -Verbose
Write-Host "End - Take over dataset"

####################################################################################################
# Bind to Gateway
Write-Host "Start - Bind to Gateway"
$bindBody = 
@"
{
    "gatewayObjectId" : "$gatewayId",
    "datasourceObjectIds" : [
     "$dataSourceId"
  ]
}
"@
Write-Host $bindBody
$bindUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/datasets/$sourceDatasetId/Default.BindToGateway"
Invoke-RestMethod -Headers $AuthHeader -Method 'POST' -Body $bindBody -Uri $bindUri -ContentType 'application/json' -Verbose
Write-Host "End - Bind to Gateway"

####################################################################################################
# Refresh dataset
Write-Host "Start - Refresh Dataset"
$refreshBody = @{"notifyOption" = "MailOnFailure"}
$refreshUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/datasets/$sourceDatasetId/refreshes"
Write-Host $refreshUri
Invoke-RestMethod -Headers $AuthHeader -Method 'POST' -Body $refreshBody -Uri $refreshUri -ContentType 'application/json' -Verbose
Write-Host "End - Refresh Dataset"

 

 

cwilke
Frequent Visitor

I was able to select the Map To field with the following:

 

# TRY TO BIND
$bindUrl = 'datasets/<ReplaceWithYourGatewayId>/Default.BindToGateway'
 
$postBindParams = @{
    gatewayObjectId = "yourGatewayId"
    datasourceObjectIds =@(
        "yourDataSourceId"
    )
} | ConvertTo-Json

write-host $postBindParams
Invoke-PowerBIRestMethod -Url $bindUrl -Method Post -Body $postBindParams -ContentType $content -Verbose
cwilke
Frequent Visitor

I have the same problem.

 

I publish our "Golden Data Set" to a workspace.

I am able to update the report Data Set Parameters "dbName" and "dbServer" using the API.

 

This enables the Gateway, but the "Map To" field does not select the server by default.

You have to manually select the server after the dbName and dbServer fields are updated.

 

I need a way to select the Data Source in the Map To dropdown using the API.

 

mbutler71
Frequent Visitor

Having the same issue, anyone else able to solve this?

DevOps deploys the "Report" Dataset successfully.
DevOps updates the datasource in the "Report" Dataset successfully.

The Gateway Connection in the "Report" Dataset loses it's mapping to a 'datasource' in the On-Premise Gateway.

 

mbutler71_0-1623160340007.png

 

 

cwilke
Frequent Visitor

Hi mbutler71,

 

I was able to select the Map To field with the following:

 

# TRY TO BIND
$bindUrl = 'datasets/<ReplaceWithYourGatewayId>/Default.BindToGateway'
 
$postBindParams = @{
    gatewayObjectId = "yourGatewayId"
    datasourceObjectIds =@(
        "yourDataSourceId"
    )
} | ConvertTo-Json

write-host $postBindParams
Invoke-PowerBIRestMethod -Url $bindUrl -Method Post -Body $postBindParams -ContentType $content -Verbose
RakeshSinghr
Resolver I
Resolver I

Its frustrating to say the least but i havent been able to use bind to gateway api via App Id either...

Did you manage to do this?

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors