report service broken after inplace upgrade SQL 2008 to SQL 2014

[SpacePants]

Hi All.
Long time lurker here but now is the first time I have been moved to create an account and ask for  some advice.  I’m looking for a bit of direction on how to proceed as I’m a bit stuck, I’d like to fix this without breaking anything else in SCCM.

 

I’m running SCCM build 1606 – Last week we upgraded the SQL instance hosting all out SCCM databases from SQL 2008 SP3 to SQL 2014 (this because we plan to upgrade to SCCM 1702). The SQL upgrade wizard reported  that it upgraded all components  successfully to SQL 2014 but when I opened the SCCM console all the reports are missing. Everything else in SCCM appears to be OK.

 

Further investigation revealed.

 

 

• The Sql server Reporting services Service  is not running  - when I try and start it I get the error “ Error 2 – can’t find the service specified”. It turns out the executable it maps to named ReportingServicesService.exe  does not exist in "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin”
•  
• If I try and launch the SQL reporting services configuration manager 2008 and 2014 console it is unable find a reporting server.

 

So I guess SQL reporting is broken.

 

I’m wondering what I should do to try and fix this. This is what I was thinking but it’s just a guess.

 

• Remove Reporting services role from SCCM.
•  
• Uninstall SQL 2008  Reporting Services  feature?
•  
• Uninstall SQL 2014 Reporting services Feature?
•  
• Unattached the current SQL Report server and Report Temp DB databases (would I need to do this?).
•  
• Reinstall SQL 2014 report services, configure reporting service point.

 

 

I’d a appreciate any advice, I did have a few custom reports configured but I don’t mind setting them up again. The main thing is to get reporting services up and running again.
Thanks in advance.

Edited April 10, 2017 by SpacePants
spelling

[GarthMJ]

What your propose, make sense to do.

[sccm_buff]

I Had same issue , I was getting assembly reference issue if  try  to access reports, I followed below steps and got it fixed. Yes I had bakup pf all my custom reports so that was not an issue for me.

 

1. Uninstalling the ConfigMgr 2012 Reporting Services Role:

1.a Open the ConfigMgr 2012 Console

1.b Navigate to Administration \ Site Configuration \ Servers and Site System Roles

1.c In the Servers and Site System Roles window on the right side of the screen locate the Server you need to remove the role from and select it

1.d In the lower pane of the window (Site System Roles) scroll down until you see Reporting Services Point

1.e Right click the Reporting Services Point, select Remove Role

1.f In the confirmation window, click Yes

1.g Close or minimize the ConfigMgr 2012 Console

 

2. Uninstall the SQL Reporting Services:

2.a On Server with the SQL Reporting Services open the Control Panel

2.b Navigate to Programs and Features (Add \ Remove Programs)

2.c In the Uninstall or Change a Program list select Microsoft SQL Server 2012 (64-bit)

2.d Click the Uninstall\Change option at the top of the list

2.e On the SQL Server 2012 Dialog, click Remove

2.f On the Setup Support Rules Dialog, click OK

2.g On the Select Instance Dialog, assure the correct instance is select and click Next

2.h On the Select Features Dialog, place a check mark next to Reporting Services – Native and click Next

2.i On the Removal Rules Dialog, click Next

2.j On the Ready to Remove Dialog, click Remove

2.k On the Complete Dialog, click Close

2.l Perform a reboot

 

3. Clean up the SQL Reporting Service DBs:

3.a Open the SQL Server Management Studio

3.b On the Connect to Server Dialog, verify connection settings and click Connect

3.c Expand the Databases

3.d If the ReportServer and ReportServerTempDB are still present perform the following, if not close the SQL Server Management Studio and move to step 3.j

3.e Select the ReportServer DB, right click and select Tasks and then Detach

3.f On the Detach Database Dialog, select the Drop check box and then click OK

3.g Select the ReportServerTempDB, right click and select Tasks and then Detach

3.h On the Detach Database Dialog, select the Drop check box and then click OK

3.i Close the SQL Server Management Studio

3.j Open File Explorer and navigate to the folder where you stored the ReportServer and ReportServerTempDB database files and the log files

3.k Delete the actual ReportServer and ReportServerTempDB files and log files

 

4. Re-Install the SQL Reporting Services:

4.a On Server that you just removed the SQL Reporting Services from open the Control Panel

4.b Navigate to Programs and Features (Add \ Remove Programs)

4.c In the Uninstall or Change a Program list select Microsoft SQL Server 2012 (64-bit)

4.d Click the Uninstall\Change option at the top of the list

4.e On the SQL Server 2012 Dialog, click Add

4.f On the Browse for SQL Server 2012 installation media, locate and select the location of the install media, then click OK

4.g On the Setup Support Rules Dialog, click OK

4.h On the Product Updates Dialog, click Next

4.i On the Setup Support Rules, click Next

4.j On the Installation Type Dialog, select Add features to an existing instance of SQL Server 2012, click Next

4.k On the Feature Selection Dialog, check the Reporting Services – Native, click Next

4.l On the Installation Rules Dialog, click Next

4.m On the Disk Space Requirements Dialog, click Next

4.o On the Server Configuration Dialog, click Next

4.p On the Reporting Services Configuration Dialog select the Install Only radio button, click Next

4.q On the Error Reporting Dialog, click Next

4.r On the Installation Configuration Rules Dialog, click Next

4.s On the Ready to Install Dialog, click Install

4.t On the Complete Dialog, click Close

 

5. Configure the SQL Reporting Services:

5.a On Server you just re-installed the SQL Reporting Services on open Reporting Services Configuration Connection

5.b On the Reporting Services Configuration Connection, click Connect

5.c Select the Web Service URL, click Apply

5.d Select the Database, click Change Database button

5.e Select Create a new report server database, click Next

5.fClick the Test Connection button, click Next if successful

5.g Click the Next button

5.h Click the Next button

5.i Click the Next button

5.j Click the Finish button

5.k Select the Report Manager URL, click Apply

5.l Click Exit

 

6. Re-install the ConfigMgr 2012 Reporting Services Role:

6.a Open the ConfigMgr 2012 Console

6.b Navigate to Administration \ Site Configuration \ Servers and Site System Roles

6.c In the Servers and Site System Roles window on the right side of the screen locate the Site Server you removed the Reporting Point Role from  and select it

6.d Right click the Server and select Add Site System Roles

6.e On the General Dialog, click Next

6.f On the Proxy Dialog, click Next

6.g On the System Role Selection, place a check next to Reporting Services Point, click Next

6.h On the Add Site System Roles Wizard, click Verify

6.i On the Add Site System Roles Wizard, click Set by User Name, select Existing Account

6.j On the Select Account Dialog, select the account, click OK

6.k On the Add Site System Roles Wizard, click Next

6.l On the completion Dialog, click Close

6.m Wait for roughly 5 minutes

6.n Navigate in the ConfigMgr 2012 Console to Monitoring \ Reporting

6.o Click the Links located in the right pane to assure they work

6.p Navigate in the ConfigMgr 2012 Console to Monitoring \ Reporting \ Reports and verify they are present

 

 

[GarthMJ]

Keep in mind that it is recommend to access the reports from the web interface and not via the console as there is less overhead. Plus do you really want to give someone the console just to view reports?

Also keep in mind that if the web interface doesn't work then neither will ConfigMgr

[SpacePants]

Thanks for taking a look guys.

 

@GarthMJ = understood, I configured RBAC ages ago, only two other admins can run reports and yes the did use the website . I have (or had) some scheduled reports that fire once a month so I want to get this fixed  before they fire off. thanks for taking a look at my post  I'll try my steps tomorrow or Wednesday and report back to this forum., hopefully if someone else has this problem they may find this post.

@sccm_buff = You had the same problem, was t after a SQL upgrade? Thanks for the detailed steps. I shall try the whole removal/reinstall of reporting services tomorrow or Wednesday. I may actually have a backup of my reports, I think may have done this before I upgrade from SCCM 2012 R2 to current branch last year. hopefully this won't be such a PITA (famous last words).

Has anyone else come across this problem?

Does anyone have any other comments before I make this change?

 

 

 

Edited April 10, 2017 by SpacePants
spelling :)

[sccm_buff]

Yes I had same issue once I was upgrading from SQL 2008 R2 SP3 To SQL 2014. For me error was like : "Failed to load expressions host assembly. Details: Could not load file or assembly ‘SrsResources. Culture=Neutral’ or one of its dependencies. The system cannot find the file specified."

I also refer : https://ronnydejong.com/2013/05/23/reporting-service-point-rsp-broken-after-upgrading-sql-server-2012-sp1-sysctr/

But i new rather playing with various files, its better to go for Reporting service installation.

[SpacePants]

@sccm_buff = thanks for the information.

Something obviously went wrong here with my reporting services upgrade - I have a MSRS10_50.MSSQLSERVER folder (SQL 2008) but I don't have a MSRS 12.MSSQLSERVER  directory (SQL 2014 ) as I would expect in the directory C:\Program Files\Microsoft SQL Server.

 

I will reinstall today or tomorrow and report back.

 

 

[sccm_buff]

as you have completed your upgrade to sql 2014, so go to services....select sql service properties which will give u location pf binaries stored.

[SpacePants]

So, should anyone have the same problem this is what I did:

• Remove Reporting services role from SCCM, closed console.
• Ran sql 2008 installer with a view to removing reporting services - it reported reporting services was not installed. (weird)
•  Ran sql 2014 installer with a view to removing reporting services - it reported reporting services was not installed. (OK, that's also weird).
• Unattached the current SQL Report server and Report Temp DB databases.
• Reinstall SQL 2014 report services.
•  
• Registered Reporting URL and created reporting databases  in Reporting Services Configuration Manager 2014
•  
• Installed the Reporting services role in SCCM - (Warning - I had the blank database problem described here http://myitforum.com/myitforumwp/2012/10/10/reporting-services-site-role-setup-instance-blankempty/I followed the instructions in this post and all is OK now.

Thanks for the help and advice offered by all.