SQL Server 2008 Reporting Services Integration with MOSS 2007

So, time to see how SQL Server 2008 Reporting Services gets on with integration with MOSS 2007.  It all starts very well, SQL 2008 install has been redesigned, and all for the better.  Makes it much easier and consistent to install and all in all very good.

The Reporting Services set up only gives you a few options on install.  The following screen shot gives an idea of how simple it is, and allows you to be prepared to integrate with MOSS straight out of the box:

This all goes very well , and installs smoothly (may be worth mentioning that our test environment has SQL 2005 on it, and therefore 2008 was being installed as an instance that also went smoothly).  This is unfortunately where the pleasure ends, and the pain begins.  On opening SQL manager, it seems that it wants to set up Reporting Services straight away, however falls over with a raft of errors – as this is still a CTP, I’m sure MS are resolving this!  The first question you get is the following:

Beyond it falling over the first time it runs, subsequent attempts result in nothing at all happening.  However do not panic, the reporting services configuration manager still exists.  This has been cleaned up somewhat, but in essence it appears to be the same tool.  Confirm that this is set up correctly by clicking on the URL in the Web Service URL part.  This should go to a basic page with as if you are looking at the file view.

Next, if you haven’t already done so – install the Microsoft SQL Server 2008 Feature Pack – can be found here for download:  http://www.microsoft.com/downloads/details.aspx?FamilyId=D68DE3C9-60A9-49C9-A28C-5C46BBC3356F&displaylang=en

The file you are going to need is called rsSharepoint.exe, download this and install it on your SharePoint 2007 Server.

Now, if you are lucky, unlike me, you will be able to go the Central Administration Console and under “Application Management” you will see a reporting services tab, if you do then great, but if you do not have it – don’t despair this is quite common.

Go to the Central Administration console for SharePoint services, and click site actions on the top right.  The Select Site Settings, and click Site Collection Features, you may see a whole host of options, depending on what you have already installed.  Look for “Report Server Integration Feature” and click activate.  All done, now you will see a Reporting services section in Application management.

Ok, so we have reporting services set up and running in integration mode, and have SharePoint able to connect to the Report Server.  Get the address of the report server, ensuring that it is not pointing to local host.  Now Click “Manage Integration Settings” in Application management of SharePoint Central Administration:

You can either use Windows Authentication, or Trusted account, note that if you want to use Windows authentication you may need Kerberos set up for cross server authentication.  Also If SharePoint is configured to use Forms Authentication, this setting will be ignored.

Now you need to grant database access, this allows the report server to access the SharePoint Content Database, and needs both read and write permissions to the content database.  Once this is done you are ready to go.