We have a setup with a MOSS 2007 server (web front-end) and a separate SQL Server 2005 SP2. The MOSS server also runs SQL Server Reporting Services and is configured for integrated mode. Integrated mode allows a report designer to publish reports (rdl files) to a document library. Users can then just click on the report in the document library to view the report. Nice idea but there are quite some issues with this setup.

When we setup this environment with Kerberos from client --> MOSS --> SQL Servr 2005 it all works fine. But we do not want Kerberos from the client to the MOSS server, we want basic authentication over SSL. Switching to basic authentication is easy enough: just go to Central Administration and from the authentication providers link you can change the authentication settings. MOSS will then configure the IIS web site appropriately. The user can then logon to MOSS using basic authentication and you can verify that in the event viewer of the MOSS server. Just look for a logon event in the security log for the user where the logon process is ADVAPI.

After switching to basic authentication, the MOSS sites work just fine. But when we click on a report we get an error: "An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode".

To explain the error we have to delve a bit deeper in how the integration works by going over the complete request:

  1. User connects to MOSS using basic authentication. In our lab environment the URL is http://sp.newtech.local.
  2. A connection is made from the MOSS server (not the client) to http://sp.newtech.local/_vti_bin/ReportServer/ReportService2006.asmx using the user's credentials. This is the Report Server proxy endpoint as discussed in this article.
  3. A connection is made to the actual RS endpoint as configured with the Reporting Services configuration utility. In our case the endpoint sits in the Default Web Site (separate from sp.newtech.local).

When we switch to basic authentication, it goes wrong at step 2 with a 401 Unauthorized. Of course, the switch to basic authentication also makes http://sp.newtech.local/_vti_bin/ReportServer use basic authentication and that is probably not supported (wild guess because I can't find documentation about it). The solution to this problem is to configure the _vti_bin/ReportServer virtual directory with Integrated Authentication. That is a bit annoying especially when you add front-end web servers later because the SharePoint Timer service won't configure this setting automatically.

UPDATE: this fix will not work for other sites below the root. In our lab, if we have reports at http://sp.newtech.local/sites/othersite/reports then the proxy web service is at http://sp.newtech.local/sites/othersite/_vti_bin/reportserver and that URL will use basic authentication as configured in Central Administration. Because that URL is not even visible in IIS Manager, I cannot switch it to Integrated Authentication. I have not yet found a solution to this problem.

Anyway, the reports now work over basic authentication. When reports need to make connections to other servers with Kerberos, make sure you configure Kerberos appropriately with SPNs and delegation settings. Also see this article.

Time to configure SSL. I won't go in the details about how to do that. Suffice it to say that we used an online certificate authority and configured a certificate on our IIS web site for https://sp.newtech.local. Of course, you need to configure SharePoint's alternate access mappings or you won't get anywhere but I guess you know that already. If you don't, check this. Make sure that the certificate is trusted by the system or step 2 will go wrong again with a failure to validate the certificate!

Now for the piece that is not working (grin). If you configure the SSL URL for a zone other than Default, viewing a report with the SSL URL will not work. It appears that Reporting Services in integrated mode only supports requests if they belong to the Default zone. So we needed to configure the URL https://sp.newtech.local for the default zone and make sure that our users only go to our site using that URL. Fair enough, no big deal there.

That should fix it right? WRONG. When you access the reports from the correct URL (default zone), the following error pops up: "The item '<report URL>' cannot be found. (rsItemNotFound)". For that one, we have not found a fix yet. I will keep you posted if we do. And if you have any ideas, let us know.

UPDATE: the rsItemNotFound error comes from the fact that the Report Server database is not synchronized properly. Redeploying the reports to SharePoint using the https://... URL with Visual Studio 2005 will fix the problem!

This just goes to show that SharePoint and products that integrate with SharePoint such as Reporting Services all are very promising but that they are quite complex to setup and manage. And I must say that the documentation sucks. There is enough documentation allright but not enough precise information about how things exactly work and what is supported and what not.