SharePoint integration with SQL Server 2005 Reporting Services
Hello Everyone, I have been banging my head against the wall with trying to integrate SharePoint 2007 and SQL Reporting Services. If anyone could offer any assistance, I would appreciate it greatly. I have been following these 2 websites down to the letter in trying to configure everything: 1. <---Microsoft's Steps 2. wss-3-0-and-moss-2007.aspx <---SharePoint Blog Group Basically my setup is as follows: Server 1 = SharePoint 2007 Server 2 = SQL Server Reporting Services SP2/ SharePoint 2007 services Server 3 = Database Server1 is setupand running SharePoint 2007 fine with no problems. It was installed under the complete option with its own server farm. Then with the Server2,SharePoint 2007 is installedagain, also under the complete complete option,and configured tojoin Server1'sserver farm. In this way, Server2 is able to supply Server1 with services and it has been said that if you are using2 different servers, one for SharePoint and one for Reporting Services, you need to have an instance of SharePoint on the reporting services box. With a SharePoint "front-end" the reporting-services now has a way to also join Server1s farm. This is where the headache begins....the SharePoint install is successful on Server2 and the services are started as evident through the Central Administration page. The next task is to install SQL Server 2005 Reporting Services and then the SQL Server 2005 SP2 patch. After installing SQL Server and the SP2 patch, you go into the SQL Reporting Services Configuration Manager and setup the Report Server, Database, and SharePoint integration. I configure everything to run under the "DefaultAppPool" under IIS, and I get green accepted checkboxes on everything. Finally, the last step is to install Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies on Server1. This add-in actually allows the connection between SharePoint on Server1 to the Reporting Services on Server2. The install is successful and the extra options for reporting pop up under Central Administration page. When configuring the final connection under the Central Admin I referred to the following Microsoft Page: For the "manage integration settings", I set the report server to Server2 and use Windows Authentication. Then for the "grant database access" I use Server2 and login with my credentials... PROBLEM: Then for the final step of setting the server defaults, I click on the link and get the following error: "An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. " I have googled and tried installing/uninstalling several times, but it seems that I keep getting hung up here. When trying to access reports on the ReportServer, the same error message comes up. If anyone is familiar with integration between SharePoint and SQL Reporting Services, and may have any ideas/comments/advice, I would greatly appreciate it. Thanks in advance! =) --A4orce84
June 30th, 2007 3:44am

I had the same error and was able to resovle it by selecting Trusted Account instead of Windows Authenticationin the Manage integration settings in Central Administration.
Free Windows Admin Tool Kit Click here and download it now
August 1st, 2007 11:25pm

Did you ever resolve this. I have slightly different configuration: server 1 - Sharepoint portal server 2 - database and RS but I have the same error. I can set all the settings on the reports integration settings (that appear after installing the add-in) and my RS setup is all green checks. If I set my integration settings to trusted account, I am able to deploy a report to my server, but with trusted account or windows authentication, I can not run a report from sharepoint. I can however run the report, by going to the RS server and browsing to the report in in the library https://server2/reportserver/... I really want to resolve this. any help would be greatly appreciated.
August 24th, 2007 7:50pm

I have been experiencing the same problem. Installation was done on a step by step basis as provided by MS, but we get the same error. I've been working with MS Premier on this and they are still researching and trying to find a plan of action to resolve the issue. If we do get it resolved, I will post our resolution here. If not, and someone else has a fix....please let us know!
Free Windows Admin Tool Kit Click here and download it now
February 11th, 2008 6:58pm

I did the same thing with PSS. Basically, it came down to: Making sure the SPNs were set properly on the account running SRS/WSS web services (needs to be the same account). You have to set up SPNs for each site that will use SRS, including the Central Admin. Making sure that the WSS/SRS front end servers had delegation enabled (Kerberos all services) along with the SRS/WSS web services account. Obviously making sure that the site you're accessing is in the Local Intranet zone of IE (so if it is FQDN, add it).
February 11th, 2008 9:57pm

Thanks....I'll give that a try and see what happens. I'll let you know...
Free Windows Admin Tool Kit Click here and download it now
February 12th, 2008 12:07am

To be a little more indepth: ServerA - WSS ServerB - SRS SQL backend (won't come into play because we're assuming SRS -> SQL works) Central Admin/WSS app pools run under mydomain\mywssaccount. SRS app pool(s) run under mydomain\mywssaccount. In AD, mydomain\mywssaccount has "Trust this user for delegation to any service (Kerberos only)". In AD, mydomain\ServerA and mydomain\ServerB have the exact same option checked as mydomain\mywssaccount. SPNs are set up as follows: setspn -A HTTP/mycentraladmin:8080 mydomain\mywssaccount setspn -A HTTP/ mydomain\mywssaccount setspn -A HTTP/mywsswebsite mydomain\mywssaccount setspn -A HTTP/ mydomain\mywssaccount {...etc...} If you're using host header mode (sites are hosted on the same IP address), you must use A records in DNS. IE doesn't like CNAMEs when it comes to Kerberos authentication. Make sure in the Central Admin that your website using Kerberos authentication. Anything else and this will fail. Make sure that your site in IIS is set up to use Kerberos authentication. You can do this by running adsutil.vbs in C:\Inetpub\AdminScripts: adsutil.vbs get w3svc/SiteID/root/NTAuthenticationProviders If the result is anything other than "Negotiate,NTLM", change it: adsutil.vbs set w3svc/SiteID/root/NTAuthenticationProviders "Negotiate,NTLM" The SiteID can be found in the IIS admin console. In the above, NTLM must be in all caps. Not sure about the 'N' in Negotiate. You can verify that Kerberos is working correctly once in Central Admin you can get to "Set server defaults" on the Application Management tab. For each site, you can verify it by going to Site Settings -> Manage Shared Schedules. From what I understand, if you add a new site after getting everything working with a new database, you should rerun the "Grant database access" under Application Management in Central Admin.
February 12th, 2008 12:48am

I tried to run the command above and get an error that it can't locate the path for NTAuthenticationProviders? I am running on Windows 2003 R2 Standard x64 SP2. Any idea?
Free Windows Admin Tool Kit Click here and download it now
February 16th, 2008 12:16am

That means the key is not set and you need to set it.
February 16th, 2008 5:07am

Where / how does this key need to be set? My focus was to develop the SharePoint sites after installation, there are other System admins that are responsible for the Server and Active Directory. I am assuming that the System Admins need to do something to set this key up, correct? I am wondering if this is what is causing me problems as right after the initial configuration where I choose Kerberos, I can't get into central admin, it keeps asking for my login to the server that I am already on. There is a weird combination of putting in my credentials when it asks me, it gives me up to 3 times before saying I don't have authorization, and in a different combination (where I am providing the Domain name as part of my ID and other times I don't), I get the page Cannot be displayed? Thanks for the help, JB
Free Windows Admin Tool Kit Click here and download it now
February 18th, 2008 5:44pm

In addition to this, every place I have found referencing running this script, it's on avirtual server. This isn't a virtual server that I tried to run this script on, does that make a difference?
February 18th, 2008 5:50pm

No, this is applicable to any IIS website, regardless of the application in use. You need to run this: adsutil.vbs set w3svc/SiteID/root/NTAuthenticationProviders "Negotiate,NTLM" You need to be an Administrator on the IIS box (Sharepoint and SRS boxes) in order to run this. And yes, because this is not set, it is most likely why you cannot get into Central Admin (there is an stsadm command to change a site back to NTLM, but then SRS configuration using "Windows Account" will fail). You will have to have an Administrator of the Active Directory computer objects (again, WSS/SRS) and User objects (your WSS user) in order to set the Kerberos Delegation options.
Free Windows Admin Tool Kit Click here and download it now
February 18th, 2008 6:40pm

My white paper on SRS reports on SharePoint lists might help.
March 19th, 2008 6:50pm

Hello All, I have been stuck with the reporting service configuration with RS. Here is how my setup is: Server1--> Running MOSS2007 Server2--> Running DB and RS For the background what i have done so far is, i have my RS app pool, Central Administration app pool report server windows Identity and WSS services configured on one and the same domain service account I faced a problem that was, when i configured my RS and i tried to access the URL with the server name in it i.e. http://Server2:9090/ReportServer it prompted me for the username and password. I got rid of this problem by following the workaround on KB article i don't think it would be possible in my company to get an approval for any changes in AD. so right now my IIS on both the servers is running with NTLM as follows: cscript adsutil.vbs get w3svc/NTAuthenticationProviders NTAuthenticationProviders : (STRING) "NTLM" That problem got fixed and i can access the report server url with name in it. Also my Central Administration webapp is running with NTLM every thing went on fine until i realezed a very strange problem: When i try to access a report deployed on a sharepoint website from any computer other than the server which is hosting the Central Administration Web app by which i mean i remote desktop logon to the server to make it work or elsei get an error as follows: "An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode." Infact this is not the end, even when i try to access the "Central Administration > Application Management > Set Server Defaults" Under the Reporting Services add-in menu in Central Administration on any other computer than the MOSS2007 hosting the SharePoint Central Admin App, i get the same error. None of the above even seem to work from the Reporting Services/DBMS server. Soreally the RS integration for me right now is working only from MOSS2007 Server Hosting Central Administration by remote logging in to the server. and i don't know why. I have not used localhost or anything like that for my configurations anywherethough and also my central admin application is running under a domain services account. Does anybody have any idea whats going on? or is anyone of you facing something similar? It would be really apperciated if anyone can help.
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2008 9:53pm

If under "Manage Integration Settings", you're attempting to use Windows Authentication, then you must have Kerberos/SPNs set up properly (this doesn't require a modification to Active Directory, just the computer/user accounts). If you are using a Trusted Account, then you can utilize NTLM.
May 16th, 2008 10:18pm

Thanks Relix, That is exactly what i figured today, infact i was going to post an entry to this thread for my findings but you happen to mention it already. I appericate your help really and that is absoulty rite what you said. I just wish that this information should have been more exposed by microsoft on msdn or blogs or somewhere, because it took me a long time to figure this out and get the things working rite.
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2008 11:47am

Hi, There are several different issues in this post. But I am going to only answer one of them,... I will not discuss Kerberos other than provide the best source of information on the internet.. There are a couple of great post by Spence on Kerberos that explains how easy it is to set up along with a configuration utility that will tell you the correct SPNs to use... Spence is one of the original MVPs and if your not subscribed to his blog, and you think you know SharePoint.. Where have you been? TechNet Misinformation on Kerberos Understanding Kerberos in your environment Kerberos Configuration Utility for SharePoint 2007 SRRS Integrated mode pre-requisite everyone seems to miss, I dont get it ensure SSRS is installed on one of your SharePoint Servers that is part of the farm or install SharePoint on the SQL Server hosting SSRS. The first option is obviously the Correct one. The SQL Database Server hosting the SQL Db Engine can support multiple instances of SSRS native and integrated. The SQL Engine also support SSRS version N+1, in other words SQL 2005 DB Engine can Support SQL 2008 RS... -Ivan Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
September 5th, 2010 12:12am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics