It's odd that even today the best article on how to install SQL Server 2005 SP2 Reporting Services is a SharePoint Team Blog from February but there (if I haven't missed something) you are.
(http://blogs.msdn.com/sharepoint/archive/2007/02/19/microsoft-sql-server-2005-sp2-reporting-services-integration-with-wss-3-0-and-moss-2007.aspx)
(Aside: It's good that the SharePoint Team Blog mostly doesn't just repeat information from other team blogs about the release of beta products - see today's other blog item here)
I've been working through that article and it could do with some re-writing in my opinion - as a blog item it's fine, but if MS ever produce a white paper or TechNet article onthis subject they will need to improve the text quite a bit.
The first thing that I found confusing is the use of the "Reporting Services" term and the "Report Server" term. This has little to do with the writer of the piece however and more with the insane fact that when you in SQL Server 2005 click on "Reporting Services configuration" (Programs->SQL Server 2005->Configuration Tools->Reporting Services configuration) you get a screen headlined "Configure Report Server".
Yes, you saw that correctly you select ReportING ServICES and you get Report ServER. How mad is that!
Then of course this is all about installing the Reporting Services *Add-In* so there is an over-abundance of Reporting Services which the documentation doesn't make a really great job of distinguising among.
Anyway enough of the general mutters, what about some specifics.
There's first a list of what components need to be installed.
- SQL Server 2005 Reporting Services
- SQL Server 2005 Service Pack 2
- Office SharePoint Server 2007
- Microsoft SQL Server 2005 Reporting Services Add-In for Microsoft SharePoint Technologies
(Aside: why is SQL Server 2005 ... and Office SharePoint Server enough for those items but the final name - which is long enough anyway - has a Microsoft shoved in front of it?)
Anyway you see that list; think you've already installed SQL Server 2005 Reporting Services; the SP2 and MOSS 2007 and so you now install The Add-In.
WRONG.
This is just a list. Preparing the ground as it were. Don't you hate it when they do that?
Instead you have to read on and here there's a para on "Report Server Configuration". *This* is the first thing you do (*before* installing the Add-In).
This is a completely useless paragraph - except that is for the information that you need in a **screen** headed "Configure Report Server" to have a tick next to the SharePoint Integration item.
What it misses are many key points.
1. You access this page by "Reporting Services configuration" (how - see my earlier mutters above)
2. When you do there is a question mark next to SharePoint Integration.
This it says is because you haven't specified a Database for reports. You go to Database Setup and see that there is a database specified for Reports. You try just saving that. I can assure you it's not going to be enough.
Instead what you need to do is to create a NEW Database for reports or amend the existing one (which SQL Server 2005 Reporting Services installed for you which the writer of the paper knows you have done ....)
I created a new database just to be 100% sure.
3. That new database needs to have "SharePoint Integration" selected (the same page where you create it - i.e. in the Database Setup part of the Configure Report server page).
Now finally you have a tick next to SharePoint Integration and your own "Configure Report Server" page looks like the one in the Team Blog.
Only now can you install the Add-In!
(The paper doesn't bother to tell you this (the next para starts "After installing the .... Add-In ... a new application becomes available) so if you were reading through this paper before installing anything you would in fact already have installed the Add-In before doing the necessary Report Server Configuration change ...)
OK. So so far the steps are
1. Run Reporting Services configuration so that you can amend the Configure Report Server page (!)
2. Install the Add-In.
You are then supposed to see a new "Reporting Services" section in Central Authorization/Application Management.
Chances are that there's a 50% chance that you won't.
That's because again there is something missing from the instructions (if you can even call them that) in the team blog article. In this case it's the information that you need to be logged in as the user that you used to install MOSS 2007.
In my case I was logged in as the Administrator of the server which was a fine choice for doing the SQL Server (Report Server / Reporting Services) configuration changes. But try (and re-install; and do occasional iisresets etc.) as I might I never saw the Reporting Services section in Application Management after installing the Add-In.
No, instead, you need to (first as Administrator uninstall the Add-In); log-out; log back in as the user used to install MOSS 2007 and only then install the Add-In.
Now you will, finally, see the Reporting Services section in Application Management.
You'd think that would be worth mentioning wouldn't you ?
The next step is described in in the Team Blog as "you must set each of the options ... as follows"
- Manage integration settings – To configure the Report Server Web Service URL and Authentication Mode (either Windows or other trusted account).
- Grant database access – To configure the name of the report server database server instance name.
- Set server defaults – To configure report server timeouts and other options.
Based on previous experience of this Team Blog, I doubt if that's going to be that straightforward (if you look at the above text for the second two items, all it seems to say is that you need to configure somethings - not what to configure them as!), so for today (or at this this blog) I'm going to quit while I'm ahead.
P.S. I'd like at this point to express my thanks to Corleen Heiderken for posting a response to my newsgroup question asking why "Reporting Services" wasn't appearing in Application Management. I was thinking of looking there next, but it was good to get a quick confirmation in advance.
P.P.S. I couldn't, of course, leave well alone and I tried to configure all three of the items listed under Reporting Services. It *seemed* (but who really knows) that the first two went OK (at least they didn't crash and burn) but the third one crashed and burned even before I got it opened claiming that "An unexpected error occurred while connecting to the report server. Verify that the report server is configured for SharePoint integrated mode."
There's a KB article for this message but it's only for Forms Authentication which i don't have. My weekend starts here so I'll now be wondering about it all that long weekend. I ought to have stuck to having a break after getting something working instead. (I have no idea whether this problem is caused by filling out the two earlier items incorrectly or not btw - I filled them out with things that were a) valid (produced no error message) and b) seemed reasonable. But what do I know.