Issue Updating Data Source Credentials via PowerShell Using Service Principal

[SamInogic]

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!

[v-yangliu-msft]

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.