Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Hi @steveearle86 , A few things:
Hi,
Thanks for your response. We are on version Version 1.9.7604.41261 (October 2020) at present. We have plans to upgrade to January 2021 but ironically, would like to solve this first if possible as it enables us to create a test environment.
As for your third point, that makes sense (and I thought that might be the case) I can allow for that in my script, however I need to understand why the refresh does not work after a manual update of the credentials first.
I have enabled the verbose log for our server, where does information get logged to?
For the January 2021 release, the verbose logging contains additional information in the logs for diagnosing scheduled refresh errors. In the same release we also fixed some scheduled refresh errors.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
7 | |
5 | |
4 | |
2 | |
2 |
User | Count |
---|---|
12 | |
7 | |
6 | |
2 | |
2 |