SharePoint 2010 Foundation + SQL Server Reporting Services Integrated mode installation issues: Failed to establish connection with report server, 401 errors and more

Thu, Feb 9, 2012 3-minute read

You may be trying to set up SharePoint 2010 to act as the report repository for SQL Server Reporting Services report. This is a pretty nifty feature, especially as it’s available in the Foundation (i.e., Free) version of SharePoint. There is a pretty exhaustive guide on how to set it up and for extra help, this blog post is pretty good too, and if you follow the steps, you get pretty far. However, if you’re in a multiple server setup (i.e. your SQL server isn’t on the same server as your Central Admin) you will likely encounter configuration issues surrounding authentication, and there is a great deal of confusion about what it all means - especially when it comes down to Kerberos. That’s beyond the scope of this post - what I’m covering here is one very annoying issue that there was no definitive answer to on the web.

Assuming you get all the server parts setup and Reporting Services configured, you might find that you can browse to your report server address on the machine hosting reporting services, but, if you try to browse to that address from anywhere else, you’ll get an endless stream of login boxes, and no credentials will work. Also, if you go to Central Admin and go to General Application Settings > Reporting Services Integration, when you fill in the details to connect to reporting services, you’ll hit an error like:

Failed to establish connection with report server. Verify the server URL is correct or review ULS logs for more information. Product area: SQL Server Reporting Services, Category: Configuration Pages

and, in the ULS logs, an error like:

SQL Server Reporting Services Configuration Pages Failed to retrieve RS configuration information: System.Net.WebException: The request failed with HTTP status 401: Unauthorized.

Here’s how I fixed it my workaround.

A lot of the help out on the web point to Kerberos authentication. I didn’t think it was that in this case, but nevertheless, I did mess about with SPN names and so on. I also disabled the loopback check, but again, I don’t think is required. (And is not supported on a production environment.)

Here’s what eventually got it to work for me, which assumes you are using ONLY basic Windows Authentication.

  1. In your RSReportServer.config file (usually in Program FilesMicrosoft SQL ServerMSRS10_…InstanceNameReporting ServicesReportServer), find the section. You will likely find a setting for . Delete/comment out this entry.
  2. You should now be able to add your RS web services URL in to Central Admin. Important: choose Trusted Account as your authentication mode.
  3. However, you will not be able to access the Set Server Defaults page which will fail with a similar error:

The request failed with HTTP Status 401.1: Unauthorized.

  1. This is because your IIS application pools are still running in mixed mode. Or words to that effect. To fix, on each server in your farm, open a command prompt running as Administrator, and navigate to C:inetpubadminscripts. Now type: cscript adsutil.vbs set w3svc/NTAuthenticationProviders “NTLM”. I did an iisreset after, just to be sure. After that, everything started working fine for me.