Getting error using Power Shell Script to update data sources.

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

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 rajeshlohar1974 

You are facing an UnknownError while updating the data source credentials in Power BI using a PowerShell script within an Azure DevOps CI/CD pipeline. The issue is likely related to service principal permissions, incorrect dataset references, or an invalid JSON request body structure. First, ensure the service principal has the necessary API permissions in Azure AD, such as Dataset.ReadWrite.All, Tenant.ReadWrite.All, and Workspace.ReadWrite.All, and that Admin consent has been granted. Additionally, in the Power BI Admin Portal, enable service principal access under Tenant Settings and confirm that the service principal is assigned as a Member or Admin in the target workspace.

Another common issue is incorrect dataset and workspace references in your script. You need to correctly assign $datasetId = $dataset1.Id instead of using an undefined variable $dataset. Also, verify if $gatewayId and $datasourceId are populated by printing their values before making the API call. If they are empty, the dataset might not be properly mapped to a gateway. Furthermore, check the JSON request body structure—it appears to be missing a comma after "credentials", which could be causing the API to reject the request. You can restructure it by ensuring the credential details follow Power BI’s expected format.

To debug further, manually test the Power BI API by running Invoke-PowerBIRestMethod with a GET request to check if the dataset and gateway are correctly linked. If the error persists, confirm that the dataset is configured to use a gateway (if required) and that the workspace settings allow external connections. By addressing these potential issues, you should be able to resolve the error and successfully update the data source credentials in your Power BI deployment process.

Hi rajeshlohar1974 ,

Please check and confirm if the following info is OK:

• Make sure the service principal have admin access to the workspace where the dataset resides
• Verify the variable $gatewayId and $datasourceId are correct
• The REST API has the proper permission:Datasets - Update Datasources In Group - REST API  

After confirmed the above info is OK, please update the codes as below:

$SecuredApplicationSecret = ConvertTo-SecureString -String $clientsec1 -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($applictionId, $SecuredApplicationSecret)
$sp = Connect-PowerBIServiceAccount -ServicePrincipal -Tenant $tenantId -Credential $credential

$workspace = Get-PowerBIWorkspace -Name $workspacename
$dataset1 = Get-PowerBIDataset -WorkspaceId $workspace.Id | Where-Object {$_.name -eq $reportName}

$workspaceId = $workspace.Id
$datasetId = $dataset1.Id  # Corrected variable name

Write-Host "Workspace ID: $workspaceId"
Write-Host "Dataset ID: $datasetId"

$datasources = Get-PowerBIDatasource -WorkspaceId $workspaceId -DatasetId $datasetId
Write-Host "Data Sources: $($datasources | ConvertTo-Json)"

foreach($datasource in $datasources) {
    $gatewayId = $datasource.gatewayId
    $datasourceId = $datasource.datasourceId
    $datasourePatchUrl = "gateways/$gatewayId/datasources/$datasourceId"

    Write-Host "Patching credentials for Data Source ID: $datasourceId"

    # HTTP request body to patch datasource credentials
    $patchBody = @{
        "credentialDetails" = @{
            "credentials" = @{
                "credentialData" = @(
                    @{ "name" = "username"; "value" = "$sqlUserName" },
                    @{ "name" = "password"; "value" = "$sqlUserPassword" }
                )
            }
            "credentialType" = "Basic"
            "encryptedConnection" = "Encrypted"
            "encryptionAlgorithm" = "RSA-OAEP"
            "privacyLevel" = "None"
        }
    }

    # Convert body contents to JSON
    $patchBodyJson = ConvertTo-Json -InputObject $patchBody -Depth 6 -Compress
    Write-Host "Patch Body: $patchBodyJson"

    # Execute PATCH operation to set datasource credentials
    try {
        Invoke-PowerBIRestMethod -Method Patch -Url $datasourePatchUrl -Body $patchBodyJson
        Write-Host "Successfully updated credentials for Data Source ID: $datasourceId"
    } catch {
        Write-Host "Error updating credentials: $_"
    }
}

Best Regards