Issue Updating Data Source Credentials via PowerShell Using Service Principal

Hi SamInogic​,

Since this thread got active again: the pattern "works with interactive login, fails with the service principal" is usually not about the credential payload. It is about who owns the semantic model.

Data source credentials are bound to the model owner. When you log in interactively you are (or can act as) the owner, so the PATCH succeeds. The service principal is a different identity, so it is updating credentials for a model it doesn't own, which fails or doesn't take effect. The fix is to let the SP take over the model first:

Connect-PowerBIServiceAccount -ServicePrincipal -Credential $Credential -Tenant $TenantID

$base = "groups/$WorkspaceId/datasets/$DatasetId"

# 1. SP becomes the owner of the semantic model
Invoke-PowerBIRestMethod -Method Post -Url "$base/Default.TakeOver"

# 2. read the bound data sources again AFTER the takeover
$sources = (Invoke-PowerBIRestMethod -Method Get -Url "$base/datasources" |
            ConvertFrom-Json).value

Then update each cloud data source. Note that credentials is a JSON string, not a nested object, and encryptionAlgorithm is "None" for cloud sources (no gateway key encryption needed):

foreach ($s in $sources) {
    $credentials = @{
        credentialData = @(
            @{ name = "username"; value = $SqlUser },
            @{ name = "password"; value = $SqlPassword }
        )
    } | ConvertTo-Json -Compress -Depth 5

    $body = @{
        credentialDetails = @{
            credentialType      = "Basic"
            credentials         = $credentials        # string!
            encryptedConnection = "Encrypted"
            encryptionAlgorithm = "None"
            privacyLevel        = "Organizational"
        }
    } | ConvertTo-Json -Depth 5

    Invoke-PowerBIRestMethod -Method Patch `
        -Url "gateways/$($s.gatewayId)/datasources/$($s.datasourceId)" `
        -Body $body
}

A few rules from the Update Datasource API and the Take Over API that tend to trip up service principal scripts:

SP needs Admin or Member on the workspace to take over
OAuth2 credentials can't be set by an SP for most sources
  -> use Basic, Key or ServicePrincipal credential types instead
on-premises gateway sources need the payload encrypted
  with the gateway's public key (the RSA details above)
cloud sources: encryptionAlgorithm = "None"

The DataSourceAccessError in your screenshot also fits this: the call reached the data source, but with credentials that weren't applied to the model the refresh actually uses.

So my overall approach would be:

SP takes over the semantic model (Default.TakeOver)
-> re-read bound data sources
-> PATCH each one with credentials as a JSON string
-> Basic / Key / ServicePrincipal types, not OAuth2
-> trigger a refresh to confirm

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

If it helps anyone still writing this by hand: the payload has to be encrypted the way Microsoft's SDK does it.

• 1024-bit gateway key: RSA-OAEP (SHA-1), in 85-byte pieces
• 2048-bit key: AES-256-CBC with HMAC-SHA256, both keys wrapped with RSA-OAEP (SHA-256)

There is a PowerShell port of it in GatewayLens, a free, open-source console for gateway admins that I wrote - see the CREDENTIAL ENCRYPTION section of GatewayLens.ps1: GatewayLens on GitHub

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.