Forum Discussion
Anonymous
1 year agoNot applicable
Service Principal Credential Configuration Error: PowerBiNotAuthorized
Hi. I have a similar problem to the following forum posted by akarkal : Solved: Update DataSet Credentials using Service Principal - Microsoft Fabric Community Similarly, I am trying to update data...
- Anonymous1 year ago
Here is the script that finally worked for me. I was suggested to get the OAuth2 access token in some other way and I did it that way. I am not sure how to tell from the power bi service if the data source is actually authenticated. However, I am assuming that it is authenticated because the script does not output an error.
# Set execution policy for Task Scheduler compatibility Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass $tenantId = '' $vaultName = "" $secretName = "" $applicationId = "" # Power BI Application Authentication $secret = Get-AzKeyVaultSecret -VaultName $vaultName -Name $secretName -AsPlainText $securePassword = $secret | ConvertTo-SecureString -AsPlainText -Force $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $securePassword Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenantId # Get PAT (through OAuth2 client credentials flow) $clientId = $applicationId $clientSecret = $secret $scope = "2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default" $body = @{ client_id = $clientId scope = $scope client_secret = $clientSecret grant_type = "client_credentials" } $response = Invoke-RestMethod -Method Post ` -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" ` -ContentType "application/x-www-form-urlencoded" ` -Body $body $accessToken = $response.access_token # ---------------- MAIN SCRIPT ---------------- $datasetId = "" $workspaceId = "" $datasources = Get-PowerBIDatasource -DatasetId $datasetId -WorkspaceId $workspaceId $datasource = $datasources[0] # # if no gateway, stop if (-not $datasource.GatewayId -or -not $datasource.DatasourceId) { Write-Host "No datasource found or no gateway associated with the datasource. Exiting script." return } $gatewayId = $datasource.GatewayId $datasourceId = $datasource.DatasourceId # Take Over Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.TakeOver") -Method Post -ErrorAction Stop # Bind to Gateway (because after taking over, the dataset is not bound to the same gateway) $body = @{ "gatewayObjectId" = $gatewayId "datasourceObjectIds" = @($datasourceId) } | ConvertTo-Json -Depth 10 Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.BindToGateway") ` -Method Post ` -Body $body ` -ContentType "application/json" ` -ErrorAction Stop # Reget the datasource after taking over and binding to gateway (this is necessary because the datasource might change after these operations) $datasources = Get-PowerBIDatasource -DatasetId $datasetId -WorkspaceId $workspaceId $datasource = $datasources[0] $datasourceId = $datasource.DatasourceId $gatewayId = $datasource.GatewayId $body = @{ credentialDetails = @{ credentials = "{""credentialData"": [{""name"": ""accessToken"", ""value"": ""$accessToken""}]}" credentialType = "OAuth2" encryptedConnection = "Encrypted" encryptionAlgorithm = "None" privacyLevel = "Public" useEndUserOAuth2Credentials = $false useCallerAADIdentity = $false } } | ConvertTo-Json -Depth 10 # Update the datasource with the new credentials Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/gateways/$($gatewayId)/datasources/$($datasourceId)") -Method PATCH -Body $body -ErrorAction Stop -ContentType "application/json"
Anonymous
1 year agoNot applicable
Here is the script that finally worked for me. I was suggested to get the OAuth2 access token in some other way and I did it that way. I am not sure how to tell from the power bi service if the data source is actually authenticated. However, I am assuming that it is authenticated because the script does not output an error.
# Set execution policy for Task Scheduler compatibility
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
$tenantId = ''
$vaultName = ""
$secretName = ""
$applicationId = ""
# Power BI Application Authentication
$secret = Get-AzKeyVaultSecret -VaultName $vaultName -Name $secretName -AsPlainText
$securePassword = $secret | ConvertTo-SecureString -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $securePassword
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenantId
# Get PAT (through OAuth2 client credentials flow)
$clientId = $applicationId
$clientSecret = $secret
$scope = "2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default"
$body = @{
client_id = $clientId
scope = $scope
client_secret = $clientSecret
grant_type = "client_credentials"
}
$response = Invoke-RestMethod -Method Post `
-Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" `
-ContentType "application/x-www-form-urlencoded" `
-Body $body
$accessToken = $response.access_token
# ---------------- MAIN SCRIPT ----------------
$datasetId = ""
$workspaceId = ""
$datasources = Get-PowerBIDatasource -DatasetId $datasetId -WorkspaceId $workspaceId
$datasource = $datasources[0]
# # if no gateway, stop
if (-not $datasource.GatewayId -or -not $datasource.DatasourceId) {
Write-Host "No datasource found or no gateway associated with the datasource. Exiting script."
return
}
$gatewayId = $datasource.GatewayId
$datasourceId = $datasource.DatasourceId
# Take Over
Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.TakeOver") -Method Post -ErrorAction Stop
# Bind to Gateway (because after taking over, the dataset is not bound to the same gateway)
$body = @{
"gatewayObjectId" = $gatewayId
"datasourceObjectIds" = @($datasourceId)
} | ConvertTo-Json -Depth 10
Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.BindToGateway") `
-Method Post `
-Body $body `
-ContentType "application/json" `
-ErrorAction Stop
# Reget the datasource after taking over and binding to gateway (this is necessary because the datasource might change after these operations)
$datasources = Get-PowerBIDatasource -DatasetId $datasetId -WorkspaceId $workspaceId
$datasource = $datasources[0]
$datasourceId = $datasource.DatasourceId
$gatewayId = $datasource.GatewayId
$body = @{
credentialDetails = @{
credentials = "{""credentialData"": [{""name"": ""accessToken"", ""value"": ""$accessToken""}]}"
credentialType = "OAuth2"
encryptedConnection = "Encrypted"
encryptionAlgorithm = "None"
privacyLevel = "Public"
useEndUserOAuth2Credentials = $false
useCallerAADIdentity = $false
}
} | ConvertTo-Json -Depth 10
# Update the datasource with the new credentials
Invoke-PowerBIRestMethod -Url ("https://api.powerbi.com/v1.0/myorg/gateways/$($gatewayId)/datasources/$($datasourceId)") -Method PATCH -Body $body -ErrorAction Stop -ContentType "application/json"