SCCM 2006 Reporting Services Point on a separate server

Mike Plichta 1 Reputation point
2020-12-22T22:48:17.273+00:00

Hi Fellow Engineers!

I dislike the rabbit holes that Microsoft documentation puts you down to get a simple step by step guide on installation. I tip my "virtual" hat to those who make "how to guides" and I appreciate that. In this case I was missing documentation about a reporting services point that is not hosting the CM_xxx database. Here are some notes on what worked.

Servers

  • Database1: Server hosts (CM_xxx, ReportServer, RerportServerTempDB) database
  • Report1: Server hosts SSRS and nothing else. Should be accessible using http://report1/reports before installing an RSP.
  • SCCMMP: Main SCCM management point server

Notes

  • Databases are all hosted on Database1. Report1 has no databases.
  • SSRS-DB-Service account is domain based and only used during the installation of SSRS. It needs rights to create the reportserver databases during installation.
  • SCCM-SSRS-Service is domain based and only has user rights in the SSRS dashboard. It is the account used when installing the reporting services point in the SCCM console. It also needs "logon as batch" rights on Report1. You can do this in gpedit.msc. It does not need to be in any administrators group. It does need read access to the cm_xxx SCCM database.
  • SCCMMP$ is the domain system account for the main SCCM management point. It must be in the administrators group on Report1. It is possible to specify a different account during the reporting point install but I find this easiest. The console install will not show any instances of SSRS on Report1 if the installation account does not have admin rights.

Troubleshooting - cannot execute existing reports

After install of the reporting services point, If you logon to the SSRS portal directly and browse, you'll see data sources at the bottom of the report list. You can attempt a test connection and if it fails, check the security event log for event failures. You'll see the SSRS Service account listed but also the report service account in the same event with a logon type and reason for failure. Assuming you have the name/password right, I was getting logon type 4 (batch) The user has not been granted the requested logon type at this machine.

Troubleshooting - No Reporting Services server instance in dropdown

"Use the site server's computer account to install the site system" is what I used for install. The name of the main SCCM Management Point followed by a $ needs to be part of the administrators group on Report1. You can use another account, but it would also need to be an administrator.

Make sure http://report1/reports is accessible meaning that the base SSRS install is working.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,799 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Fiona Yan-MSFT 2,311 Reputation points
    2020-12-23T10:55:32.777+00:00

    @Mike Plichta

    Thank you for posting in Microsoft Q&A forum.

    Based on a lot of researches, unfortunately, there are actually no document about RSP not host on the CM database. We can install the SQL reporting services on a separate server and add the CM role to it. It needs a DB and ideally should be linked to the SCCM SQL Server. About this, I may need sometimes to do a lab,once it gets some update I will tell you.
    But it seems to be more related to SQL issues, at such situation, to get better support, it is suggested to post on the SQL server community to see if they have some comments about this, as they may have more resources about the SQL. So I appreciate your understanding that we are not the best channel to address this issue since the forum is mainly focusing on SCCM simple break-fix issues.
    To obtain the contact methods for specific SQL technology request please take a look at the web site listed below:
    https://learn.microsoft.com/en-us/answers/products/sql-server

    Thank you very much for your kind understanding and have a nice day!

    If the response is helpful, please click "Accept Answer" and up vote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments