Thursday, June 10, 2010

Reporting Server 2005 Add-in for SharePoint Technologies Not setting up correctly on SharePoint Server 2007

I was trying to setup Reporting Services 2005 Add-In for SharePoint Technologies. Existing Production Environment is
(1) Server1 - Web Server (64-bit) - Windows Server 2008 R2, Office SharePoint Server 2007, IIS 7.0 - 64-bit softwares are used in this box
(2) Server2 - Database Server (32-bit) - Windows Server 2003 Ent Edition, SQL Server 2005 Ent Edition, Analysis, Integration & Reporting Services - are installed.

I thought of creating Reporting Services on Web Server (Server1) and with this thought, we installed SQL Server Reporting Services 2005 (32-bit) on Web Server. Initially configuration check was failing because 'Enable 32-bit Applications' setting was 'False' in IIS. I changed it to True and validation succeeded. Installation also completed without any further problems. Ran Reporting Services COnfiguration wizard to set the configuration settings, we gave Database Server name as Server2 and Instance name as MSSQLSERVER. It is done without any problem. Before setting up the SharePoint integration mode, we checked the SharePoint Portal server, and found that neither the portal nor the central admin page is accessible. Panic created because it was production portal web site. Checked all configurations, everything looks good, but still the problem persist. As soon as I disabled 'Enable 32-bit application' and set its value to 'False', Central Admin and Portal Web Site started working again. So conclusion came out that we can't install SSRS on Web Server because web server is running on 64-bit hardware and software, however, SQL Server database is running on 32-bit OS and softwares. So we decided to install SSRS on database server (as there is no separate box available for Reporting services). So we changed 'Enable 32-bit application' to true and then un-installed SSRS from Web Server. Restarted the web server, and changed 'Enable 32-bit application' back to 'False'. Portal web sites and central admin web site was running fine.

Now we installed SSRS on Database server (Server2), and configured reporting services in there. We also installed WSS 3.0 (32-bit) on database server and add it to existing web farm. We configured reporting services into SharePoint Integration Mode, without any problem using domain service account. In fact, we logged to these machines using this service account. All configuration is done and accessible.

Further we need to install Reporting Services 2005 Add-in for SharePoint Technologies on the web server. We installed (64-bit) version on Web Server (Server1). Double click installation was failing so adapted another suggested way. Downloaded SharePointRS_x64.msi locally, and then from command prompt, executed this command SharePointRS_x64.msi SKIPCA=1. This extract a file named rsCustomAction.exe to the %temp% location. From Command Prompt, executed command cd %temp% to go to Temp directory, and then executed rsCustomAction.exe -i. Installation completed without any problem. Given the log details:

User: svcSharepoint
Installing Report Server feature.
Calling copyappbincontents command.

copyappbincontents command completed successfully.
Adding Report Server feature to farm.
Installed Report Server feature.
Activating Report Server feature to root level site collections.
Activating feature in web app 'SharePoint - 90'
Activating feature to root site collection: http://Server1
Feature already activated in site collection http://Server1
Activateing Report Server feature in all admin site collections.
Activating feature in web app ''
Activating feature to root site collection: http://Server1:24920
Feature already activated in site collection http://Server1:24920
Updating DocIcon.xml file
DocIcon.xml file updated.
Beginning install of cab files.
Calling HCInstal for lcid '1033'
Outcome code is: 4
Calling HCInstal for lcid '3082'
Outcome code is: 4
Calling HCInstal for lcid '2070'
Outcome code is: 4
Calling HCInstal for lcid '2052'
Outcome code is: 4
Calling HCInstal for lcid '1055'
Outcome code is: 4
Calling HCInstal for lcid '1053'
Outcome code is: 4
Calling HCInstal for lcid '1049'
Outcome code is: 4
Calling HCInstal for lcid '1046'
Outcome code is: 4
Calling HCInstal for lcid '1045'
Outcome code is: 4
Calling HCInstal for lcid '1044'
Outcome code is: 4
Calling HCInstal for lcid '1043'
Outcome code is: 4
Calling HCInstal for lcid '1042'
Outcome code is: 4
Calling HCInstal for lcid '1041'
Outcome code is: 4
Calling HCInstal for lcid '1040'
Outcome code is: 4
Calling HCInstal for lcid '1038'
Outcome code is: 4
Calling HCInstal for lcid '1036'
Outcome code is: 4
Calling HCInstal for lcid '1035'
Outcome code is: 4
Calling HCInstal for lcid '1032'
Outcome code is: 4
Calling HCInstal for lcid '1031'
Outcome code is: 4
Calling HCInstal for lcid '1030'
Outcome code is: 4
Calling HCInstal for lcid '1029'
Outcome code is: 4
Calling HCInstal for lcid '1028'
Outcome code is: 4
Cab files installed successfully.
Stopping W3SVC service.
Starting W3SVC service.

After installing, followed the instructions to configure the settings in Central Administration - Application Management Tab. I found 'Reportion Services' section. Clicked Manage integration settings and gave values for Report Server Web Service URL as http://server2:8080/reportserver. Set the value of Authentication Mode as Trusted Account. In Grant Database Access section, I noticed that Report Server name is appearing as Server1 (which is my webserver and not report server). Changed this name to Server2. Click Ok pops up a dialog box to ask about the reporting services service account details. I inputed the correct service account details and click Ok. In Set server defaults, I did not make any changes.

With these settings, I thought that I am done with my configuration settings. Further I created a simple SSRS report using Business Development Studio from Database Server (Server2) and published the reports directly to SharePoint Portal under Reports -> Reports Library. Publishing succeeded without any problems. When I clicked the report RDL file, it gives me 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.

Enabled the tracing on the web server, and access the portal directly from web server, i found this error:

File not found.

Both webserver and database server are running under NTLM authentication. I ran the following script from command prompt:

cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"

So now the question I am asking to myself, where exactly it is searching for the report? When I further checked the configuration values set under 'Grant database access', I found that Server Name is still pointing to Web Server (Server1) instead of correct reporting services database that is running on Server2. So now I am stuck as none of the reports are accessible. And I have no clue what else I can check.

---------------------------
I posted this description on SQL Server Reporting Services Forum, but didn't receive any response in few days. Finally opened a ticket with Microsoft and they resolved it. Here is the resolution.
1. Checked if both Server1 and Server2 are running with same version of SharePoint. It was different. Server1 installed with Office SharePoint Search Server 2007 Enterprise Edition 64-bit, and Server2 installed with WSS3.0 (32-bit). So un-installed WSS3.0 from Server2 and installed Office SharePoint Search Server 2007 Enterprise Edition (32-bit as machine is 32-bit). Since reporting services add-in needs SharePoint Object Model to be installed/present on the machine, I configured this installation as 'Web Front-end Only' and attached it to existing web farm. Definitely I dont want this server to host any web application so opted the choice accordingly.
2. Checking "NTLM" Authentication set on Report Server (Server2). He checked this option using this script from command prompt:

C:\inetpub\AdminScripts>cscript adsutil.vbs get w3svc/NTAuthenticationProviders

With this command it is identified that IIS is configured with NTLM authentication. I had executed the check earlier and set it to NTLM using command:

C:\inetpub\AdminScripts>cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"

Further, check if Report Server IIS Web Site authentication is NTLM. To check that, use following command:

C:\inetpub\AdminScripts>cscript adsutil.vbs get w3svc/812193046/root/NTAuthenticationProviders Where 812193046 is website identifier related to my machine.

Yours could be different so use it accordingly. If the value is not set or is not NTLM, use the following to set appropriately:

C:\inetpub\AdminScripts>cscript adsutil.vbs set w3svc/812193046/root/NTAuthenticationProviders "NTLM"

3. Checking Disable Loop Back Check registry entry on Web & Report Server (Franky speaking, I do not know much about disabling Loop back check, however, it is required to set on web & report server) - As I said, it is required to be set in Web front end & Report Server machines. Here are the instructions. On the web front end, open registry using regedit from Start->Run.

(b) Navigate to the path: Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa and on the right side, if you do not find the name as DisableLoopbackCheck, then create one by:
(a) Right Click white space, click New-> DWORD (32-bit) Value (My WFE is 64-bit machine). Give the name as DisableLoopbackCheck (case-sensitive). The value should be 1 (Decimal).
Do this change on Report Server machine too.

4. Final changes are required in SharePoint Central administration -> Operations -> Global Configuration -> Alternate Access Mapping.
It is important to note that Reporting Services 2005 Add-in works ONLY with the website defined in Default Zone.
In my situation there were two zones (a) Default zone, the internal url was http://server1 and (b) Intranet zone, it was http://portal.company.com/.
I was trying to access the reports using http://portal.company.com/ and getting file not found error because of the said reason. I swapped default zone and intranet zone Internal URL. It is important to note that http://server1 internal URL must exist in any of the zone (other than default).

With these changes, republished a SSRS report on to the portal and accessed it correctly.

So one milestone achieved where SSRS reports are accessible directly from SharePoint Portal. Now the next task was to publish the Analysis Services based SSRS reports to Portal. Publishing is easy, however, those reports will not be accessible and you may notice this error:

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

And if you try to access the report directly from Web Server (assuming that trace is on), you will see 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. --> Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized.

With the error description, it is clear that current logged on user is not authorized to access cube data. It is by design that Analysis Services Cubes run under 'Windows Integrated Security'. So how to get pass by this problem, when NTLM security is set for Reporting Services 2005 with SharePoint. There is a solution for that. Follow the steps to check and set some settings:

1. On Database server where report server database exist (in my case, report server is configured on database server {Server2}). Start SQL Server Surface Area Configuration. This option is available here: Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

2. Click Surface Area Configuration for Features. Expand Reporting Services. This option is available under default instance of MSSQLSERVER. Click 'Windows Integrated Security' and clear the check box on the right with the option 'Enable Windows Integrated Security for report data source connections' (if selected).

All set here with a change in the Cube based data source that you must create a new Report Data Source, say DataSource1. Other values are:
a. Name (Required): DataSource1 (.rsds)
b. Data Source Type: Microsoft SQL Server Analysis Services
c. Connection String: Data Source=;Initial Catalog=. For example, if Analysis Services Database Name is AdventureWorks 2008 DW, then this value must be in double quotes while defining in Initial Catalog. Initial Catalog="AdventureWorks 2008 DW"
d. Credentials: Click 'Stored credentials' and then define domain service account in user name as DomainName\DomainUserName, in password box, type valid domain user password. Make sure to select check boxes 'Use as Windows Credential'. Also, this domain service account user should have access to the cubes appropriately. If not it will not work.
e. Availability: Make sure that 'enable this data source' check box is checked. If not, then this data source can't be used at run time for reports.
f. Click Ok to save the changes.

Appropriately set the Report data source as this newly created data source and you are all set.

4 comments:

MANOJ KUMAR said...
This comment has been removed by the author.
Vani said...

Recently I discovered a great site on reporting and document generation in SharePoint. SharePoint Reporting provides reporting and document generation add-on to SharePoint.It has basic info for all the vendors.

Anonymous said...

I can not get the Sharepoint URL to work with reporting services. In the web part, I get 401 not authorized. If I try and view the report it has the generic error that it can not be accessed. If I go the Reporting services integrated mode url the report pulls up fine. Reporting services is 2008 R2 running on a separate server than the Sharepoint central admin and wfe but the reporting services is a part of the farm. Everything configured successfully except when we try and access reporting services in sharepoint url it fails. I read countless posts about NTLM double hop issues. Everything is set to NTLM and I did configure the disable loopback spoke about here. We are so close....just stuck for weeks on this last part.

dyard said...

I followed all the instructions above and still getting this error

Please help
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. ---> Client found response content type of text/html charset=utf-8 but expected text/xml
The request failed with the error message

the rest of the message is not allowed here