Configuration Manager reporting services point: install errors, empty reports, srsrp.log

Configuration Manager (the product many still call SCCM) doesn't render reports itself: SQL Server Reporting Services (SSRS) does, and the reporting services point keeps the reports, the data source and the permissions in sync. When reporting breaks, the problem is in one of those three. In this post I'll check SSRS first, then the role install, the data source and permissions, and finally reports that run but come back empty.

Symptoms#

• Monitoring › Reporting › Reports is empty, or Report Options lists no report server.
• Reports fail in the console or the SSRS web portal with errors such as:

The permissions granted to user 'CONTOSO\jdoe' are insufficient for performing this operation. (rsAccessDenied)
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source '<data source name>'. (rsErrorOpeningConnection)
The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

• Reports run without errors but return no rows for some administrators.

Why it happens#

The reporting services point copies Configuration Manager's report folders and reports into SSRS, creates a data source with the reporting services point account and applies report security. When someone runs a report, SSRS uses that account to query the site database. Security comes from role-based administration: Configuration Manager adds two SSRS roles, ConfigMgr Report Users (run reports) and ConfigMgr Report Administrators (all reporting tasks), maps your administrative users onto them, and rechecks the folder permissions every 10 minutes, overwriting manual changes.

How to fix it#

1. Prove SSRS works on its own#

On the reporting server, open Report Server Configuration Manager and connect to the instance:

• Report Server Status: the service is Started.
• Web Service URL: the URL opens in a browser.
• Database: the report server mode is Native, and the ReportServer database is reachable. If not, this is where rsReportServerDatabaseUnavailable comes from.

Configuration Manager supports the same SQL Server versions for reporting as for the site database, on a 64-bit SQL Server instance, local or remote. Starting in version 2002, you can also integrate Power BI Report Server.

2. Fix the reporting services point install#

In Administration › Site Configuration › Servers and Site System Roles, add or check the Reporting services point role. Typical wizard problems:

• Verify on the database fails (red warning icon): your account needs Read access to the site database. Hover over the icon for the reason.
• Reporting Services server instance list is empty: SSRS isn't installed, configured and started, or your account can't read WMI on that server.
• The reporting services point account must have the Log on locally right on the server that hosts the Reporting Services database; Configuration Manager grants it access to the site database for you.

Then follow the install on the site server: srsrp.log in the Configuration Manager Logs folder (with srsrpsetup.log and srsrpMSI.log for setup detail), and the SMS_SRS_REPORTING_POINT component under Monitoring › System Status › Component Status.

3. Fix data source errors#

rsErrorOpeningConnection means the stored credentials stopped working, for example after a password change, a disabled account or a moved site database. Open the role's properties (Site Role › Properties) and re-enter the site database server, database name and user account so the site rewrites the data source. Check the account isn't locked out and still has Log on locally.

4. Fix rsAccessDenied#

• Give the user a security role with Run Report (or Modify Report to author) for the objects they report on; the site maps these to the two SSRS roles.
• Users who run reports must be in the same security scope as the site that hosts the reporting services point.
• Allow up to 10 minutes for the permission sync, and never fix access by editing SSRS permissions by hand: the site overwrites them.
• For users in another domain, you need a two-way trust, and the account that runs the Reporting Services service must be in the domain's Windows Authorization Access Group.

5. Explain empty reports#

Most built-in reports filter rows by role-based administration, so an administrator with a narrow scope legitimately sees less. Test with a full administrator: if they see data, it's scoping. If nobody does, check the collection parameter and that the data exists (hardware inventory, software updates compliance, status messages) for those devices.

6. After URL changes, SQL upgrades or Report Builder errors#

If someone changed the report server URL after installing the role, the console keeps the old URL and can't run, edit or create reports. Remove the reporting services point, change the URL, then reinstall it; do the same around SQL Server upgrades. If Report Options won't save a default report server, install the SQL Server Report Builder version that matches your report server.

Verify the fix#

1. SMS_SRS_REPORTING_POINT shows status message ID 1015 (installed successfully).
2. srsrp.log contains Installation was successful, then the folder, report and security confirmations, ending with Successfully checked that the SRS web service is healthy on server.
3. Monitoring › Reporting › Reports lists the report folders, and Report Options shows your default report server.
4. A test user with Run Report rights runs a report from the console. In the ReportServer database, recent runs show success:

SELECT TOP 20 TimeStart, UserName, ItemPath, Status
FROM ExecutionLog3
ORDER BY TimeStart DESC

Prevent it next time#

• Treat the report server URL as fixed; plan any change as remove, change, reinstall.
• Remove the reporting services point before SQL Server or SSRS upgrades, and reinstall afterwards.
• Use a dedicated reporting services point account with a managed password-change process.
• Grant report access through Configuration Manager security roles only.
• Check component status after role changes; the site component status messages quick reference explains the other message IDs you'll meet there.

References#

• Configure reporting in Configuration Manager
• Prerequisites for reporting in Configuration Manager