Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
We are facing an issue while trying to update Power BI Data Source Credentials using PowerShell with a Service Principal.
When we authenticate using Connect-PowerBIServiceAccount (interactive login), we can successfully update the credentials. However, when using Service Principal authentication with Connect-PowerBIServiceAccount -ServicePrincipal -Credential $Credential, the update fails.
Here is the PowerShell code we are currently using to edit the Data Source Credentials:
# Log in to Power BI
$Secret = ****************
$AppID = ****************
$AppPassword = ConvertTo-SecureString $Secret -AsPlainText -Force
$Credential = New-Object PSCredential $AppID, $AppPassword
$TenantID = ****************
Connect-PowerBIServiceAccount -Tenant $TenantID -ServicePrincipal -Credential $Credential
$datasources = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/******-****-****-****-********/datasets/******-****-****-*..." -Method get | ConvertFrom-Json
foreach($datasource in $datasources.value)
{
# Update datasource API details
$GatewayId = $datasource.gatewayId
$DatasourceId = $datasource.datasourceId
$UpdateDatasourceUrl = "https://api.powerbi.com/v1.0/myorg/gateways/$GatewayId/datasources/$($DatasourceId)"
$GetGatewayUrl = "https://api.powerbi.com/v1.0/myorg/gateways/$($GatewayId)"
# Get the OAuth token (use your actual method to retrieve the token, shown above)
$resource = **********
$body = @{
client_id = $AppID
client_secret = $Secret
grant_type = "client_credentials"
resource = $resource
}
$tokenResponse = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$($TenantID)/oauth2/token" -ContentType "application/x-www-form-urlencoded" -Body $body
$accessToken = $tokenResponse.access_token
# create HTTP request body to patch datasource credentials
$patchBody = @{
"credentialDetails" = @{
"credentials" = "{\""credentialData\"":[{\""name\"":\""accessToken\"", \""value\"":\""$($accessToken)""}]}"
"credentialType" = "OAuth2"
"encryptedConnection" = "Encrypted"
"encryptionAlgorithm" = "None"
"privacyLevel" = "Organizational"
"useEndUserOAuth2Credentials" = $false
}
}
# Convert the body to JSON format
$jsonBody = $patchBody | ConvertTo-Json -Depth 5
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/gateways/$($GatewayId)/datasources/$($DatasourceId)" -Method PATCH -Body $jsonBody
}
Please refer to the below screenshot for the same,
We have checked that the Service Principal has all required permissions in Power BI Admin Portal and Azure AD. The Service Principal is also added to the necessary workspace and dataset.
We would greatly appreciate any guidance or suggestions on resolving this error in the PowerShell scripting process for updating the Data Source Credentials.
Thanks!
Hi @SamInogic ,
According to your error details, the error you are experiencing is “Gateway DataSourceAccessError”, which means that you are experiencing an error with gateway trying to access the data source using this datasource credential.
Can you use the same datasource credential to access the same datasource in the UI if it is convenient for you?
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
5 | |
4 | |
3 | |
2 | |
2 |
User | Count |
---|---|
8 | |
6 | |
4 | |
4 | |
4 |