Martin's Blog

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:

param 
(    
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()] 
    [String]
    $WorkspaceName, 

    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()] 
    [String]
    $ReportName, 

    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()] 
    [String]
    $DataSourceUser,
     
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()] 
    [String]
    $DataSourcePassword
)

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.