Update Data Source Password Power BI

It’s password rotation day at my company today and I discovered that some of our Power Bi report writers individually access each report on the Power Bi Server to change the password on each REPORT DATA SOURCE CONNECTION.

To help make this process a breeze I wrote up this little PowerShell script that can do the job.

It’s QUICK and SMALL and therefore has several limitations (not all listed):

1. Runs for a single User Login Name only.
2. Will run at the root level “/” but recommended to use at the folder level “/Myfolder.”

First, I’ll make sure I’ve installed the Reporting Services Module.

# Should only need to run below line on server once.
Install-Module ReportingServicesTools

I’ll click “YES TO ALL” on the popup during the installation.

Next, I create the script I will run.

$newPassword = 'MyPASSWORD'
$dsUserName = 'MyUSERNAME'
#$dsNewUserName = 'myOptionalNewUSER'
$folderName = '/myFOLDER'


$ReportName = Get-RsRestFolderContent -RsFolder $folderName -ReportPortalUri 'http://report01/pbireports' -Recurse

foreach ( $item in $ReportName) {
    $folderPath = $item.Path

    try {
        $dataSources = Get-RsRestItemDataSource -RsItem $folderPath -ReportPortalUri 'http://MYREPORTSERVER/pbireports'      
        $i = 0  
        foreach ( $ds in $dataSources ) {
         if ( $ds.DataModelDataSource.Username.ToString() -eq $dsUserName ) {
           
              # Optional New UserName line commented out.
              #$DataSources[$i].DataModelDataSource.UserName = $dsNewUserName
              $DataSources[$i].DataModelDataSource.Secret = $newPassword

              # Print to window to help debug/develop.
              $item.Path
              $DataSources[$i].DataModelDataSource.UserName

              # This CHANGES the PASSWORD.
              Set-RsRestItemDataSource -RsItem $item.Path -RsItemType 'PowerBIReport' -DataSources $DataSources -ReportPortalUri 'http://MYREPORTSERVER/pbireports'
                   $i = $i + 1
                }
            }
        }
    catch {
        $folderPath
    }    
}

Additional documentation for this may be found at: Change data source connection strings with PowerShell – Power BI Report Server pre-October 2020 – Power BI | Microsoft Learn