Hi There,
I have an on-premise PowerBI Report Server and have backed up and restored the production database to a test server. I want to update the connection strings on the test server so the data sources point to the database on the test server.
My code is as follows
$ReportServerURI = "http://MyServer/Reports"
$powerBIs = Get-RsFolderContent -RsFolder '/Path/To/My/Folder' -ReportServerUri $ReportServerURI -Recurse | Where-Object -Property "Name" -EQ "My Report";
$pbi = $powerBIs[0]
$rds = Get-RsRestItemDataSource -WebSession $session -RsItem $pbi.Path | Where-Object {$_.DataModelDataSource.Kind -eq "SQL"}
$rds[0].ConnectionString = "myNewServer;DatabaseName"
Set-RsRestItemDataSource -WebSession $session -RsItem $pbi.Path -DataSources $rds -RsItemType PowerBIReport;
If I run the code as far as the first Set-RsRestItemDataSource command, and then do the following:
- navigate to /Path/To/My/Folder in the web GUI
- Click the Elipses
- Click Manage
- Click Data Sources
I can see the connection string has been updated for the data source my code updated. However, if I click "Test Credentials" for that data source, it fails (despite those credentials having access to DatabaseName on myNewServer)
I can update the credentials manually and then the test succeeds.
If I then create a new scheduled refresh plan and click "Refresh Now" I get an error:
[0] -1055784932: Credentials are required to connect to the SQL source. (Source at myOldSever;DatabaseName.). The exception was raised by the IDbCommand interface.
[1] -1055129594: The current operation was cancelled because another operation in the transaction failed.
it appears that the scheduled refresh is still using the original connection string I just updated. How can I get the scheduled refresh plan to use the new updated connection string?
