MOSS 2007 R2 and SQL 2005 Reporting Services Integration
I finally got this working after banging my head into the wall for 2 days. This is a very tricky implementation on Server 2008 with everything running on ONE box, much more so in a distributed environment. The basic steps of installing SSRS and integrating into SharePoint are well documented and consist of installing SSRS on SQL, installing the Reporting Services Add-in for SharePoint, and configuring. There are a lot of twists, turns, and caveats along the way, however, that can make this a not so simple setup.
The Reporting Services Configuration Manager should be used first to properly setup IIS, the database, encryption keys, and service accounts. No matter what you do or change to make things work, this should come up all greens whenever launched after configuration. If you manually change application pool credentials or authentication schemes, launch this tool again and make sure everything is green. I chose to install the RS virtual directory into my home SharePoint site, not the default website.
The first clue to ensuring things are working properly is the existence of the “Reporting Services” section under Application Management on the Central Admin page. If this new section does not exist, you may have to manually install the SharePoint add-in bits via command line. First run the installer which will copy all the bits without installing:
rsSharePoint.msi SKIPCA=1
Next find rsCustomAction.exe which should be in a temp directory in your user profile. This will deploy and activate the RS features to SharePoint:
rsCustomAction.exe /i
Watch for any permissions errors, correct if any exist, and rerun the installer. Once successful you should see the Reporting Services section in the Central Admin site.
Now you will see 3 new links under the RS section and each should be able to be accessed and configured without errors. Under Manage integration settings I had to set my report web service URL to http://<servername>/reportserver. The SP site alias did not work here for some reason [which may have been resolved in the finally step]. I also set the authentication mode to “windows authentication”. I read that many people set this to “trusted” with a service account but this did not work so well for me.
Next grant your service account database access. Even though this is supposed to be setup during the RS Config tool, I had to manually give my service account rights to the DB in SQL before I could get this working.
The last link, set server defaults, is another hurdle that will truly test if your install is working. For a long while I would get 401: unauthorized errors here which required a lot of fiddling with IIS to get things working. Here is how my configuration ended up to make it work:
- Change auth mode in SharePoint to NTLM and force IIS to use it as well. I didn’t have any luck with Kerberos even after delegating computer accounts and setting SPNs. I just needed the site to work. In cmd, navigate to c:\inetpub\adminscripts and run:
cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"
- Set IIS application pool identities to use service account with right to DB and SP. Managed pipeline mode should be classic.
- Enable anonymous authentication on ReportServer vdir in IIS and set it to use the app pool identity. Ensure that the ASP.NET impersonation is set to use the authenticated user. Leave Windows Authentication enabled.
- On the home SP site, enable basic authentication, set the default domain, and ensure that ASP.NET impersonation is set to use the authenticated user.
Ok, finally, I can get to the set server defaults link on the Central Admin site. Nothing earth-shattering to set or change there but now it is accessible at least. Making progress. So next I tried to add the SQL Reporting Services Viewer web part to a page in SP to test. Click the “load tool” link to choose a report, click the “…” button and after a moment got a new window with yet another 401: unauthorized error. Even entering the path manually and clicking apply yielding the error below in the web part. This one was baffling and took days of scouring the internet before finally discovering a fix. I came very close to calling Microsoft about this after nothing was working.
I came across the following MS KB a couple of times but ignored the work around part, almost positive that it didn’t apply to me. Then I ran across another blog posting that explained the problem and fix in detail. The underlying issue appeared to be because of the way SP uses hostnames, especially when making calls between web services on the server. The fix was to add a new multi-string value in the registry called BackConnectionHostNames that contained both NetBIOS and FQDN’s of the site. Then like magic I could finally choose a report for the web part with no more errors!
KB 896861 via [MSDN blogs]
No comments: