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.

http://my.opera.com/duncans/blog/2009/01/14/no-reporting-services-section-in-application-management-after-installing-rs-ad

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:

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]

After attempting to install the patch KB967831 on OCS 2007 R2, if you don’t have MS message queue services installed, the install will fail and prevent the front end service from restarting. To fix you need to install the MS messaging queue services + server and reapply the patch. The front end service will then start again without issue.

To install these services quickly, from cmd run:

• ServerManagerCmd -i MSMQ-Services
• ServerManagerCmd -i MSMQ-Server

http://blogs.technet.com/exchangept/archive/2009/05/21/ocs-front-end-service-fails-to-start-after-kb967831.aspx

For whatever reason your SharePoint server is dead. The database checks out ok and is still accessible but you cannot get the portal to load and need to extract a critical document for an executive meeting 5 minutes from now.  By default, SharePoint stores documents in the SQL Server database as BLOBs (Binary Large Objects) and cannot be easily exported without assistance from the presentation tier.

Pascal Gill decided to write a custom application to solve this very problem and did so handily. His creation, SharePoint Database Exporter, can connect directly to the WSS_Content database and provides the ability to save any stored version of any document in the database in its native format. This includes the ability to capture custom lists, metadata, and even web parts.

Simply launch the utility (a single, self-contained executable), log in to the database, find the content you need to extract, and save it off somewhere. Done. Clean, simple, incredibly effective.

http://blog.dreamdevil.com/index.php/2007/03/13/sharepoint_2003_database_exporter/

If you’ve ever tried to enable SQL Reporting Services, after the fact, on an existing SQL server you may have noticed the Reporting Services button greyed out in the setup wizard. SSRS depends heavily on IIS and needs some very specific components installed before it will become available for install. These dependencies also exist in Server 2003, of course, but enabling them is very different in Server 2008.

http://support.microsoft.com/default.aspx/kb/938245/

The aforementioned KB walks through the steps of enabling via the Server Manager MMC, so here is the list of essential IIS role services that must be available to the server before SQL setup will allow you to install SSRS:

• Static Content
• Default Document
• Directory Browsing
• HTTP Errors
• HTTP Redirection
• ASP.NET
• Windows Authentication
• IIS Metabase Compatibility
• IIS 6 WMI Compatibility
• IIS 6 Scripting Tools
• IIS 6 Management Console

I stumbled across something very interesting today regarding these two components that is worth sharing, especially for anyone else managing a Server 2008 environment. Many of us got used to disabling the firewall in Server 2003 unless we needed to solve very specific security requirements with limited resources. The Firewall in Vista/Server 2008 has been completely revamped and is now a truly stateful firewall complete with profiles, profile-specific rules, and the ability to separately manage inbound/ outbound traffic. Host-based firewalls certainly add complexity to any environment and one has to be very cognizant of hosted services as well as consuming applications and users. Troubleshooting can be frustrating if you forget that “oh yeah that server has its firewall turned on”.

In my current dev environment I disabled the firewall on all servers, 2003 and 2008, via GPO. This is how you do it in XP and 2003 so the same applies to 2008, right? While this does in fact disable the firewall in 2008, there appears to be an underlying system dependency on the firewall service in Server 2008. I installed the April Windows updates on both a Server 2008 STD (x86) VM and a Server 2008 STD (x64) physical host. After rebooting, both servers came back up fine but were completely inaccessible from the network. No PING, no RDP, no CIFS. From the servers themselves, outbound traffic flowed without issue, to the internet, ping other hosts, browsing, etc. This was baffling especially since the firewall was disabled. Nothing should be blocking traffic! I followed the usual troubleshooting methods of digging through logs, checking driver revs, I even added a new vNIC and reinstalled vmTools in my VM thinking that maybe the virtual device had become corrupted somehow. Nothing worked!!

So I decided to do a packet capture to dig a bit deeper hoping that maybe the answer would become clear in the annals of the IP stack. Ok I see ICMP echo-requests coming into the server but it’s not replying, hmm. Let’s try a telnet to TCP 3389, I see SYN messages but the server isn’t sending a SYN-ACK back, what is going on here?? It seems that the server is flat choosing to not respond to inbound traffic. The firewall is definitely disabled.

Thinking this had something to do with Windows update, I opened a sev3 case with Microsoft. In the meantime I continued to troubleshoot and finally developed a work-around before they called me back. There is no stated dependency or published best practice (that I’ve seen) that recommends leaving the firewall enabled on Server 2008 lest you risk network problems. I think most people assume that it’s a security feature that can be safely disabled without adversely affected normal server performance or function [as it should be]. To fix the problem I enabled and started the firewall service, then in the firewall management MMC set all 3 profiles to allow/allow for inbound/outbound access. Both problem servers are now accessible and functioning perfectly on the network.

This experience changes the way I will setup Server 2008 environments as it appears the firewall is a mandatory service that the network service depends upon for normal operation. Microsoft doesn’t have a KB or any information yet about this issue so the support people are kicking my case up to the product teams for review. I also submitted a change request on the Connect site so hopefully someone will take notice and at the very least write a KB stating that the firewall service is a required component of Server 2008. Ideally this dependency will be dropped altogether but we’ll see. Here is my submission:

https://connect.microsoft.com/WindowsServerFeedback/feedback/ViewFeedback.aspx?FeedbackID=464554

To sum: leave your firewalls turned on in Server 2008 and if you wish to make them transparent then do so by allowing all traffic inbound/outbound for all profiles. Don’t disable the service!!