Service Pack 2
(Configuration Woes for Sharepoint Integrated Mode)
I finally got the SQL Server Reporting Services 2005 to run in Sharepoint Integrated Mode and can now see Reports.
Story:
We were evaluating Reporting Services and Sharepoint and wanted to see how well both of these technologies could help leverage our Business. So, tried to setup a Lab Environment. As I already mentioned in my previous blog entry, we had an Image of Windows Server 2003 SP2 and SQL Server 2005 SP2 with Reporting Services 2005 SP2 and MOSS 2007 loaded.
I started with this image and tried to set up the Integration Mode.
So, first downloaded the Reporting Services Addin here and installed it and configured the server per the documentation. I followed the links below:
http://technet.microsoft.com/en-us/library/aa905871.aspx
http://blogs.msdn.com/sharepoint/archive/2007/08/02/microsoft-sql-server-reporting-services-installation-and-configuration-guide-for-sharepoint-integration-mode.aspx
This second one above was extremely helpful when I went back and checked on some things that I overlooked the first time and configured later while I was troubleshooting below.
Issue 1
It appeared that the Addin installed fine, but when I went to look in the Central Administration, I could not find the Reporting Services Section under Application Management at all….what did I do wrong?
After some research, found out that if your Sharepoint Site Collection was not available or if the account you are using to install Reporting Services Addin does not have full control over that Site Collection(you are concerned about) when you install the Reporting Services Add-in, then the Reporting Services Integration Feature is not activated for that Site collection.
If you activate it, by doing the steps below for the site collection, you can see the Report Viewer webpart and also add Reporting Services Content Types.
http://blogs.msdn.com/rosettaue/archive/2007/04/06/how-to-activate-the-report-server-feature-in-sharepoint-central-administration.aspx
Okay…I uninstalled add-in and reinstalled with an account that was the Site Collection Adminitrator/Farm Administrator and Local Administrator of that machine. I could have done the above for my site collection in question and it would have worked.
After that I could see the Reporting Services section under Central administration. However, when I clicked on “Set Server Defaults” option is Central Administration, I was getting the following error:
Issue 2
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized.
Looked all over internet and even posted the question to the forum. I followed the suggestions some people had in Brian Welckers Blog and couple others, but no luck.
I tried the following workarounds:
http://support.microsoft.com/kb/871179/en-us%20:%20Here%20I%20actually%20tried%20setting%20up%20my%20environment%20to%20“NTLM”%20(workaround)%20as%20I%20was%20working%20with%20an%20image%20and%20could%20not%20get%20the%20spn%20to%20work.: Here I actually tried setting up my environment to “NTLM” (workaround) as I was working with an image and could not get the spn to work.
http://stevenharman.net/blog/archive/2007/03/21/Avoiding-the-401-Unauthorized-Error-when-Using-the-ReportViewer-in.aspx: Here I even modified the registry, even though I was working with Windows Server 2003 SP2…which may not have needed this…
Issue 3
Well. I was tired of getting the above error message to go away and thought that as Set Server Defaults was just an optional requirement, I would go ahead and add the Report Viewer Web part to my Sharepoint Site. I was able to add the Report Viewer web part , but when I tried to create a shared data source or tried to choose an rdl to load the report viewer….I was getting an error
Well, I tried a couple more things….
I added SSRSWebAccount that I was using to run my Reporting Services Web Service (which also is my Report Server ApplicationPool account) to the Reporting Services Database (in my case the name of the database was OSS1) by going to the SQl Server Management Studio and manually assigning it. For some reason, it was not there to begin with.
I gave RSExec permission to SSRSWindows Account on Reporting Services database.
In Central Administration, I have Authentication Providers for my Site Collections set to Integrated Authentication..Kerberos…
In Central Administration, Reporting Services -> manage integration settings… I have the Authentication Mode set up to Trusted Account.
None of this helped and suddenly I looked at the Reporting Services’ Database Version and noticed that it was 9.0.0.33. I knew that SP2 version was 9.0.03042. The version that was loaded on that image was the December, 2006 pre release version
So, what does it hurt…so I went and grabbed the latest released version of SP2 9.0.03042 and applied it on my server. After that I went back and filled in any missing configuration that I applied above. Most of it was already there.
Okay….tried now going to Set Server Defaults,…yippe…it worked…
Both of my above issues went away after I installed SQL Server 2005 SP2 9.0.03042.
It was strange that I did not have to setup spn’s even though I have my Site Collections as Integrated…Kerberos as stated was required somewhere in the docs… I still don’t completely understand that inner workings of all this…but it works well now.
Remember, all the workarounds that I applied (as stated in Issue2 and Issue3) on the server still remain. I did not undo any of that…so all that with the SP2(3042) may be what helped me solve this….
Good Luck to those of you….on your journey to Configuring Reporting Services in Integrated Mode.
