The Network Manager at Westminster School presents solutions to sticky problems...

Thursday, 22 November 2012

Getting SQL 2008 R2 Reporting Server working with System Center 2012 Configuration Manager

When you upgraded to System Center 2012, you discovered that in order to get reports from Configuration Manager, you now absolutely need to get Configuration Manager working with SQL Reporting Services. You follow the instructions provided by Microsoft and then you hit the train wreck!!!

Error 7403 - The report server service is not running on Reporting Service Point server ""; start the service to enable reporting.

Nothing out there seems to fix the problem. I have discovered that there are a number of problems that cause this error, hence the one or two 'fixes' out there are all red herrings. There are, of course, clues. I followed them and have come to the following solution to the problem.

What's the problem?
The problem occurs mainly when you have the SQL Server, the SQL Reporting Services on the same server as the Management Point. This means also that IIS is installed. That is not a problem, mind you. Reporting Services does work happily with IIS, but as you will see, there are caveats. After following all the installation you find that SMS_SRS_REPORTING_POINT under Component Status, has a Red X instead of a Green Tick. List the error events you get a repeated performance from Error 4703. It is also possible that if you go to Reporting Service Configuration Manager, you can sometimes get the ReportServer website to come up, and sometimes not, but only on HTTP, and not on HTTPS. You also see no reports and any attempt to create a report brings up AUEM (Another Useless Error Message.) Checking the srsrp.log you notice the error: 'The underlying connection was close: Unable to establish a secure connection.'

Problem 1 - The certificate Subject
If you have followed Microsoft's installation plan to the letter you probably have an IIS certificate. The subject on the certificate will be the host name of the Management Point (e.g. 'MySMS'.) This is fine, perfect for Configuration Manager. However, I bet when specify the Site Server, you specify them using their FQDN (e.g. ''.) Can you guess what I am about to say now? Configuration Manager now cannot connect to SRS because the certificate is invalid! As I said - train wreck!

Solution 1 - The first thing you need to do is uninstall the Reporting Point, as you will need to re-install it later. Get yourself a new certificate from your CA with the FQDN as the subject and a uniquely identifying name (I used "For Report Server") as the subject and install it in the computer's personal certificate store.

Problem 2 - Conflicting Bindings
But it's not a simple as that. Why? Because Reporting Services shares the TCP Port with IIS. IIS needs to be using the SMS IIS Certificate. Despite the fact that you can specify a separate certificate in IIS to that in Reporting Services, they both present their Certificate to the OS to handle. The result is that only one certificate can remain on that particular binding (IP and Port combination.) In the report server log you see 'Failed to register url=' etc.

Solution 2 - Change the Binding in Reporting Services Configuration Manager (RSCM.) Now, funny enough guess what? You might have another problem.

Problem 3 - Lingering URLs in Reporting Services
When you change the certificate and binding in RSCM you sometimes find that although you have changed the binding, the original link to 443 remains. If it does, Reporting Services will still fail to bind everything.

Solution 3 - Remove all bindings first and stop the Reporting Services Service. Open \Program Files\Microsoft SQL Server\MSRS10_50.\Reporting Services\ReportServer\rsreportserver.config (remembering to take a backup first!!!) and remove any lingering URL Reservations. You will find them under ... For me it was okay to remove the entirety and leave a behind, if you understand xml. If not, just delete everything in between the individual  and . Restart the Reporting Services Service.

Wrapping it all up
Before you re-configure your bindings in RSCM I would look at netstat first. Using the command line 'netstat -ano -p TCP' list out all the bindings. I did not do this and guess what, I picked another port that was already in use! I would suggest using a custom HTTP port as well as a custom HTTPS port, for the simple reason that you will see it being bound in netstat. It's okay to leave it and share the binding with IIS, but it does mean that you won't see the successful binding.

Okay - you have picked your ports and you go back to RSCM. Go to the "Web Service URL" tab and click on Advanced. Reporting Services automatically picks a host only URL if you enter an IP Address. You don't want this, for sanity's sake. Add the HTTP identity using your custom port (I used 81) and the FQDN for the host header. Add your SSL Binding selecting (All IPv4), add you custom currently unused port (I used 446 - 443 was used by IIS and something else had 445) and select your uniquely named certificate (with the "For Report Server" name.) Do the same for IPv6 (You are using IPv6 - >frown<) and confirm. Do the same for the "Report Manager URL" tab. Now go back to netstat and note that you now have new connections (for me on 81 and 446.)

Great. Now Navigate to your ReportServer url (launch Internet Explorer using 'Run As Administrator')  on SSL and note that you do not get any certificate warnings. Start to feel happy.

Install the Reporting Point once more. You do not have to worry about port numbers, Configuration Manager figures this out. You will need to wait about ten minutes (should be less) and then check your reporting service URL again. Configuration manager will have added a folder. Go to the Monitoring Tab of Configuration Manager and check out the large number of default report that have suddenly appeared. Finally, notice that you now have a green tick next to your SMS_SRS_REPORTING_POINT component status.

Go back to you boss and state to him that you single handly resolved the problem that had been bothering him for the last six months. Brownie points all round!!!

1 comment:

  1. could you pose a video please, its really hard to go through. thanks.