Getting error using Power Shell Script to update data sources.

[rajeshlohar1974]

We are developing CI/CD Pipeline in Azure DevOps. We are able to deploy PBIX to development workspace. Now we deployed the report on production workspace and using Power Shell Script to update data source. but getting error as below:

Invoke-PowerBIRestMethod : Encountered errors when invoking the command: {
"code": "UnknownError",
"pbi.error": {
"code": "UnknownError",
"parameters": {},
"details": []
}
}

 We are using service principal to authenticate and all the required permission is allowed. Below is the power shell script we are using:
$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 -Name $reportName
$dataset1 = Get-PowerBIDataset -WorkspaceId $workspace.Id | Where-Object {$_.name -eq $reportName}

$workspaceId = $workspace.Id
$datasetId = $dataset.Id
$workspaceId
$datasources = Get-PowerBIDatasource -WorkspaceId $workspaceId -DatasetId $datasetId
$datasources
foreach($datasource in $datasources) {
$gatewayId = $datasource.gatewayId
$datasourceId = $datasource.datasourceId
$datasourePatchUrl = "gateways/$gatewayId/datasources/$datasourceId"
Write-Host "Patching credentials for $datasourceId"
# HTTP request body to patch datasource credentials
$userNameJson = "{""name"":""username"",""value"":""$sqlUserName""}"
$passwordJson = "{""name"":""password"",""value"":""$sqlUserPassword""}"
$patchBody = @{
"credentialDetails" = @{
"credentials" = "{""credentialData"":[ $userNameJson, $passwordJson ]}"
"credentialType" = "Basic"
"encryptedConnection" = "Encrypted"
"encryptionAlgorithm" = "RSA-OAEP"
"privacyLevel" = "None"
}
}
# Convert body contents to JSON
$patchBodyJson = ConvertTo-Json -InputObject $patchBody -Depth 6 -Compress
# Execute PATCH operation to set datasource credentials
Invoke-PowerBIRestMethod -Method Patch -Url $datasourePatchUrl -Body $patchBodyJson
}

 Please suggest a solution.

[Poojara_D12]

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.

[Anonymous]

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

[rajeshlohar1974]

Hi @Anonymous ,

Thank you for the response. I verified the permissions and all the permission are already given to service principal. Also tried the above solution, but getting below error:
Invoke-PowerBIRestMethod : Encountered errors when invoking the command: {
"code": "BadRequest",
"message": "Bad Request",
"details": [
{
"message": "Invalid value",
"target": "datasourceDelta"
}
]
}

[Anonymous]

Hi @rajeshlohar1974 ,

The following links are the ones which has the similar requirement as yours, hope its solution can help you.

Solved: Update Datasource Credential Using Only Powershell... - Microsoft Fabric Community

PowerBI-Developer-Samples/PowerShell Scripts/EncryptGatewayCredentials.ps1 at master

# Basic Credentials
function EncryptBasicCredentials {
    param (
        [Parameter(Mandatory=$True,Position=1)]
        [String]$Username,
        [Parameter(Mandatory=$True,Position=2)] 
        [String]$PasswordAsString,
        [Parameter(Mandatory=$True,Position=3)] 
        [string]$GatewayExponent,
        [Parameter(Mandatory=$True,Position=4)] 
        [string]$GatewayModulus
    )
    
    # Create the objects to perform the necessary encryption on the credentials.  
    $gatewayKeyObj = [Microsoft.PowerBI.Api.Models.GatewayPublicKey]::new($GatewayExponent, $GatewayModulus)
    $credentialsEncryptor = [Microsoft.PowerBI.Api.Extensions.AsymmetricKeyEncryptor]::new($gatewayKeyObj)
    $basicCreds = [Microsoft.PowerBI.Api.Models.Credentials.BasicCredentials]::new($username, $PasswordAsString)

    # Construct the CredentialDetails object. The resulting "Credentials" property on this object will have been encrypted appropriately, ready for use in the request payload.
    $credentialDetails = [Microsoft.PowerBI.Api.Models.CredentialDetails]::new(
        $basicCreds, 
        [Microsoft.PowerBI.Api.Models.PrivacyLevel]::Organizational, 
        [Microsoft.PowerBI.Api.Models.EncryptedConnection]::Encrypted, 
        $credentialsEncryptor)
    
    # Construct the body for the API request.
    $body = @{
        credentialDetails = @{
            credentialType      = "Basic";
            credentials         = $credentialDetails.Credentials;
            encryptedConnection = "Encrypted";
            encryptionAlgorithm = "RSA-OAEP";
            privacyLevel        = "Organizational";
        }
    }
    $bodyJson = $body | ConvertTo-Json

    Write-Output $bodyJson
}

Best Regards

[rajeshlohar1974]

Hello @Anonymous ,

I tried the above, but still not working. 

I have a power bi report connected with an on-premises data source using on-premises data gateway. When I publish report to workspace, it is working well but when I publish report using CI/CD pipeline from Development to Production workspace, then I need to update datasource like database name and credentials. I am able to update database name and server name using /default.updateparameters API, but getting error in updating credentials. 

Also I found that gateway ID getting from dataset is different from gateway ID on Power BI, it seems it updates when parameters updated. 

Suggest possible solution.