I recently implemented SQL Server 2008 Reporting Services in SharePoint Integration mode. Although I followed all the directions and walkthroughs, it took me two days to get it implemented. I was installing SSRS on an existing MOSS WFE, and though every indication showed a successful installation, it would not integrate. I finally did get it working, though. Here is what I did.
While I was testing the broken SSRS implementation the symptoms included:
- No report would open or display.
- I could not configure any of the SSRS-specific properties of any SSRS object, including reports, models, and data sources.
- The SharePoint-specific properties, such as version and metadata, were still editable.
- Every failure resulted in the unhelpful error message: "An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode."
The SSRS logs and SharePoint logs did not have much in the way of useful information, except a generic .NET failure message:
| Timestamp | Process | TID | Area | Category | EventID | Level |
|
6/29/09 17:17 |
OWSTIMER.EXE (0×0820) |
0×0FBC |
Office Server |
Office Server Shared Services |
7fy6 |
Exception
|
| Message:
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.ReportingServices.SharePoint.Common, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.ReportingServices.SharePoint.Common, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) at blah blah blah… |
||||||
In the SharePoint site I set up to test SSRS, I put a Report Viewer Web Part on the page and attempted to link to an existing RDL file in a document library on the same site. When I clicked the browse button, instead of seeing the custom "browse for file" dialog, I saw a SharePoint page stating that a 401 error had been returned). This was not the generic IIS 401 page, but a SharePoint page.
This is where I had an "aha!" moment. SharePoint has a known issue with a 401.1 error, where access will be denied when attempting to browse to a host header that doesn't match the hostname of the server.
On my MOSS server, SharePoint integration is configured as follows:
| Service | URL | Port |
| MOSS | http://portal (a host header) | 80 |
| SSRS | http://Moss1 (a hostname) | 8080 |
The way MOSS and SSRS integrate is that reporting services creates an HTTP endpoint and hands off to MOSS (and vice versa). As far as the user is aware, the interface is seamless and only one URL (http://portal) is presented. That's the way it's supposed to work.
But since our MOSS portal is using a host header, this is triggering some built in security on Windows 2003's part. Since SP1, the default behavior has been to block host header browsing from the server itself. If you browse from any other machine to the server, it works fine. But on MOSS1, for example, I would not have been able to browse to http://portal , since this would be looping back on itself and possibly a reflection attack.
Normally this is a simple inconvenience. So what if you can't browse to a host-header site from the server it's hosted on? No biggie, right?
But SSRS is another web service trying to communicate to MOSS via a host header, and that is what broke. After modifying the registry to disable loopback protection and rebooting, the integration began working as expected.
References:
- Reporting Services HTTP 401 (Unauthorized) – Host Headers require your attention
- Integrating SQL Server 2008 Reporting Services with SharePoint 2007:
moffitar