Today’s script took a long time to write. The concepts are fairly simple and the resulting script is quite trivial but the lack of easy to find documentation on Reporting Services administration via scripts meant I got to spend a lot of time with my good friend intellisense. In fact, it actually led me to go out and download PowerGUI and start using it again just to get intellisense.
Now that I am done whining about how difficult it was, let’s talk about the script. Ever had to change the login information for all reports in a particular folder? In my case I was going from SQL authentication to Windows authentication for 4 folders with 15-20 reports each. I had done this before, manually, but I broke a few reports and it took hours to complete. Having to do the whole thing a second time meant a script was in order.
The script below is pretty simple and could easily be turned into a function. It takes a target server, target folder, user name to change to and password to assign. I have the script hard-coded to use Windows Credentials because that is all I have tested with. With that information the script walks through the folder and adjusts the first data source on each report. If you have multiple data sources then you will want to add code to handle that. That is it. (I told you the result is deceptively simple.)
Here is the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | CLS [string] $message = "" $reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService" foreach($report in $reports) $message = "{0}: Updating UserId and Password for the following report: '{1}'." -f $(get-date -displayhint DateTime), $report.Path Write-Host $message $dataSource = $reporting.GetItemDataSources($report.Path)[0] $dataSource.Item.WindowsCredentials = $true $dataSource.Item.UserName = $userName $dataSource.Item.Password = $password $reporting.SetItemDataSources($report.Path, $dataSource) $message = "{0}: Update completed." -f $(get-date -displayhint DateTime) Write-Host $message Write-Host "" |
So that’s it. I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
