Using Powershell to update On Premise PowerBI Datasource causes Connection Test to fail

Question

I have a script which updates PowerBI data sources on an on-premise PowerBI report server (script below is abridged for brevity) the script updates the connection string in all SQL datasources from $OldServerName to $NewServerName

The script below filters to just one report for the sake of testing. The real script loops through all reports from the root folder.

param ([Parameter(Mandatory=$true)]$ReportServerURI,
[Parameter(Mandatory=$true)]$OldServerName,
[Parameter(Mandatory=$true)]$NewServerName
);

$session = New-RsRestSession -ReportPortalUri $ReportServerURI;

# get all PowerBI reports
$powerBIs = Get-RsFolderContent -RsFolder '/MyFolder1/MyFolder2' -ReportServerUri $ReportServerURI -Recurse | Where-Object -Property "TypeName" -EQ "PowerBIReport"; #the real script starts at the root folder. I just restrict here to target one report for testing 

foreach ($pbi In $powerBIs | Where-Object {$_.Name -eq "MyReport"}) #again, this restriction to one report is just for testing
{
    # get all the datasources in the report
    $rds = Get-RsRestItemDataSource -WebSession $session -RsItem $pbi.Path;
    # if data sources have been found
    if ($rds -ne $null)
    {
        # loop through all the datasources 
        foreach ($d in $rds)
        {
            if ($d.ConnectionString.ToUpper().Contains($OldServerName.ToUpper()) -and $d.DataModelDataSource.Kind -eq "SQL")
            {
                $d.ConnectionString = $d.ConnectionString -replace $OldServerName, $NewServerName;              
                Write-Host ("$($d.id) updated") -ForegroundColor Green;
            };            
        };
    };   
    Set-RsRestItemDataSource -WebSession $session -RsItem $pbi.Path -DataSources $rds -RsItemType PowerBIReport;              
};

The script works and when I browse to /MyFolder1/MyFolder2/ in the web report manager, click manage for MyReport and then go to the datasources tab, I can see that the datasources are there, the SQL data source connection strings have been updated as hoped and the credentials are as they were before the update. However, When I click "Test Connection" I get the error

Log on failed. Ensure the user name and password are correct.

I can confirm that the connection succeeds before the update (although this is against $oldServerName).

The credentials for the SQL data sources are for a windows user and that Windows Login exists on the SQL Server $NewServerName and is a user in the database that the data source points to.

There are also some Excel data sources for the PowerBI report, which use the same windows user's credentials which, whilst not updated by the script, display the same behaviour (Connection Test succeeds before the script update but fails after)

If I re-enter the credentials manaually the test then succeeds, however, when I refresh the shared schedule via Manage --> Scheduled Refresh --> refresh now. The refresh fails and I get the following error

SessionID: 45944afc-c53c-4cca-a571-673c45775eab [0] -1055784932: Credentials are required to connect to the SQL source. (Source at OldServerName;Database.). The exception was raised by the IDbCommand interface. [1] -1055129594: The current operation was cancelled because another operation in the transaction failed. [2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

What am I missing? Is there something else I need to do? I am using PowerBI Report Server version October 2020

DarkLite1 DarkLite1 · Accepted Answer · 2021-01-22T08:21:08

I think the moment you execute Set-RsRestItemDataSource to modify the SQL DataSource Connection strings the password becomes invalid. This seems normal to me, as you don't want someone to modify the connection string and use it with someone else's credentials. So in a way this looks like a security feature and is behaving as desinged.

A possible workaround you could try is to set the credentials again:

Create a credential object with New-RsRestCredentialsByUserObject From the docs:

This script creates a new CredentialsByUser object which can be used when updating shared/embedded data sources.

Update the SQL DataSource connection with the new credentials object

Something like this in your case might work:

$newCredentialsParams = @{
  Username           = "domain\\username"
  Password           = "userPassword"
  WindowsCredentials = $true
}
$rds = Get-RsRestItemDataSource -WebSession $session -RsItem $pbi.Path
$rds[0].CredentialRetrieval = 'Store'
$rds[0].CredentialsByUser = New-RsRestCredentialsByUserObject @newCredentialsParams 

$setDataSourceParams = @{
  WebSession  = $session
  RsItem      = $pbi.Path
  DataSources = $rds
  RsItemType  = PowerBIReport
}
Set-RsRestItemDataSource @setDataSourceParams

Using Powershell to update On Premise PowerBI Datasource causes Connection Test to fail

1 Answers