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.