SharePoint and SSRS

Out of nowhere it seems a very stable and performing MOSS 2007 and SSRS 2005 environmnet completely bombed!! Ok maybe not out of nowhere, after completing a migration of our MOSS environment from one SQL Server to another our Integrated MOSS 2007 and SSRS environment quit delivering reports. Where we once had reports we now had the below error message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'YourDataset'. (rsErrorExecutingCommand)
Failed to execute web request for the specified URL. (rsXmlDataProviderError)

 We immediately went into trouble-shooting mode and begin retracing the steps the bring our new environment up to standards where the old environment was. This included web part installs and configuration changes. After all of this still no reports from MOSS!!

 This post is half gripe and complain and half helpful tips on integrating MOSS and SSRS.

There are many useful links when it comes to integrating SSRS with MOSS. I followed the ones available on TechNet and SharePoint and SQL Server Reporting Services - Installation and Setup. These seem to be the most straight forward I found during our initial deployment.

Of course during our migration/restoration "We knew exactly what we did the 1st time" so we just did the same thing again. And we all know how well that usually winds up.  With the above error and users complaining that the reports are down!!

Well after much time and headache we decided to retrace the steps following the above mentioned blogs step by step.  In the blogs one part that is mentioned briefly and probably not stressed as much as necessary is the need for MOSS site you are pulling data from the have Kerberos as the default Authentication Method!!  You can set this by navigating to Central Administration on your MOSS server > Application Management > Select your Web Application > under the Application Security section > Authentication Providers > default zone > check Integrated Windows Authentication > Negotiate (Kerberos) > Save.

This simple change totally resolved our issues.  Of course this leads into Kerberos Authentication for your MOSS Environment and the SPN's that go with it but we will save that for another post!!

I hope this will save someone out there a little time and trouble!!