Site icon Martin's Blog

Automating Power BI deployments: Change data source credentials

Martin Schoombee

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:

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.





 

	


	






		

			
				Exit mobile version