Reporting Services SharePoint Integration Troubleshooting

SQL Server Technical Article

Writer: Chris Alton

Published: August 2009

Applies to: SQL Server 2005, SQL Server 2008

Summary: This document covers most of the errors you might encounter while installing, configuring, and using Reporting Services SharePoint integration for SQL Server 2008 and SQL Server 2005.

1. Introduction

Installing and configuring Microsoft® SQL Server® Reporting Services integration with SharePoint® services can be a complicated process; if the correct process is not followed, it can be easy to experience problems. Before going into this document, ensure that you have read and followed all the steps in “Configuring Reporting Services for SharePoint 3.0 Integration” in SQL Server Books Online for the version of SQL Server you are using:

SQL Server 2008: https://msdn.microsoft.com/en-us/library/bb326356.aspx

SQL Server 2005: https://msdn.microsoft.com/en-us/library/bb326356(SQL.90).aspx

This white paper will go over the errors you can run into, their possible root causes, and their possible resolutions. There are some other issues you might run into that have already been documented in the following articles. Check them if you are unable to find your specific topic covered in this document:

Troubleshooting Integration with SQL Server 2005 and Microsoft SharePoint Technologies:
https://msdn.microsoft.com/en-us/library/bb969101(SQL.90).aspx

Troubleshooting Configuration Problems:
SQL Server 2008 - https://msdn.microsoft.com/en-us/library/ms159704.aspx
SQL Server 2005 - https://msdn.microsoft.com/en-us/library/ms159704(SQL.90).aspx

This article is organized in a beginning to end format. The article starts with the initial installation of Reporting Services and ends with deploying reports and viewing reports from your SharePoint site. Each section of the document has a section for a specific error you can encounter, the troubleshooting steps, and the resolutions for the problem. Lastly, there is an appendix that includes various items that are not specific to Reporting Services but are used in many places throughout the article.

2. Important Items to Remember

When you run into a problem with Reporting Services integration, there are a few things that will, except in a few extremely rare cases, not assist in resolving the problem. These items are discussed in this section.

2.1 Uninstalling and Reinstalling Reporting Services

There are only a few rare cases where this will help resolve the issue you are having. For example, if the Reporting Services binary files have somehow been corrupted or are not all the correct version, uninstalling and reinstalling can be useful. These types of issues should be very evident, because you should receive various errors in regards to failures loading assemblies or mismatches in assembly versions. These errors can appear in the event log as well as the Reporting Services error logs.

Uninstalling and reinstalling Reporting Services it does not remove the database associated with it. Therefore, if you reconnect to the old database after you reinstall Reporting Services, the problem(s) you were experiencing before are likely to return.

This is also a very time consuming process because you have to install Reporting Services and then apply service packs and hot fixes to get back to the state you were in before you uninstalled the program. Any custom modifications to the Reporting Services configuration files will need to be made again.

2.2 Changing Reporting Services from SharePoint Integrated Mode to Native Mode

This step only confirms that a regular mode Reporting Services works correctly. After it's switched back to SharePoint mode, any existing problems will persist. Everything in regards to the integration is stored in the SharePoint or Reporting Services database and will still be there after Reporting Services is switched back.

If Reporting Services is switched to native mode, part of the installation process is to create a Report Manager virtual directory, which is not deleted after the instance is switched back to SharePoint mode. This can confuse things if someone tries to use Report Manager after the return to SharePoint mode.

3. Reporting Services Initial Installation and Configuration

This section discusses any issues you may encounter during the initial installation and configuration of the Reporting Services instance.

Issue:

3.1 The SharePoint Integration Section of the Reporting Services Configuration Tool Shows Up With a Red X

Possible Error Messages:

"SharePoint content service is null. Report Server may not have joined the SharePoint farm, or Report Server service account may not have been granted access to the farm."

Reasons:

  • The SharePoint 2007 Web front end has not been installed on the Reporting Services server (also known as the report server).
  • The Reporting Services Windows® service and/or application pools are configured to run under the NetworkService or LocalSystem account, and the SharePoint database is stored on the same machine where Reporting Services is installed.
  • The database is not in SharePoint integrated mode.
  • Service Pack 2 (SP2) for SQL Server 2005 has not been applied.

Resolutions:

  • You will need to install the SharePoint Web front end from the media. This does not have to be a full installation, just the Web front end installation. The installer for this should join the Reporting Services server to the SharePoint farm. If it does not, you can rerun the SharePoint Configuration Wizard and follow the steps to join the server to the SharePoint farm.
  • These accounts are automatically denied access to the SharePoint databases if they are located on the same server, because Reporting Services and therefore cannot be used. You will need to change both the application pool and the Windows service to run under a domain account.
  • Verify that the database is in SharePoint integrated mode on the Database Setup portion of the Reporting Services Configuration tool. If it is not, you will need to create a new database in integrated mode.
  • Install SP2 or later for SQL Server 2005.
  • Verify that you have successfully run the Grant database access command from SharePoint Central Administration.

4. Installing the Reporting Services Add-In

This section discusses issues you may encounter when you install the Reporting Services Add-in for SharePoint Technologies.

Issue:

4.1 When you run the Reporting Services Add-in for SharePoint, the Add-in Fails to Install and Rolls Back with No Visible Error Message

4.1.1 First Steps

If you run into this issue, the first thing you should do is run the add-in manually. This will allow you to get the binaries installed and run the custom action (usually the reason the add-in fails to install) manually. If you do this, you can view the messages displayed and the reason it failed without having to run the installer over and over.

Here are the steps to run the installer in files-only mode and running the custom action next:

  1. Open a command prompt.
  2. Change to the directory where the .msi file resides.
  3. Type msiexec /i SharePointRS.msi SKIPCA=1
  4. The installer opens. Just follow the normal flow, and it should finish successfully.
  5. Type cd %temp%
  6. Type rscustomaction /i

This will run the custom action manually and will display all of the messages as it tries to activate the Reporting Services feature and modify the web.config files on the SharePoint site collections.

Note: You only need to do steps 1-4 one time. Then if the custom action fails you can rerun it at any time without running the installer first.

Here are some of the error messages you might receive when running this and possible ways to resolve them.

4.1.2 “A web configuration modification operation is already running”

There are two possible resolutions to this issue. Try them in order.

1. Remove all hung one-time jobs from the Timer Job Definitions page:

  1. Open SharePoint Central Administration.
  2. Go to Operations, and then click Timer Job Definitions.
  3. Sort by Schedule.
  4. Find any jobs that are listed as one-time and delete them.
  5. If there is an event log error associated with the jobs that failed, following the steps in section 10.3.3, “SharePoint Timer Jobs Fail to Run or IIS Manager Is Empty“could resolve the issue.
  6. Remove these jobs by clicking the job name. On the next page, click Delete.
  7. The jobs that we found in the customer case where this was resolved were the following:
    • Windows SharePoint Services Web.Config Update
    • Provisioning Web Application (<SiteNameHere>)
  8. After the jobs are deleted, rerun rscustomaction /i
  9. If the jobs are not succeeding, check the event logs and the SharePoint logs on the SharePoint servers that are failing for further information. For more information about the logs, see section 10.3.2, “SharePoint Logs.”

2. Modify the web.config files for each site in SharePoint.

Note: Any changes made and saved to the web.config for this procedure will cause a recycle of the Web application.

Make the changes to these files as listed here. The sites are usually found in

X:\inetpub\wwwroot\wss\VirtualDirectories\#### (where #### is the site number).

Under <Safe Assemblies> remove this:

<SafeControl Assembly="Microsoft.ReportingServices.SharePoint.UI.ServerPages,
Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Namespace="Microsoft.ReportingServices.SharePoint.UI" TypeName="*" Safe="True" />
<SafeControl Assembly="Microsoft.ReportingServices.SharePoint.UI.WebParts,
Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Namespace="Microsoft.ReportingServices.SharePoint.UI.WebParts" TypeName="*"
Safe="True" />

Under <http handlers> remove this:

<add verb="*" path="_vti_bin/ReportServer"
type="Microsoft.ReportingServices.SharePoint.Soap.RSProxyHttpHandler,
RSSharePointSoapProxy, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" />

Under </system.net> remove this:

<location path="_vti_bin/ReportServer/ReportServiceAuthentication.asmx">
<system.web>
<authorization>
<allow users="*" />
</authorization>
</system.web>
</location>

After you have removed these items from all the SharePoint site’s web.config files, you can rerun rscustomaction.exe /i.

Note: Make sure that you check the X:\inetpub\wwwroot\wss\VirtualDirectories folder and its parent folders for any web.config files that may have been copied into that location, because these files can cause this problem as well.

Note 2: Make sure that there are no files that end in .config, because they can also be read and cause this problem. For example, make sure there is no file named “Copy of web.config”. Make sure the files end an extension other than .config, for example: “Copy of web.config.bak”

4.1.3 “SharePoint is installed, but not configured”

  1. Make sure that the SharePoint Configuration Wizard has been run and the Reporting Services server is part of the SharePoint farm.
  2. Make sure that the user running the add-in is a SharePoint farm administrator.
  3. Verify that the Reporting Services application pool and Windows service are running under a domain account and not NetworkService or LocalSystem when the SharePoint databases are on the same machine with Reporting Services. These accounts are denied access to the SharePoint databases by default.

4.1.4 “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\bin\hcinstal.exe return error code: 8”

  1. Try pausing all Web sites from IIS Manager and rerunning the installation.
  2. This issue can be caused by third-party SharePoint administration tools. One verified application that has caused this is "DOCAVE".
  3. This issue can be caused by an invalid TEMP variable. Check that your TEMP variable points to a valid location by clicking Start, pointing to Run, and then typing %TEMP%. If the folder does not display, or you receive an error, change the location to which the variable points in the Environment Variables program in Control Panel. In Control Panel, double-click System, click Advanced System Settings, and then on the Advanced tab, click Environment Variables.
  4. This issue can be caused by disk space issues on the SQL Server database. Verify that the SharePoint configuration database has enough free space available.
  5. This issue can be caused if the .js file type is blocked on SharePoint. Follow these steps to remove this block:
    • Go to SharePoint Central Administration. 
    • Under Security Configuration, click Manage Blocked File Types.
    • Find JS.
    • Remove JS from the list.
    • Click OK.

4.1.5 “Failed to find the XML file at location '12\Template\Features\ReportServer\feature.xml'”

  1. The add-in installer rolled back the installed feature files, and then the rscustomaction.exe command was run. This causes the required files that rscustomaction.exe needs to run successfully to not be on the system. Run the .msi installer in files-only mode so that the required files will be available. For more information about files-only mode, see section 4.1.1, “First Steps.”.
  2. The user installing the add-in does not have the permissions to access the file in the location specified.
  3. The user installing the add-in needs to be a SharePoint farm administrator.
  4. This can be caused if you have some servers in your farm are using Windows SharePoint Services 3.0 and others that are using Microsoft Office SharePoint Server. Your farm should be all Windows SharePoint Services 3.0 servers or all Office SharePoint Server servers.

4.1.6 “User does not have permission to add feature to site collection: <http://SITENAME:PORT>"

The user installing the add-in must have the correct SharePoint permissions to install the feature on the site collection mentioned in the error. The permissions required should be gained by adding the user running the add-in as a site collection administrator for the site collection in the error.

Note: This will not cause the rsCustomAction to fail. The message is a warning that the feature wasn't activated on that site collection. You can activate the feature by following these steps:

  1. Browse to the site collection you want to activate the feature on as a user who is a site collection administrator.
  2. Click Site Actions.
  3. Click Site Settings.
  4. Click Site Collection Features.
  5. Find Report Server Integration Feature in the list.
  6. Click Activate.

4.1.7 “Cannot complete this action. Please try again”

If this installation of the add-in is on the second, third and so on Web front end server and the full installation has already run successfully on another Web front end then you only need to run the install in files-only mode. (For more information about files-only mode, see section 4.1.1,“First Steps.”) The custom action has already successfully executed on the previous server and pushed all necessary SharePoint changes out across the farm. You do not need to run rscustomaction.exe again.  

4.1.8 “Object Reference not set to an instance of an object”

This error can occur if, after the Reporting Services Add-in is installed and activated on a SharePoint site collection, SharePoint is uninstalled, and the user then attaches the content databases that were previously used with the Reporting Services integration to a new SharePoint Web Application. The error should occur immediately after a message similar to the following that mentions the Web application where the content database could be at fault:

Activating feature in web app '<SharePoint Web App Name>'

Note: Verify that this is the case before you attach or detach the databases.

Following these steps should resolve the issue:

  1. Run RScustomaction.exe /I and note the site collection that appeared right before the failure.
  2. Detach the content database for the site collection by following these steps:
    1. Open Central Administration.
    2. Under SharePoint Web Application Management, click Application Management, and then click Content Databases.
    3. In the drop-down list box in the top right, select the Web application that was mentioned in the error message.
    4. Click the content database in the list.
    5. Note: Remember the name of the database, because you will need to specify it again to reattach it.
    6. Select the Remove Content Database check box, and then click OK.
    7. Note: This does not delete the database; it just detaches it from the site collection.
    8. Click Add a content database at the top of the list.
    9. Change the database name if you want, and then click OK. This will create a new blank content database.
  3. Rerun rscustomaction /i if it fails with the same error again note the site collection it failed on, and redo section b for each site collection that fails until the add-in completes successfully.
  4. Complete the normal Reporting Services configuration steps through Central Administration. (Set server defaults and so on.)
  5. Reattach the content databases by following the steps in section b, but in step vii change the database name to the name of the previous content database for that site collection.

If this does not resolve the issue, your last option is to get a memory dump on the rscustomaction.exe process using WinDBG to determine where the failure occurred. You can do this by following these steps:

  1. Open WinDBG, and click File, and then click Attach to Executable.
  2. Select the rscustomaction.exe file.
  3. In the Arguments box, type /i.
  4. Set the Start Directory value to the directory rscustomaction.exe resides in.
  5. Type g to get the process running and wait for the AV to occur.
  6. If the error is not an AV (0xc0000005), type g to continue execution until it occurs.
  7. After WinDBG breaks on that error, type .dump /ma c:\dumps\mydump.dmp. to write out the dump file.

Issue:

4.2 After You Install the Reporting Services Add-in for SharePoint, You Receive an Error Stating “Server Error in ‘/’ Application” When You Try to Browse Central Administration or Any Other SharePoint Site

Reason:

The add-in has only been installed on one server in the SharePoint farm. The custom action that is run during the add-in installer modifies the SharePoint site collection’s web.config file for the Reporting Services integration and these changes are propagated to all front end servers in the farm. If the add-in has not been run on those servers, the binaries referenced in the web.config files will not have been installed, and the site will fail, because we cannot load those assemblies.

Another reason is when report server and SharePoint are deployed side-by-side, customers have the RS VDir – Reports and ReportServer in the same site as that of Office SharePoint Server and they have a site collection names – Reports (like Report Center).

Resolution:

Install the add-in on all servers in the farm. If you want to make the installation go a little quicker, you can do the following to do a files-only mode installation of the add-in on the other servers. (For more information about files-only mode, see section 4.1.1, “First Steps.”) The full installation that runs rscustomaction.exe is only needed to run once.

5. SharePoint Central Administration Configuration

This section discusses issues you may encounter during configuration of the Reporting Services Add-in from SharePoint Central Administration.

Issue:

5.1 When You Try to Configure the Reporting Services Integration in Central Administration, the Reporting Services Configuration Section Is Not Shown

Causes:

  • The Reporting Services Add-in is not installed, or it is installed incorrectly.
  • The Reporting Services feature has not been activated on the SharePoint Central Administration site collection.

Resolutions:

  1. Make sure the Reporting Services Add-in is installed on the Central Administration server.
  2. Verify that the Reporting Services Integration feature has been enabled. Follow these steps to do so:
    • Office SharePoint Server 2007: On the Start menu, click Programs, point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.
    • Windows SharePoint Services 3.0: On the Start menu, click Control Panel, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
  3. Under Site Actions, click Site Settings.
  4. Under Site Collection Administration, click Site Collection Features.
    Note: Site Collection Features appears only if the user you are logged on as is a site collection administrator for Central Administration.
  5. Locate Report Server Integration Feature and make sure it has Active next to it. If it does not, click Activate.

For more information about activating the Reporting Services feature in SharePoint, see “How to: Activate the Report Server Feature in SharePoint Central Administration” in SQL Server Books Online for the version of SQL Server you are using:

 - SQL Server 2008: https://msdn.microsoft.com/en-us/library/bb677366.aspx

 - SQL Server 2005: https://msdn.microsoft.com/en-us/library/bb677366(SQL.90).aspx

Issue:

5.2 “System.ArgumentException: Value does not fall within the expected range”

When you click Set Server Defaults, you receive the following error:

“System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details.  ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.ArgumentException: Value does not fall within the expected range."

Reason:

One of the SharePoint sites that Reporting Services is trying to get metadata for (usually Central Administration) is not in the location Reporting Services believes it to be.

First Steps:

In the Reporting Services log files, you should find an exception with a top function similar to the following:

“Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:

An internal error occurred on the report server. See the error log for more details. ---> System.ArgumentException: Value does not fall within the expected range.

at Microsoft.SharePoint.Library.SPRequestInternalClass.GetMetadataForUrl(String bstrUrl, Int32 METADATAFLAGS, Guid& pgListId, Int32& plItemId, Int32& plType,

Object& pvarFileOrFolder)”

If you have SQL Server 2005 SP2 Cumulative Update 6 (CU6) or later, you will see an entry above this exception that will tell you the Web site that is causing the issue. The message looks like this:

“w3wp!library!1!04/23/2008-15:54:34:: e ERROR: Error when calling GetSPItemMetaData on path: <http://SERVERNAME:PORT>"

Resolution:

If the Web site listed in the exception is Central Administration (or you don't have SP2 CU6 or later applied) you can follow these steps to fix the issue:

  1. This can occur if there are multiple locations for Central Administration specified in Alternate Access Mappings and the first item in the list is not the one that Reporting Services expects. Follow these steps to resolve this:

    1. In Central Administration, click Operations, and then click Alternate Access Mappings.
    2. Make sure the URL mentioned in the error message mentioned above is the first URL in the list for Central Administration by deleting/re-creating Alternate access mappings to reorder the list.
  2. Use the tool at the following location to fix this issue: https://blogs.msdn.com/calton/archive/2009/05/09/how-to-fix-the-error-value-does-not-fall-within-expected-range-from-srs-integration-with-sharepoint.aspx. Be sure to read the .txt file before you run the tool. If this tool does not resolve the issue, you can try the steps below.

  3. Verify the location of SharePoint Central administration and compare it to the site Reporting Services was trying to get the metadata for.

  4. If the sites differ by port only, follow these steps:

    1. Log on to the Central Administration server.

    2. Open a command prompt on the drive where SharePoint is installed.

    3. Type cd\Program Files\Common Files\Microsoft Shared\web server extensions\12\bin

    4. Type stsadm –o setadminport –port #### (replace #### with the port number for Central Administration).

      When you run this command, you may receive an error similar to the following:

      An object of the type Microsoft.SharePoint.Administration.SPWebApplicationProvisioningJobDefinition named "Provisioning GUID" already exists under the parent
      Microsoft.SharePoint.Administration.SPWebService named "". Rename your object or delete the existing object.

      If you get this error, there is a problem with the provisioning job that updates the Central Administration site properties. You can check the status of this job by following the steps in section 10.3.1, “Check/Manage Timer Jobs.”

      You can try deleting the job and then running the stsadm command again.

      Note: If you delete the job and run the stsadm command, it will succeed, but that does not mean that the command was successful. It will only be successful when Provisioning Job does not show up in the Timer Job Definitions list.

  5. If the sites differ by server name, you can try removing the Reporting Services server from the SharePoint farm and rejoining it. Follow these steps to do this:

    1. On the Reporting Services server click Start, point to Programs, point to Microsoft Office Server, and then click SharePoint Products and Technologies Configuration Wizard.
    2. Follow the prompts, and then click Disconnect this server from the farm.
    3. Follow the prompts to remove the server from the farm.
    4. Rerun the configuration wizard as in step a.
    5. Follow the prompts and join the server to the farm.
    6. Retry Set Server Defaults in Central Administration and see whether it works.

Issue:

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

When you click Set Server Defaults, you receive 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."

Determine Root Cause:

If you are browsing to Central Administration directly on the machine that Central Administration is installed on, the underlying error is usually displayed in the message. If not, try the following steps to determine the underlying cause:

  1. The usual root cause for this error is loopback checking. Loopback checking causes credentials to fail to be passed on POSTs back to the server. This issue occurs only if a host header or DNS name is being used for the SharePoint site. For more information about how to resolve this issue, see section 10.1, “How to Change the DisableLoopbackCheck Registry Key.”

  2. Another usual cause is SSL certificates. If the server is using an SSL certificate, and you are viewing the site over SSL but not using the URL the SSL certificate was issued for, the URL you are browsing to will show up with a red background with a red X next to it in the Windows Internet Explorer® browser.

  3. If you receive this error on a client machine, try viewing the report from the SharePoint server itself to see whether a more descriptive message is displayed.

  4. Check the Reporting Services log files for an error during this time.

    Note: If you see a large number of errors similar to "The permissions granted to user 'DOMAIN\UserName' are insufficient for performing this operation", do not worry. These messages are benign and can be ignored.

  5. If there is nothing in the Reporting Services logs, it's possible we never made it to the Reporting Services machine in the first place. Look in the Internet Information Services (IIS) logs, for the SharePoint site where the error occurred, for a POST to .../_vti_bin/ReportServer. If this request did not fail,  the issue usually resides outside of SharePoint. If this request fails with a 401 due to credentials not being passed, see section 5.4, “The request failed with HTTP status 401: Unauthorized.” Every operation in regards to Reporting Services made through SharePoint goes through a proxy object on the SharePoint server. The _vti_bin/ReportServer is this proxy object. The SharePoint server makes a POST back to itself using the name of the server in the URL entered into the browser. Starting with Windows Server® 2003 SP1, a security feature was added to prevent the passing of credentials in this scenario.

    Here is an example of an IIS log entry that could indicate problems.
    There will be three requests in a row, all without credentials:
    POST /_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 2
    POST /_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1
    POST /_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1

    Here is another example of an IIS log entry that could indicate problems.
    There will be three requests in a row, all without credentials:
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 2
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1

    Here is an example of an IIS log entry that does not indicate problems.
    There will be three requests in a row, all without credentials:
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 2
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 DOMAIN\JoeUser 10.10.10.10 - 200 0

  6. Check the SharePoint log files and the System and Application event logs for any information as to the cause. For more information, see section 10.3.2, “SharePoint Logs.”.

  7. If all else fails, get a network trace on the SharePoint server in question to determine whether a request was made to the Reporting Services machine and how it failed.

Reasons:

The reasons listed in this error message will not be exhaustive as the specific errors you can receive, which are covered later. If this error message is the only one you see, the most likely cause is one of the following conditions: 

  • The underlying error is a 401 (or other authentication error) from the Reporting Services server. This can be caused by a few different things and are discussed in the resolutions section.
  • The Reporting Services Web site is running on a port that is meant for another SharePoint site. (Example: There is a SharePoint site in the farm meant to run on port 1234. The Reporting Services Web site is running on port 1234 on the Reporting Services server.)

Resolutions:

  1. To resolve the issue if it is a 401, follow the resolutions in section 5.4.
  2. To resolve this issue, change the port that the Reporting Services Web site is running on to a port that does not coincide with another SharePoint site.

Issue:

5.4 “The request failed with HTTP status 401: Unauthorized”

When you click Set Server Defaults, you receive the following error: "Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized."

Reasons:

  • The Reporting Services Web service is not configured to use Windows integrated authentication.
    Note: The Reporting Services Web service must use Windows integrated authentication in SharePoint integrated mode. If it is set to Basic, Anonymous, or otherwise, it will not function as expected, if at all.
  • This message can occur if the following items are true:
    • You are accessing Set Server Defaults from another machine rather than from the Central Administration server directly.
    • Reporting Services integration has been configured to use Windows Authentication.
    • Kerberos authentication has not been configured correctly.
  • Reporting Services is installed on the same machine as Central Administration but the Reporting Services site is using a host header, and DisableLoopbackCheck has not been turned on. For more information, see section 10.1, “How to Change the DisableLoopbackCheck Registry Key.”
  • If the customer has more than one domain, verify that the SharePoint and Reporting Services service accounts and the user accounts accessing SharePoint are in domains that have a two-way trust between them. If there is only a one-way trust, there will be problems authenticating users and resources from both domains.
  • The Enable Integrated Windows Authentication option in Internet Explorer is not enabled.

Resolutions:

To change the Reporting Services Web service virtual directory to use Windows Authentication, follow these steps:

  1. On the Reporting Services machine, open IIS Manager.
  2. Locate the Web site that contains the ReportServer virtual directory.
  3. Right-click the virtual directory, and then click Properties.
  4. Click the Directory Security tab.
  5. Under Authentication and Access Control, click Edit.
  6. Make sure that only the Integrated Windows Authentication check box is selected.

There are two ways to resolve this:

  1. Configure Reporting Services Integration to use Trusted Account instead of Windows Integrated. Follow these steps to change this setting:
    1. Open Central Administraton, and then click Application Management.

    2. Click Manage Integration Settings.

    3. Set Authentication mode to Trusted Account.
      Note: You will no longer be able to specify Windows Authentication for your report data sources, because we are not using Windows credentials. If this is done, you will receive the following error: “This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode".

      You can still specify specific credentials for the data source that are Windows credentials.

  2. Configure Kerberos authentication, and then configure SharePoint to correctly use Kerberos authentication. (The overall configuration of Kerberos is documented in a large number of other resources and won't be specifically covered here. The following steps assume Kerberos has already been configured correctly. For more information about Kerberos and troubleshooting, see section 10.2, “Kerberos.”)
    1. After Kerberos is configured, change the authentication providers on the SharePoint sites in order to be able to successfully use Kerberos.
    2. Open Central Administration, and then click Application Management.
    3. Under Application Security, click Authentication Providers.
    4. Click the zone you want to modify.
    5. Under IIS Authentication Settings. click Negotiate instead of NTLM.
    6. Click Save.
    7. Repeat steps iv-vi for each zone/site you want to change by selecting a different Web application from the drop-down list in the upper right hand corner.

Edit the DisableLoopbackCheck registry key using the steps in section 10.1, “How to Change the DisableLoopbackCheck Registry Key.”

To verify that integrated Windows authentication is enabled, follow these steps:

  1. In Internet Explorer, click Tools, and then click Internet Options.
  2. Click the Advanced tab.
  3. Under Settings, scroll down to Security.
  4. Make sure the Enable Integrated Windows Authentication check box is selected.
  5. Close and then restart Internet Explorer.

Issue:

5.5 “The permissions granted to user 'Domain\User' are insufficient for performing this operation”

When you click Set Server Defaults and you are in Trusted Account mode, you receive the following error: "System.Web.Services.Protocols.SoapException: The permissions granted to user 'Domain\User' are insufficient for performing this operation. --->

Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The

permissions granted to user 'Domain\User' are insufficient for performing this operation."

Reasons:

  • This is caused when the application pool account that runs Central Administration in IIS and the application pool account that the SharePoint object model believes is running the site differ. This happens if the application pool account is changed directly in IIS instead of through SharePoint (using the approved method, which is documented in the Microsoft Knowledge Base at https://support.microsoft.com/kb/934838).
  • This error can be caused when Kerberos authentication has been set up correctly and delegation in a double hop scenario will succeed, but Reporting Services integration has been set up for Trusted Account rather than Windows Authentication. This causes SharePoint to continue to impersonate the logged-on user when contacting Reporting Services; when Reporting Services compares the application pool account to the user account making the request, they are not equal.

Resolutions:

There are two ways to fix the application pool problem:

  • Change the application pool account in IIS back to the account that was used before the problem started occurring.
  • Change the application pool account for Central Administration correctly by following these steps:
    1. Open a command prompt on the Central Administration server.
    2. Type cd %commonprogramfiles%\Microsoft Shared\Web server extensions\12\Bin
    3. Type stsadm -o updatefarmcredentials -userlogin DomainName\UserName -password NewPassword
    4. Reset IIS by typing iisreset
      Note: For more information about changing service accounts in SharePoint, see the following article: https://support.microsoft.com/kb/934838

There are two ways to resolve the issue with Kerberos:

  • Change the SharePoint site(s) so that they are configured for NTLM authentication rather than Negotiate. Follow these steps to change this:
    1. Open Central Administration, and then click  Application Management.
    2. Under Application Security, click Authentication Providers.
    3. Click the zone you want to modify.
    4. Under IIS Authentication Settings, click NTLM instead of Negotiate.
    5. Click Save.
    6. Repeat steps iv-vi for each zone/site you want to change by selecting a different Web application from the drop-down list in the upper right-hand corner.
  • Change the ReportServer virtual directory on the Reporting Services server to support NTLM only instead of Negotiate, NTLM. Use the following sections to change this:

SQL Server 2005 Reporting Services

  1. Get the Web Site ID for the Reporting Services Web site from IIS Manager by clicking the Web Sites folder and looking in the Identifier column next to the site.
  2. Open a command prompt.
  3. Type cd\inetpub\adminscripts
  4. Type cscript adsutil.vbs set w3svc/<WebSiteID>/root/NTAuthenticationProviders "NTLM"
  5. Note: This will cause all double hops to Reporting Services to fail unless the value is set back to Negotiate,NTLM.

SQL Server 2008 Reporting Services

Using the information in How to: Configure Windows Authentication in Reporting Services (https://msdn.microsoft.com/en-us/library/cc281253.aspx) in SQL Server Books Online, set AuthenticationTypes to RSWindowsNTLM only.

Note: This will cause all double hops to Reporting Services to fail unless RSWindowsNegotiate is added.

Issue:

5.6 “An internal error occurred on the report server. See the error log for more details. ---> Operation is not valid due to the current state of the object"

When you click Set Server Defaults, you receive the following error: "An internal error occurred on the report server. See the error log for more details. ---> An internal error occurred on the report server. See the error log for more details. ---> Operation is not valid due to the current state of the

object."

Reasons:

This error can occur if all of the servers in the SharePoint farm are not the same build. To check the builds of the servers in the farm, follow these steps:

  1. Open Central Administration.
  2. Click Operations, and then click Servers In Farm. The build of the servers in the farm will be displayed in the Version column.

Resolutions:

Apply all of the necessary Service Packs and hotfixes to the servers in the farm to bring them up to the correct build of Office SharePoint Server or Windows SharePoint Services.

Issue

5.7 “The operation is not supported on a report server that runs in native mode”

When you click Set Server Defaults you receive 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. --> The operation is not supported on a report server that runs in native mode. "

Reasons:

This error is caused by pointing to a Reporting Services URL that is from a native mode (non-SharePoint integrated) installation.

Resolutions:

Create a Reporting Services database in SharePoint integrated mode using the Reporting Services Configuration tool. For more information about how to do this, see “How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration)” in SQL Server Books Online for the version of SQL Server you are using:

SQL Server 2008 - https://msdn.microsoft.com/en-us/library/bb283151.aspx

SQL Server 2005 - https://msdn.microsoft.com/en-us/library/bb283151(SQL.90).aspx

Issue:

5.8 “The Resource object with classname 'ReportServer' and key 'CentralAdmin' was not found”

When you click Set Server Defaults, Manage Integration Settings, or Grant Database Access, you receive the following error: "The Resource object with classname 'ReportServer' and key 'CentralAdmin' was not found"

Reason:

This error occurs due to missing resource (.resx) files required for the Reporting Services integration. This can be caused by a failed run of the Reporting Services Add-in that has not correctly installed the .resx files into the Central Administration site, the RSCustomAction executable has not been run successfully, or the .resx files are invalid.

Resolution:

  1. Make sure that the add-in installer, or the RSCustomAction.exe if you are running the add-in in files-only mode has run through successfully on one server in the farm.
  2. Verify that the one-time SharePoint jobs that push the changes out to the farm initiated by RSCustomAction have completed successfully. These jobs should have names similar to "Configuration Update" or "web.config change". If they do not, this could be the reason why the .resx files are not in the correct location. For more information about how to check these jobs, see section 10.3.1, “Check/Manage Timer Jobs.” If there is an event log error associated with the jobs, section 10.3.3, “SharePoint Timer Jobs Fail to Run or IIS Manager Is Empty,” contains information that might help you resolve the issue.
  3. If the jobs did fail, try removing them and then trying to run the RSCustomAction.exe again.
  4. If the jobs are not succeeding, check the event logs and SharePoint logs on the SharePoint servers that are failing for further information. For more information, see section 10.3.2, “SharePoint Logs.”.
  5. If all else fails, you can follow these steps to manually copy the resource files.
    1. Open the following folder: x:\Program Files\Common Files\Microsoft Shared\Web Service Extensions\12\Config\Resources
    2. Copy ReportServer.* to C:\Inetpub\wwwroot\wss\VirtualDirectories\####\App_GlobalResources (Where #### is the ID of the SharePoint site)

Issue:

5.9 “Server was unable to process request. ---> Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'”

When you click Set Server Defaults you receive 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. ---> Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: <html>"

Reason:

This error occurs when the ReportServer Web service page returned an error that was not a SOAP response or a regular HTTP error code such as 401. This is usually an ASP .NET error page that displays no useful information if the Reporting Services server is a separate machine because ASP .NET errors are not displayed to remote machines by default.

Resolutions:

The resolution depends on what the underlying error returned by the Reporting Services machine was.

To determine the underlying error, try the following:

  • Look in the resulting HTML in the error message (if remote errors are on or Reporting Services is local to the SharePoint server)
  • Check the Reporting Services log files as the error will be logged there.
  • Try to browse directly to http://<Server>/ReportServer from the Reporting Services machine.
  • Verify that the URL you have specified is a valid URL for a Reporting Services ReportServer instance. You can verify this by browsing to the URL you entered and making sure that the ReportServer Web service interface displays correctly.

6. Issues Deploying Reports

If you are deploying a report to a SharePoint site, there are a few things to check to start with. Here is a list of the steps to follow before you attempt any other troubleshooting:

  1. Verify that the machine deploying the reports from the Microsoft Visual Studio® development system has at least Service Pack 2 for the SQL Server 2005 client tools applied. Service Pack 2 is the first service pack to support SharePoint integration. To verify that your software supports deploying to a SharePoint site, follow these steps:
    1. Open Visual Studio.
    2. Click Help, and then click About.
    3. Locate SQL Server Reporting Services in the list, and then click it.
    4. Verify the version is above 9.00.3042.
    5. If it is not, apply SP2 or later to deploy the reports.
  2. Verify that your deploy locations are set correctly in your report project properties. This is one of the biggest culprits when there are issues deploying reports to SharePoint, because these properties are drastically different between a normal Reporting Services installation and a SharePoint installation. Follow the steps below to verify these properties are set correctly:
    1. In Solution Explorer, right-click your report project, and then click Properties.

    2. TargetDataSourceFolder should be in the format of: http://SharepointServer/Document Library/My Data Source Folder

    3. TargetReportFolder should be in the format of: http://SharepointServer/Document Library/My Reports Folder

    4. TargetServerURL should be in the format of: http://SharepointServer

    5. If any of these values are not in the format listed here, this is most likely the cause of your deployment issues.

    6. For more information about how to deploy reports, see “How to: Publish a Report to a SharePoint Library” in SQL Server Books Online for the version of SQL Server you are using:

      SQL Server 2008 - https://msdn.microsoft.com/en-us/library/bb283155.aspx
      SQL Server 2005 - https://msdn.microsoft.com/en-us/library/bb283155(SQL.90).aspx

  3. If the SharePoint site you are deploying to has an SSL certificate installed on it, verify the following items to rule that out as the culprit:
    1. Is the certificate fully trusted by the machine deploying the reports? To check this, try to browse to the SharePoint site URL you specified in the TargetServerURL property.
    2. If you receive a certificate error stating that there was a problem with the certificate, it is not fully trusted and you will not be able to deploy reports to this site.
    3. You will need to fix the issues with the certificate before you are able to deploy, or you will have to remove the certificate temporarily to deploy the reports.
    4. Make sure you specify the URL to the SharePoint site as the exact site name specified on the SSL certificate.

For more information about how to resolve some of these issues, see section 10.4, “SSL Certificates.”

7. Issues Creating Reporting Items in a SharePoint Document Library

This section contains information about issues you may encounter when you create Reporting Services items (Report Models, Report Data Sources or Report Builder Reports) in a SharePoint library.

7.1 First Steps

  1. Verify that the content types have been added to the document library where you are trying to create the item. For more information, see “How to: Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode)” (https://msdn.microsoft.com/en-us/library/bb326289.aspx) in SQL Server Books Online.
  2. If the icons for report items, Reporting Services content types, or the content types do not show up correctly or at all on the New button after you follow the steps in the SQL Server Books Online topic, this could be caused by forms authentication in SharePoint. Try switching the SharePoint site in IIS Manager to Windows Authentication and see whether the issue goes away. If the issue is resolved after you switch to Windows Authentication, it is related to SharePoint. To prove this, add content types to the document library that are not Reporting Services related and see whether some of those types do not show up. If they do not appear, the issue is unrelated to Reporting Services.

Issue:

7.2 “The path of the item 'http://server:port' is not valid”

When you try to create a Reporting Services item in SharePoint, you receive the following error: "The path of the item 'http://server:port' is not valid."

Reasons:

This can occur if the Reporting Services server is joined to a SharePoint farm that is different from the farm that Central Administration is on or to a stand-alone farm.

Resolution:

Join the Reporting Services machine to the same farm that the Central Administration server is part of. Follow these steps to do this:

  1. Click Start, point to Programs, point to Microsoft Office Server, and then click SharePoint Products and Technologies Configuration Wizard.
  2. Click Next until you get to the Modify Server Farm Settings page.
  3. Click Disconnect from this server farm.
  4. Click Next to remove the server from the incorrect farm.
  5. Run the SharePoint Products and Technologies Configuration Wizard again, this time connecting the server to the existing, correct farm.

For more information, see “Add Servers to the Farm” in “Install Office SharePoint Server 2007 and run the SharePoint Products and Technologies configuration wizard” (https://technet.microsoft.com/en-us/library/cc262079.aspx).

8. Issues Running or Viewing Reports

This section discusses issues you may encounter while running or viewing reports from a SharePoint site.

Issue:

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

Determine Root Cause:

This error can be caused by quite a few different things. This section will help you determine what the underlying problem was. After the root cause has been determined, use the sections following this to resolve the issue.

The usual root cause for this error is loopback checking. This causes credentials to fail to be passed on POSTs back to the server. This will only occur if a host header or DNS name is being used for the SharePoint site. For more information about how to resolve this issue, see section 10.1, “How to Change the DisableLoopbackCheck Registry Key.”

Another usual cause is SSL certificates. If the server is using an SSL certificate, and you are viewing the site over SSL but not using the URL the SSL Certificate was issued for, the URL you are browsing to will show up with a red background with a red X next to it in Internet Explorer.

If you receive this error on a client machine, try viewing the report from the SharePoint server itself to see whether a more descriptive message is displayed:

  1. Check the Reporting Services log files for an error during this time.
    Note: If you see a large number of errors similar to "The permissions granted to user 'DOMAIN\UserName' are insufficient for performing this operation," do not worry; these messages are benign and can be ignored.

  2. If there is nothing in the Reporting Services logs, the problem might not have occurred in Reporting Services. Look in the IIS logs, for the SharePoint site where the error occurred, for a POST to .../_vti_bin/ReportServer. If this request did not fail, the issue probably resides outside of SharePoint. If this request fails with a 401 due to credentials not being passed, use the information in section 8.2, “The request failed with an HTTP status 401: Unauthorized.”. Every operation in regards to Reporting Services made through SharePoint goes through a proxy object on the SharePoint server. The _vti_bin/ReportServer is this proxy object. The SharePoint server makes a POST back to itself using the name of the server in the URL entered into the browser. In Windows Server 2003 SP1 and later, a security feature was added to prevent the passing of credentials in this scenario.

    Here is an example of an IIS log entry that could indicate problems.
    There will be three requests in a row, all without credentials:
    POST /_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 2
    POST /_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1
    POST /_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1

    Here is another example of an IIS log entry that could indicate problems.
    There will be three requests in a row, all without credentials:
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 2
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1

    Here is an example of an IIS log entry that does not indicate problems.
    There will be three requests in a row, all without credentials:
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 2
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 - 10.10.10.10 - 401 1
    POST /MyDocLibrary/_vti_bin/ReportServer/ReportExecution2005.asmx - 80 DOMAIN\JoeUser 10.10.10.10 - 200 0

  3. Check the SharePoint log files and the System and Application event logs for any information as to the cause. For more information, see section 10.3.2, “SharePoint Logs.”

  4. If all else fails, get a network trace on the SharePoint server in question to determine whether a request was made to the Reporting Services machine and how it failed.

Reasons:

The reasons listed in this error message will not be exhaustive as the specific errors you can receive, which are covered later. If this error message is the only one you see, the most likely cause is one of the following conditions: 

  • The underlying error is a 401 (or other authentication error) from the Reporting Services server. This can be caused by a few different things and are discussed in the resolution section
  • The Reporting Services Web site is running on a port that is meant for another SharePoint site. (For example: There is a SharePoint site in the farm meant to run on port 1234. The Reporting Services web site is running on port 1234 on the Reporting Services server.)

Resolutions:

  • To resolve the issue if it is a 401, follow the resolutions in section 8.2.
  • To resolve the issue if it is a port conflict, change the port that the Reporting Services Web site is running on to a port that does not coincide with another SharePoint site.

Issue:

8.2 “The request failed with HTTP status 401: Unauthorized”

When you try to view a report, you receive the following error: "The request failed with HTTP status 401: Unauthorized"

Reasons:

This error can be caused by a number of different conditions. There are two types of issues: intermittent and constant. The following sections discuss each type.

8.2.1 Intermittent

This issue is usually caused by the fact that the SharePoint site is being load balanced. If this issue does not occur when one of the SharePoint servers is being accessed directly rather than through the VIP (virtual IP or virtual name) of the SharePoint site, load balancing is probably the cause.

The reason for this is that the POST back to the server will go to the load balanced VIP and will be made by the SharePoint server. Because the request is made by the server and not the client any affinity, sticky sessions and any other settings the client had set by the load balancer do not affect this request. The request can then be load balanced to another server in the farm, which can result in a double hop.

8.2.2 Constant

  1. This can be caused by accessing the SharePoint site using a host header or DNS name and DisableLoopbackCheck has not been turned on. For more information, see section 10.1, “How to Change the DisableLoopbackCheck Registry Key.”
  2. Reporting Services integration has been configured to use Windows authentication, and Kerberos authentication has not been configured correctly.
  3. Reporting Services integration has been configured to use Windows authentication, Kerberos authentication has been configured correctly, and the Enable Integrated Windows Authentication option in Internet Explorer is not enabled.

Resolutions:

Intermittent -

The resolution for this depends on what technologies have been enabled for your SharePoint site. The following sections will go over the two scenarios.

8.2.3 Load Balancing

  1. To resolve the issue, the POST back to the VIP needs to be prevented from being load balanced a second time and causing the double hop.
  2. To prevent this, open the C:\windows\system32\drivers\etc\HOSTS file.
  3. Add an entry to the file with the IP address assigned to the SharePoint site in IIS and the name of VIP for the SharePoint site.
    Example:   10.10.10.10       my.sharepoint.vip.com
  4. Repeat steps 2 and 3 for each SharePoint server in the Network Load Balancing (NLB) farm.
  5. The IP address is crucial in this instance though, because the Web site can be listening on a single IP and not every IP on the server. Therefore, putting the wrong address can cause any request from that server to the farm to fail.
  6. Verify the IP address of the site by opening IIS Manager clicking the Web Sites folder, and then looking at the right-hand window. The sites on the server and the IP addresses associated with them are displayed. If the site has "(All Unassigned)" set for the IP, it is listening on all IP addresses on the server.

8.2.4 Load Balancing and SSL Offloading

If SSL offloading is in the mix along with load balancing, the previous steps above won't resolve the issue, because all SSL requests have to be routed through the hardware device. If an SSL request is made to the server after making the HOST file changes described earlier, it will fail because IIS is not listening on 443.

There are three ways to resolve this issue. They are listed in the order of difficulty, starting with the easiest:

  • Assign the SSL certificate to the SharePoint site in IIS on each node:

    1. Follow the steps in section 8.2.3, “Load Balancing,” to add the HOST file entries.
    2. If you have access to the full SSL certificate (with private keys) that is being used by the SSL offloader, the easiest way to resolve this is to assign the certificate to the SharePoint site in IIS so that when the POST is made, the site will be listening on SSL and the attempt will succeed.
    3. You can follow the steps at the following link to assign the SSL certificate. If you already have the certificate, follow the last two procedures: https://support.microsoft.com/kb/313299
  • Create a self-signed certificate and then assign it to the site. This does not require access to the certificate on the offloader. This certificate will not be fully trusted by remote clients, but the only machine that will be accessing it will be the SharePoint server itself; that's the only one that matters in this instance.

    1. Follow the steps for section 8.2.3,"Load Balancing," to add the HOST file entries.
    2. Download and install the IIS Resource Kit from here: https://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499\&DisplayLang=en
    3. Open a command prompt, and then navigate to the following folder: C:\Program Files\IIS Resources\SelfSSL
    4. Run the following command to generate and assign the certificate:
      SelfSSL /n:cn=my.sharepoint.site.vip.com /s:<IISSiteID> /t. Replace <IISSiteID> with the ID of the Site from IIS Manager. (Click on the Web Sites Folder in IIS Manager.)
    5. Repeat steps 1-3 on each server in the Network Load Balancing (NLB) farm.
  • Configure Kerberos authentication. Do not follow the steps in 8.2.3, "Load Balancing," to add the HOST file entries. Use the information in section 5.4 under "Resolutions" number 2 step b  to configure Kerberos authentication.

Constant -

If the issue is constant, follow the same steps from “Resolutions” in section 5.2, “The Request Failed with HTTP Status 401: Unauthorized,” to resolve this.

Issue:

8.3 “The permissions granted to user 'Domain\User' are insufficient for performing this operation”

When you click on a Reporting Services item (report, report data source, and so on) and you are in Trusted Account mode, you receive the following error: "System.Web.Services.Protocols.SoapException: The permissions granted to user 'Domain\User' are insufficient for performing this operation. --->

Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The

permissions granted to user 'Domain\User' are insufficient for performing this operation."

Reasons:

  • This is caused when the application pool account that runs the SharePoint Web application you are on in IIS and the application pool account that SharePoint believes is running the site differ. This happens if the application pool account is changed directly in IIS instead of through SharePoint (using the approved method).
  • This error can be caused when Kerberos authentication has been set up correctly and delegation in a double hop scenario will succeed, but Reporting Services integration has been set up for Trusted Account rather than Windows Authentication. This causes SharePoint to continue to impersonate the logged-on user when contacting Reporting Services; when Reporting Services compares the application pool account to the user account making the request, they are not equal.

Resolutions:

Change the application pool account in IIS back to the previous account that was used before the problem started occurring, or change the application pool account for the SharePoint Web application correctly by following these steps:

  1. In Central Administration, click Operations, and then in the Security Configuration section, click Service Accounts.
  2. Click Web Application Pool.
  3. Under Web Service, select Windows SharePoint Services Web Application.
  4. Under Application Pool, select the SharePoint Web application that is experiencing the problem.
  5. Select either Predefined or Configurable, and then select or enter the credentials under which you want the IIS application pool to run.
  6. Click OK.

You have two options to resolve the issue with Kerberos:

  • Change the SharePoint site(s) so that they are set to NTLM rather than Negotiate (Kerberos). Follow these steps to change this:

    1. In Central Administration, click Application Management.
    2. In the Application Security section, click Authentication providers.
    3. Click on the zone you want to modify.
    4. Under IIS Authentication Settings, section select NTLM instead of Negotiate (Kerberos).
    5. Click Save.
    6. Repeat iv-vi for each zone/site you want to change by selecting a different Web application from the drop down in the upper right hand corner.
  • Change the ReportServer virtual directory on the Reporting Services server to support NTLM only, using one of the following procedures. Ensure that the option to use NTLM only (NTLM for IIS 6.0 or RSWindows NTLM for SQL Server 2008) is selected rather than the option to negotiate (Negotiate, NTLM for IIS 6.0 or RSWindowsNegotiate for SQL Server 2008).

    IIS 6.0

    1. Get the Web Site ID for the Reporting Services Web site from IIS Manager by clicking the Web Sites folder and looking in the Identifier column next to the site.
    2. Open a command prompt.
    3. Type cd\inetpub\adminscripts
    4. Type cscript adsutil.vbs set w3svc/<WebSiteID>/root/NTAuthenticationProviders "NTLM"
      Note: This will cause all double hops to Reporting Services to fail unless the value is set back to Negotiate,NTLM.

SQL Server 2008 Reporting Services

Using the information in How to: Configure Windows Authentication for Reporting Services (https://msdn.microsoft.com/en-us/library/cc281253.aspx) in SQL Server Books Online, set AuthenticationTypes to RSWindowsNTLM only.  The article also contains further information about authentication types in SQL Server 2008 Reporting Services.

Note: This will cause all double hops to Reporting Services to fail unless RSWindowsNegotiate is added.

Issue:

8.4 “The item <ItemPath> cannot be found. (rsItemNotFound)”

Reasons:

  • The report was deployed to one location and then viewed from another. The Reporting Services integration only works with a single location for a report, so if there are multiple locations that the report can be viewed from, this error will occur. "Location" means the exact URL that the report you are trying to view was deployed to.

    Example:
    You deploy your reports to http://my.sharepoint.site.com/My Document Library. Your users view the reports from https://my.sharepoint.site.com/My Document Library or http://machinename/My Document Library.

    Example 2:
    SharePoint site A has two host header values My.Site.com and My.Sharepoint.site.com. If a report was deployed using the My.Site.Com address, users receive this error when viewing the report from the My.Sharepoint.site.com address.

  • The public URL for the default zone on the Alternate Access Mappings page has been changed since the report item(s) were uploaded.

  • The Reporting Services Web Service virtual directory is configured for anonymous authentication.
    Note: The Reporting Services virtual directory should only be configured for Windows authentication.

How to determine valid report locations:

To determine the location for the report items in question, follow these steps:

  1. Browse to http://ReportingServicesServer/ReportServer.
  2. The first page should contain a list of the SharePoint sites that have Reporting Services content.
  3. If the URL that you are accessing the failing item from is not in the list, that site is not seen as being a valid location for Reporting Services content.
  4. If you can find the URL in the list, browse through the folder, locate the report item, and then try to view it. If viewing the report also fails, you will need to either change the public URL back to the value it was when you deployed the reports or redeploy the reports to the new location.

Resolutions:

  1. Configure the alternate access mappings to point any URLs/machine names that the SharePoint site can be accessed by to a single public URL. This will forward all requests for that SharePoint site no matter what the user enters for the URL to a single URL, which should virtually eliminate this error.
    1. On the Central Administration home page, click Operations. On the Operations page, in the Global Configuration section, click Alternate Access Mappings.
    2. To filter the list of URLs to a single SharePoint Web application for easier viewing, click the drop-down box in the upper right that says Show All. Click Change Alternate Access. Mapping, and then select the Web application you are working with.
    3. Choose the one URL that you want all client requests to go to. This will become your public URL for the default zone (Example: https://my.site.com).
    4. Click Edit Public URLs.
    5. Under Default, enter the URL.
    6. Click Save.
    7. Make a list of the various names and URLs through which the site can be accessed. Each name/URL will be an internal URL that will forward users to the public URL for the default zone. (Example: http://server1, https://server1, http://server1.site.com, https://server1.site.com https://my.site.com, http://my.site.com)
    8. Click Add Internal URLs.
    9. In the URL protocol, host and port box, enter one of the URLs for the site.
    10. In Zone, ensure that Default is selected.
    11. Click Save.
    12. Repeat steps h through k for each item in the list you made for step g.
      For more information about changing alternate access mapping in SharePoint, see Configure alternative access mapping (https://technet.microsoft.com/en-us/library/cc263208.aspx).
      Note : If you have already uploaded report items to a SharePoint site and you change the public URL for the default zone during these steps, you could run into the #2 reason for this error described above. If you do, follow the steps under the #2 resolution below.
      Note: If browsing to the internal URL does not correctly redirect you to the public URL for the default zone, you may be using an old version of Office SharePoint Server or Windows SharePoint Services 3.0 . For more information, see Description of the Windows SharePoint Services 3.0 hotfix package (Sts.msp): August 26, 2008 (https://support.microsoft.com/kb/956057).
  2. There are two ways to resolve this problem if the public URL has been changed.
    • Change the public URL for the default zone back to the value it had when you deployed the reports.
    • Redeploy all of the existing reports and shared data sources. You may have to delete the reports first if redeploying/overwriting them does not work.
  3. To change the SQL Server 2005 Reporting Services Web service virtual directory to use Windows authentication, follow these steps:
    1. On the Reporting Services machine, open IIS Manager.
    2. Locate the Web site that contains the ReportServer virtual directory.
    3. Right-click the virtual directory, and then click Properties.
    4. Click the Directory Security tab.
    5. Under Authentication and Access Control, click Edit.
    6. Make sure that only the Integrated Windows Authentication check box is checked.
  4. To verify the authentication method used for SQL Server 2008 Reporting Services follow these steps:
    1. Open C:\Program Files\Microsoft SQL Server\MReporting Services10.<INSTANCENAME>\Reporting Services\ReportServer\web.config.
    2. Verify that the following tag is set as follows:
      <authentication mode="Windows" />
    3. Follow the same steps for C:\Program Files\Microsoft SQL Server\MReporting Services10.<INSTANCENAME>\Reporting Services\ReportManager\web.config.

Issue:

8.5 “System.ArgumentException: Value does not fall within the expected range”

Reason:

This issue can occur if a report is viewed from within SharePoint or from the Report Viewer Web Part. This happens when a SharePoint site has had its public URL changed and you are accessing the site through the old URL or still have the old URL specified in for the report location in the Report Viewer Web Part properties.

Resolutions:

There are two ways to fix this. The method depends on where the error is occurring:

  • Viewing the report from within a SharePoint document library: View the report from the current public URL for the default zone specified in the Alternate Access Mappings page, or browse to the site using one of the internal URLs specified in the Alternate Access Mappings page, because this will forward you to the correct public URL.
  • Viewing the report using the Report Viewer Web Part:
    1. Change the URL of the report you are pointing the Web Part to the correct location of the report using the current public URL for the SharePoint site. The Report Viewer Web Part ignores the values in the Alternate Access Mappings page and will therefore not forward itself to the public URL like a browser client.
    2. Click Site Actions, and then click Edit Page for the SharePoint page that contains the Report Viewer Web Part.
    3. Click Edit, and then click Modify Shared Web Part in the upper left of the toolbar above the Report Viewer control.
    4. On the right-hand side, change the URL under Report to the correct report location using the current public URL for the default zone, or click the "..." button and browse to the report you want the control to display.

9. Issues Browsing to ReportServer

This section discusses issues you may encounter browsing the ReportServer virtual directory directly.

Issue:

9.1 "Report Server has encountered a SharePoint error. (rsSharePointError) Object reference not set to an instance of an object"

Reasons:

There are multiple underlying causes to this error, and the one you encounter could be different from the ones listed here.

Note: It is very possible that this error will not affect the integration of Reporting Services and SharePoint. If you are unable to resolve the issue, verify that it actually causes a problem when users try to run or deploy reports to SharePoint.

  • This can be caused when there is a SharePoint content database to which the Reporting Services instance does not have access.
  • This can also be caused by an orphaned SharePoint site collection. This is a site collection that, when clicked on, has all of its properties dimmed or empty.

Determine Root Cause:

If you want to verify the underlying error that is occurring, you will need to get a memory dump on all AV/NullReferenceExceptions on the Reporting Services Web process and then analyze it. To do this, follow these steps:

  1. Use the Debug Diagnostic tool, DebugDiag, which is the easiest way to obtain a memory dump. The tool is available for 32-bit systems only:
    1. Download and install DebugDiag from the following link: https://www.microsoft.com/downloads/details.aspx?FamilyID=28bd5941-c458-46f1-b24d-f60151d875a3\&displaylang=en
    2. Start DebugDiag and leave Crash selected.
    3. Click Next, and then select All IIS/COM+ Related Processes.
    4. In Action type for unconfigured first chance, click Log Stack Trace.
    5. Click Exceptions.
    6. Click Add Exception.
    7. Select the first item in the list, CLR (.NET) Exception.
    8. In .NET Exception Type, enter System.NullReferenceException. (Case matters!)
    9. Change Action Type to Full User Dump.
    10. Change Action Limit to 10.
    11. Click OK.
    12. Click Save and Close.
    13. Click Next, and then configure the location for the memory dumps to be written to.
    14. Click Next, and then click Finish.
    15. Reproduce the exception by browsing to http://<Server>/ReportServer.
    16. The dump(s) will be written to the location you specified.
  2. Use the Debugging Tools for Windows (WinDBG). These tools are available for both 32-bit and 64-bit systems:
    1. Download and Install the newest Debugging Tools for Windows from https://www.microsoft.com/whdc/resources/downloads.mspx.
    2. Open WinDBG, and on the File menu, click Attach to a Process.
    3. Select the w3wp.exe process that is the IIS application pool running SQL Server 2005 Reporting Services, or the ReportingServicesWindowsService.exe process if this is SQL Server 2008 Reporting Services.
    4. Type g to get the process running and wait for the AV to occur.
    5. If the error is not an AV (0xc0000005), type g to continue execution until it occurs.
    6. After WinDBG breaks on an AV (0xc0000005) error, type the following, which will cause the dump file to be written out: .dump /ma c:\dumps\mydump.dmp
  3. Locate the underlying exception that caused the NullReferenceException.
    1. Open the dump file that you created using WinDBG.
    2. Type .symfix
    3. –For 64-bit systems, type .load c:\windows\microsoft.net\framework64\v2.0.50727\sos.dll
    4. –For 32-bit systems, type .load c:\windows\microsoft.net\framework\v2.0.50727\sos.dll
    5. Type !analyze -v to get a basic analysis of the exception and to get set to the thread where the error occurred.
    6. Type !dso
    7. Search this output for "Exception" to locate the exception objects that were on the stack on this thread. Look for Exception objects other than NullReferenceException.
    8. Type !pe <ExceptionAddress> to print out the information on this exception. Repeat this for all of the various exception objects (if any) in the list.
    9. Use this output to help determine what the underlying cause of the exception was.
    10. If this does not yield any more information, look at all of the exception objects in the dump to see whether the error occurred on another thread.
    11. Type !dumpheap -stat -type Exception
    12. Type !dumpheap -mt <MethodTableAddress> to get the list of the addresses for that exception type. You can ignore the ExecutionEngine, StackOverflow, and OutOfMemory exception objects as they are always there.
    13. Type !pe <ExceptionAddress> to print out the information on that exception.

Resolutions:

  • If the error is a database access issue, follow these steps to resolve it:
    1. On the Central Administration home page, click Application Management, click Reporting Services, and then click Grant Database Access.
    2. Enter the Reporting Services machine and instance name, and then click OK.
    3. Enter the credentials to connect to the Reporting Services machine, and then click OK.
    4. This should grant the Reporting Services service accounts the required access to the SharePoint content databases.
    5. Try to browse to http://<Server>/ReportServer.
  • If the error is an orphaned site collection, you can try the following.
    Caution: This solution is unverified, so you should back up the SharePoint content databases in question before you run the final step to repair the issue.
    1. Open a command prompt on the SharePoint server.
    2. Type cd %commonprogramfiles%\Microsoft Shared\web server extensions\12\BIN
    3. Type stsadm -o databaserepair -url http://<SharePointSiteURL> -databasename <Name of the SharePoint Content Database for the site>
    4. If you run the previous command and receive output regarding orphaned sites, run the following command to try and repair it: stsadm -o databaserepair -url http://<SharePointSiteURL> -databasename <Name of the SharePoint Content Database for the site> -deletecorruption
    5. Repeat steps 3 and 4 for every SharePoint site.

10. Appendix

The appendix contains general procedures and information about items not directly related to Reporting Services that are referenced throughout the article.

10.1 How to Change the DisableLoopbackCheck Registry Key

  1. Open the Registry Editor (regedit).
    Caution: Incorrectly editing the registry can severely damage your system. Before making changes to the registry, you should back up any valued data on the computer.
  2. Open the following key: HKLM\System\CurrentControlSet\Control\Lsa.
  3. Create a new DWORD value called DisableLoopbackCheck.
  4. Set the value to 1.
  5. Reboot the server.
    Note: This is required for the change to take effect.
  6. If this resolves the issue, there is a more secure way of making this change. Instead of making the server-wide change that DisableLoopbackCheck implements, you can disable checking for specific host names. For more information about how to do this, see the following article in the Microsoft Knowledge Base: https://support.microsoft.com/kb/896861

10.2 Kerberos

10.2.1 Kerberos Resources Online

“How to use SPNs when you configure Web applications that are hosted on IIS 6.0”:
https://support.microsoft.com/kb/929650

“Kerberos authentication and troubleshooting delegation issues”:
https://support.microsoft.com/kb/907272

“How to configure an ASP.NET application for a delegation scenario”:
https://support.microsoft.com/kb/810572

“Answers to frequently asked Kerberos questions”:
https://support.microsoft.com/kb/266080

10.2.2 How to Use the DelegConfig Tool to Troubleshoot Kerberos Issues with SharePoint and Reporting Services Integration

  1. Download the Delegation/Kerberos Configuration tool (DelegConfig v1) .zip file from here: https://www.iis.net/downloads/default.aspx?tabid=34\&g=6\&i=1434
  2. Install DelegConfig on the Central Administration SharePoint server (or the Web front end where you are having trouble).
  3. Extract the .zip file anywhere on a machine that has IIS 6.0 and .NET 2.0 installed.
  4. Open IIS Manager.
  5. Create a new virtual directory, and point it to the Kerberos folder that was extracted from the zip file.
  6. Make sure Run Scripts and Execute are selected on the permissions window.
  7. Right-click the virtual directory you created, and then click Properties.
  8. Click the Directory Security tab.
  9. Under Authentication and Access Control, click Edit.
  10. Make sure that only the Integrated Windows Authentication check box is checked.
  11. Change the virtual directory to run under the same application pool that the failing SharePoint site is.
  12. Open a new Internet Explorer window, and then browse to the virtual directory you created for DelegConfig. If you do not get the DelegConfig page, try adding /Default.aspx to the URL.
  13. If you were connecting to the failing site using a DNS name or host header, you will need to change the resolvedName parameter in the URL to match the name of the site. Example: https://localhost/DelegConfig/Default.aspx?resolvedName=www.adventure-works.com
  14. The information displayed will verify the service principal names (SPNs) that have been created, if the application pool account can delegate credentials and if there are any duplicate SPNs.
  15. Test the Reporting Services machine as well.
  16. On the DelegConfig page, click Add Backend.
  17. Put the server name of the Reporting Services machine as it is entered into the Manage Integration Settings section in Central Administration.
  18. In Service Type, click Web Server.
  19. Because Reporting Services has to run under a domain account, in Service Account, click Configured. Enter the account the Reporting Services application pool runs under in "Domain\UserName" format.
  20. Click Submit.
  21. The previous page of results will now have a Backend Server section that displays the SPN and delegation status of the Reporting Services server.

You can use these results to determine what further steps are required to configure Kerberos successfully.

10.3 SharePoint-Specific

This appendix section contains information about SharePoint-specific information referenced throughout the paper.

10.3.1 Check/Manage Timer Jobs

  1. On the Central Administration home page, click Operations. On the Operations page, click Timer Job Definitions.
  2. Look for jobs with a Schedule Type value of One Time.
    Note: You can sort by clicking the column header.
  3. These jobs should not show up in this list if they have successfully completed on all servers in the farm. If they are in this list, there was either a failure in the job or it is still running.
  4. On the Timer Job Definitions page, you can also check the status of the job and the servers on which it has succeeded or failed.
  5. This page does not have a Schedule Type column like the Timer Job Definitions page. The easiest way to single out the jobs is to look for any items that do not have Succeeded in the Status column.
  6. To see why the job failed, look at the SharePoint logs.

10.3.2 SharePoint Logs

  1. To view the logs generated by SharePoint, navigate to  the following location: Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS.
  2. Review the Application event logs for additional errors and warnings in regards to SharePoint and its services.
  3. For more information about information in these logs that is specific to Reporting Services, see “Events for a Report Server in SharePoint Integrated Mode” in SQL Server Books Online (https://msdn.microsoft.com/en-us/library/cc627510.aspx).

10.3.3 SharePoint Timer Jobs Fail to Run or IIS Manager Is Empty

  1. When you open IIS Manager, the window is empty, and no tree view  to select Web sites or otherwise.
  2. In the Application event log on the same server, you will see errors similar to the following: "The Execute method of job definition <job> threw an exception. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
  3. This issue can be resolved by applying the following hotfix: https://support.microsoft.com/kb/946517

10.4 SSL Certificates

This appendix section contains information specific to SSL certificates.

10.4.1 "The security certificate presented by this website was not issued by a trusted certificate authority"

If you receive this error: Follow these steps to resolve the issue:

  1. Browse to the failing site in Internet Explorer.
  2. If you are using Internet Explorer 7, click Continue to this website (not recommended).
  3. Click Certificate Error next to the Refresh button.
  4. Click View Certificates link at the bottom of the pop-up message.
  5. Click the Certification Path tab.
  6. Click on the top item in the list that has a red X on it. (This should be your certification authority (CA).)
  7. Click View Certificate.
  8. Verify that the date in Valid From is correct. If it is not, this procedure will not resolve the issue. You must update the server certificate.
  9. If the name on this certificate or the Details tab contains a name that is a server on your internal network, follow the next set of steps. If it does not, you will need to contact the issuing authority for a valid CA certificate.
  10. On the server that issued the certificate, open Internet Explorer.
    Important: If the server is not running Windows, you will need to export this certificate following that operating system’s steps.
  11. On the Tools menu, click Internet Options, and then on the Content tab, click Certificates.
  12. Click the Trusted Root Certification Authorities tab.
  13. Click the certificate in the list for the Certification Authority that issued your certificate.
  14. Click Export.
  15. Click Next twice.
  16. Specify the location and the file name (make sure the file ends in .cer).
    Note: You do not need to export the private keys, even though the user interface may indicate otherwise.
  17. Click Finish to save the file.
  18. Copy the .cer file to the development machine.
  19. Import the exported certificate on the Reporting Services machine by double-clicking the exported .cer file and clicking Next or OK until it successfully imports the certificate.

11. Conclusion

This paper covered the various areas of the Reporting Services integration with SharePoint and provided guidance on how to resolve the various issues that can arise when installing and configuring this integration.

For more information:

http://www.sqlcat.com/: SQL Customer Advisory Team

https://blogs.msdn.com/psssql/: SQL Server CSS Blog

https://blogs.msdn.com/sqlrsteamblog/: SQL Reporting Services Development Team Blog

https://blogs.msdn.com/calton/: My Personal Reporting Services Blog

https://www.microsoft.com/sqlserver/: SQL Server Web site

https://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

https://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter 

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

  • Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
  • Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

If you have any comments or suggestions, feel free to contact the author. You can reach Chris Alton at calton@microsoft.com.