Tag Archives: Reporting Services Point

SCCM

SCCM Reports Fail to Run – Incorrect User Name or Password Error for Default Data Source

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.

http://eskonr.com/2014/04/sccm-configmgr-2012-how-to-change-custom-data-source-to-shared-data-source-for-multiple-ssrs-reports/

https://ask.sqlservercentral.com/questions/86369/change-datasource-of-ssrs-report-with-powershell.html

SCCM

Creating or Editing a SCCM 2012 R2 Custom Report

Not a great fan of reporting in SCCM 2012 as it just seems so much more hassle than it was with the ASP reports in SCCM 2007.  Below is my preferred method of creating or editing custom reports in SCCM 2012 R2.

First Use
  • Ensure the Reporting Service Point (RSP) is installed and configured.
  • Access the RSP server using the link, e.g. http://sccmserver/reports.
  • Open the default folder – probably called ConfigMgr_ABC where ABC is the SCCM Site name.
  • Create a new folder called ABC Custom Reports.
  • In the SCCM console access the Reporting node in the Monitoring workspace.
  • Right-click on any report and choose Edit.  This will force the Report Builder tool to download to your user profile.
  • Don’t do anything, just close Report Builder.

Create a Run As Administrator Shortcut to Report Builder

  • Create a shortcut to the MSReportBuilder executable now buried in your profile here: C:\Users\username\AppData\Local\Apps\2.0\WMN9W578.6MY\4ZPA1R15.EH1\repo..tion_c3bce3770c238a49_000c.0000_f828cd3ef9610d16\MSReportBuilder.exe – not sure if the naming is consistent but it will be under the 2.0 folder somewhere.
  • Access the properties of the shortcut, click the Advanced button and tick the Run As Administrator check-box.

Creating a New Report

  • Create/obtain the SQL query to be used for the report.
  • Open Report Builder using the shortcut.
  • From the Getting Started screen select New Report on the left and Table or Matrix from the right.
  • Ensure Create a dataset is selected at the bottom and click Next.
  • The default data source should be highlighted, if not browse to it under the ConfigMgr_ABC folder (it’s listed after all the report folders).  Click Next.
  • In the Design window click Edit as text.
  • Paste the SQL in the text box and test using the execute button (the red exclamation mark).
  • Assuming the SQL syntax is OK click Next.
  • Select all the fields listed in the Available fields box and drag to the Values box.
  • Click Next, Next and Finish.
  • Adjust the column widths etc. to suit.
  • Click Save As from the ribbon button and save the report in the ABC Custom Reports folder.

The report should now be visible in the SCCM console.

Editing a Custom Report
  • Don’t use the SCCM console to edit a report – too flaky.
  • Open Report Builder using the shortcut.
  • Click Open on the Getting Started screen and browse to the custom report to be edited.

Editing an Existing Report

  • Do not edit the existing reports in place causing them to be overwritten – always do a Save As to the custom reports folder as soon as they are opened

Reference: https://technet.microsoft.com/en-gb/library/dd334596.aspx.