This blog post is part of my “Automating Power BI deployments” series, and you can find a list of the other posts here.

Changing the credentials of a data source is very similar to what we’ve done with report parameters, but there are a few nuances we have to account for:

  • Our data set (report) may have multiple data sources, and they may be different types of data sources.
  • The API url to change data source credentials include the data source id, which means we cannot change all credentials with one API call as we’ve done with parameters.

For the purposes of this blog post, we’re going to assume that we want to change the credentials for all of the SQL Server data sources.

Parameters & Power BI Objects

We’ll need the following parameters as input to our PowerShell script:




Next we will need to retrieve the workspace, report and all of its data sources to use in the API call:

#Retrieve the workspace
$WorkspaceObject = (Get-PowerBIWorkspace -Scope Organization -Name $WorkspaceName)

#Retrieve the report
$PbiReportObject = (Get-PowerBIReport -Workspace $WorkspaceObject -Name $ReportName)

#Retrieve all data sources
$PbiDataSourcesObject = (Get-PowerBIDatasource -DatasetId $PbiReportObject.DatasetId -Scope Organization)

The API call

The method we need to use here is the Patch method. Why is it Patch and not Post like we did with the parameters? Great question, and the “devil is in the details”…the method dictates how the underlying resource or attribute is modified, and the Patch method in this case means that there are partial (and in-place) modifications to an existing resource. Read more about the differences between Post, Put and Patch here.

To wrap it all up we’ll loop through all of the data sources, build the API request and initiate the API call to change the credentials of each SQL Server source:

foreach ($DataSource in $PbiDataSourcesObject) { 

    #Store the data source id in a variable (for ease of use later)
    $DataSourceId = $DataSource.DatasourceId

    #API url for data source
    $ApiUrl = "gateways/" + $DataSource.GatewayId + "/datasources/" + $DataSourceId

    #Format username and password, replacing escape characters for the body of the request
    $FormattedDataSourceUser = $DataSourceUser.Replace("\", "\\")
    $FormattedDataSourcePassword = $DataSourcePassword.Replace("\", "\\")
    #Build the request body
    $ApiRequestBody = @"
            "credentialDetails": {
                "credentialType": "Basic", 
                "credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"$FormattedDataSourceUser\"},{\"name\":\"password\", \"value\":\"$FormattedDataSourcePassword\"}]}",
                "encryptedConnection": "Encrypted",
                "encryptionAlgorithm": "None",
                "privacyLevel": "Organizational"

    #If it's a sql server source, change the username/password
    if ($DataSource.DatasourceType = "Sql") {

        #Update username & password
        Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body ("$ApiRequestBody") 

        Write-Output "Credentials for data source ""$DataSourceId"" successfully updated..." `n

Pay special attention to the formatting we’re applying to the username an password. This is necessary because the \ is an escape character that’s already used in the body of the API call, and it is possible that our username or password contains this character…especially if we’re using a domain account for an on-premises server. Also note the privacy level (set to Organizational in this script) which you should change if your data source requires something different.

Want to download the PowerShell scripts to perform these actions? Get it from my GitHub repo.

5 thoughts on “Automating Power BI deployments: Change data source credentials

  1. Ho do update the server name in Data source credentials section

    1. Hi Mohammed, this API only allows you to change the credentials and not the name of the server. If you want to be able to update the server name without redeploying the data model, you should use a parameter and then refer to the post on updating parameters:

  2. PowerShellRocks says:

    Hi Martin,

    I know this is an old post but I will give it a try.

    I would like to know how did you manage to make it work. I have Service Principal. I am able to connect to PowerBI.
    I can get workspace, report, dashboard etc but without -Scope Organization. When I have Organization I get 401 Unauthorized. This one confuses me, but that’s not the main problem. Without -Scope Organization it works correctly.
    However, when I want to get a gateway via REST API I get 401 Unauthorized. Same when I want use PATCH method.
    Do you remember what kind of permissions you had on your App Registration? Or it was just PowerBI Admin Role assigned in Azure AD?

    1. You may have missed step 4 in the following guide:

      Also pay special attention to the APIs available with Service Principal at the end of the document.

Leave a Reply

%d bloggers like this: