question

PrimeFX avatar image
0 Votes"
PrimeFX asked saldana-msft edited

SCCM Reporting Services not working after SQL upgrade

Hi all,

we have a SCCM build 1910 (Hotfix KB4537079 installed) and needed to upgrade the existing SQL Server 2012 with the CM DB to SQL Server 2019 a few days ago. .

The SQL Server runs on its own Windows Server. CU9 is installed.
SCCM is communicating with SQL server and all components status is okay, but when installing the RS Role nothing happens, Reports don´t show up in console.


So i removed SCCM RS role, re-installed Reporting Services 2019 and configured it on SQL Server, the URL http://SQLServer:80/Reports is accessible, even from other Servers (but no reports available).
Before that, the ReportServer and ReportServerTEMPDB were detached in the SQL Mgmt Console and the DB and DB Log Files were deleted.
The RS configuration recreated the DB and Log file.

When I now try to install the Reporting Services role in SCCM console (correct server, SQL Instance and user selected) nothing happens - in the log files srsrpsetup.log, srsrp.log, srsrpMSI.log on the SQL Server under C:\SMS\logs there are no new entries, also no entries for this in the SCCM Server Logs and there is no new component SMS_SRS_REPORTING_SERVICE displayed under Monitoring -> System Status -> Component Status.

Only the site system role "Reporting Services point" appears in the status critical under Monitoring -> system status -> site status.

When I display all messages for this site system role, not a single entry appears for 1 day ago...


What I noticed on the SQL Server after the upgrade is that the service "SMS_EXECUTIVE" does not start and cannot be started manually.
I get the error message ->

"Windows could not start the SMS'_EXECUTIVE service on Local Computer. -> Error 1053: "The service did not respond to the start or control request in a timely fashion".

The Event Viewer doesn't say anything more about this either....

In SCCM -> "Configuration Manager Service Manager" -> Components there are three entries for this service - one for SCCM Server (started), one for SUP (started) and for SQL Server (stopped). If i try to start service here i get the error: "Error communicating with component"

I already googled but since there are no entries in the logs it is a bit difficult - is it possible that this behavior with RS role installation has to do with the SMS_EXECUTIVE service issue?

What could be the reason that this service does not start anymore?

Thanks a lot!



mem-cm-generalsql-server-reporting-servicesmem-cm-site-deployment
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

By the way, on the SQL server there is still the folder C:\SMS_SRSRP with all the default reports in it.
So they are on the server, only the role cannot be installed - after adding the role to the SQL server nothing happens, no entries in the logs about it.

I think I need to fix the SMS_EXECUTIVE service issue first, but have no other clues except for the error message.

Please help!

0 Votes 0 ·

1 Answer

AllenLiu-MSFT avatar image
0 Votes"
AllenLiu-MSFT answered AllenLiu-MSFT edited

Hi, @PrimeFX
Thank you for posting in Microsoft Q&A forum.
Have you configured reporting services role before you upgrade the SQL Server and did it work before?

We may use the following procedure to verify that SQL Server Reporting Services is installed and running correctly
https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/configuring-reporting#verify-sql-server-reporting-services-installation

For the Error 1053 of start the SMS_EXECUTIVE service, we may try to delete and re-create site system server of the SQL Server.


If the response is helpful, please click "Accept Answer" and upvote 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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Allen,

thanks for your answer!

Before the SQL upgrade Reports worked.

Of course, like mentioned in my post i tried several times to configure the reporting services on SQL Server.

I I went through the points in the MS link:

The Reporting Services Instance is running, also Web Service URL ad Web Portal URL are accessible from SQL Server and also other Servers.
Database is set to native.

So i think on Reporting Services everything is okay..
But when installing the RS Role in SCCM then nothing happens in logs, just the site system component in status "critical" appears in "site status".
No SMS_RS_Reporting_Services component in system status -> component status.

I´m not sure about re-creating the site system server on SQL Server, what would be the best way to do this?
I have following site system role regarding SQL Server:

  • Site dabase server -> CM_XXX Databse

  • Site databse server ->CM_XXX Transaction Logs

  • Component Server

thanks!!

0 Votes 0 ·

Hi, @PrimeFX
Thank you for posting in Microsoft Q&A forum.
Sorry, if we have the site database on the site system server, we cannot delete the site system server.
Did we install the client agent on this database server and uninstall it?
Now, we may try to perform the site maintenance or reset this site to check if it helps, it will reinstall all site components and all site system roles:
86855-42.jpg


0 Votes 0 ·
42.jpg (101.0 KiB)