Sharepoint Report Server Integration (multi-server)

Follow all the steps at http://msdn.microsoft.com/en-us/library/bb677365.aspx

The best troubleshooting document is Chris Alton’s White Paper,  Reporting Services SharePoint Integration Troubleshooting

Perhaps one of the most typical of the difficult problems is the one I faced where after getting everything up and running with Sharepoint on one server and Report Services on another it through this error:

“An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode”

Getting this when attempting to run an uploaded report.rdl file or set/create a new Data Source. The IIS logs showed multiple 401 (Unauthorized authentication errors) like:

10.10.10.19 POST /_vti_bin/ReportServer/ReportExecution2006.asmx – 80 – 10.10.10.19 – 401

Note that the user logon is missing and its calling itself. Monitoring the traffic between the Sharepoint and Report Services servers I saw that there wasn’t any generated so the logs on the Report site and server were of no help. The *call* never got forwarded.

I won’t go any further into all the troubleshooting I did but eventually I found the White Paper linked above which had section 8.1.2 exactly detailing this issue,

Every operation in regards to Reporting Services made through SharePoint goes through a proxy object on the SharePoint server. The _vti_bin/ReportServer is this proxy object. The SharePoint server makes a POST back to itself using the name of the server in the URL entered into the browser. In Windows Server 2003 SP1 and later, a security feature was added to prevent the passing of credentials in this scenario.

Goodness! Clear to the point of “Why isn’t this better documented” !! This *LoopBack* security is invoked under these conditions (maybe others) in addition to the WinOS version,

1. NTLM security (NOT Kerberos –but who can get all the SPN and Delegation working?)
2. Server hosting the Sharepoint site and the Sharepoint site have different DNS.

Condition #2 is the big *got-ya* as MS documentation doesn’t properly account for this scenario which I think is common enough.

FIX: The MS KB article http://support.microsoft.com/kb/896861 does explain the needed registry tweaks. I used method 2 that specifies host names *BackConnectionHostNames* new registry value that are allowed loop-back.

  1. In Registry Editor, locate and then click the following registry key:
  2. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
  3. Right-click MSV1_0, point to New, and then click Multi-String Value.
  4. Type BackConnectionHostNames, and then press ENTER.
  5. Right-click BackConnectionHostNames, and then click Modify.
  6. In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK.

I added the DNS of the Sharepoint Website and reset IIS. It Worked! Sharepoint and RS were working in Integrated mode (hosted on different servers).