Syptoms
When running reports in SCCM I kept getting incorrect user name or password errors for the default SCCM SSRS data source despite all accounts and passwords relating to the SCCM Reporting Services Point, the SSRS configuration and SQL Server being correct.
Environment
- 1 x Standalone Primary Site Server
- 1 x server running SQL Server for the SCCM and SSRS databases
- 1 x server with SSRS installed
- All servers Windows Server 2019
- SQL Server 2017
- SSRS 2017
- Clean SCCM 1906 install
- SCCM Reporting Services installed by the book following the link below and previous experience of doing the install countless times without any issues.
https://docs.microsoft.com/en-us/sccm/core/servers/manage/configuring-reporting
Spent two days trying to fix including reinstall and got nowhere. In desparation tried to delete and recreate the default SCCM SSRS data source and that failed as well (data source already exists error when trying to reuse the default data source name) but it did unlink the default data source from all the canned SCCM reports.
Thought I now had two problems but the script below not only reattached the data source to the canned reports it also fixed the dodgy user name and password issue.
All has been fine since though I did need to run multiple times against each report folder, after changing the folder name in the script (sure there is a way to script this but I am happy enough doing this as-and-when I want to use a particular set of reports!).
#Set variables
#Enter the name of the SSRS server used by SCCM in the next line – in my case the server name is cmrs6svr
$reportserver = “cmrs6svr”;
#URL to connect to the SSRS server
$url = “http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL”;
#Provide new data source path. In this case I am reapplying the default SCCM data source to fix the issue discussed in this post
$newDataSourcePath = “/ConfigMgr_WR6/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}”
#Provide new Data source Name which is part of above source path
$newDataSourceName = “{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}”;
# SSRS report folder path that contains the reports to change the datasource. In this case the default SCCM folder is called ‘ConfigMgr_WR6’
# and the new datasource is being written to all the reports in the ‘Software Updates – E Troubleshooting’ folder
$reportFolderPath = “/ConfigMgr_WR6/Software Updates – E Troubleshooting”
#————————————————————————
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential
$reports = $ssrs.ListChildren($reportFolderPath, $false)
$reports | ForEach-Object {
$reportPath = $_.path
Write-Host “Report: ” $reportPath
$dataSources = $ssrs.GetItemDataSources($reportPath)
$dataSources | ForEach-Object {
$proxyNamespace = $_.GetType().Namespace
$myDataSource = New-Object (“$proxyNamespace.DataSource”)
$myDataSource.Item = New-Object (“$proxyNamespace.DataSourceReference”)
$myDataSource.Item.Reference = $newDataSourcePath
$_.item = $myDataSource.Item
$ssrs.SetItemDataSources($reportPath, $_)
Write-Host “Report’s DataSource Reference ($($_.Name)): $($_.Item.Reference)”;
}
Write-Host “————————“
}
Thanks to Tim at ask.sqlservercentral.com is required, as well as Eswar at eskonr.com – more info using the links below.