Forum Discussion
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...
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
$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"