Share via

Use Access Services with SQL Reporting Services: Installing SQL Server 2008 R2 Reporting Services Add-In (SharePoint Server 2010)

  • Article

 

Applies to: SharePoint Server 2010 Enterprise

A prerequisite for running Access Services for SharePoint Server 2010 is the installation of Microsoft SQL Server 2008 R2 Reporting Services Add-in for SharePoint Technologies 2010 (SSRS). The SSRS add-in is available as a free download via the Web and provides a set of features that enable you to run Access Services reports in SharePoint.

Important

While the SSRS Add-in can be installed either before or after SharePoint Server is deployed, it is strongly recommended that it be installed prior to installing SharePoint Server to avoid time consuming manual configuration on a server by server basis.

  • If you install SSRS before SharePoint Server:A deployment of Reporting Services integrates with a deployment of SharePoint at the farm level. No additional configuration or replication out to servers in the farm will be necessary. If you use the SharePoint prerequisite tool during SharePoint installation, the SSRS add-in will be automatically installed during that process.

  • If you install SSRS after installing SharePoint Server:If you choose to install SSRS after deploying SharePoint Server, the installation process will involved many more steps, especially if you have multiple web front end servers. If you have multiple SharePoint Web applications in a farm configuration, you must install the Reporting Services Add-in on each computer that has a front-end Web.

Connected vs. Local Mode:

There are two modes in running Reporting Services with SharePoint Server. There is the standard default mode referred to as the “Connected mode” and requires SharePoint Server, the SSRS add-in and the SQL Server 2008 R2 Report Server. The new mode is “Local mode”. It is a light-weight setup for Reporting Services to integrate with SharePoint Server. It only requires SharePoint Server and the SSRS Add-in.

Important

Local mode means there is no Reporting Services server. You must install the add-in but it is not an actual Reporting Services server. With Local mode, users can view reports but will not have access to server side features, e.g., setting up ‘subscriptions’.

  • If you have multiple Web front-ends in your SharePoint farm, it is recommended that you set up using Connected Mode. To set up connected mode, you will need SQL Server 2008 R2 Report Server and the Reporting Services Add-in.

  • If you are running a SharePoint farm with only one Web front-end, you can set up Local Mode. You need Reporting Services Add-in.

Note

Installing the Reporting Services Add-in is one of many steps that are necessary for integrating a report server with an instance of a SharePoint product or technology. The order of steps for configuring the servers is important. For best results, be sure that you do them in order.

Installing Reporting Services Add-in before SharePoint installation (Recommended)

  1. Download the SSRS Add-in from https://go.microsoft.com/fwlink/p/?LinkID=164654&clcid=0x409.

  2. Run rsSharePoint.msi on the farm server on which you intend to start SharePoint installation.

  3. Install SharePoint Server.

  4. Enable the Session State for the farm by opening the SharePoint Management Shell. Enter the command [Enable-SPSessionStateService -DatabaseName "foo"] — The DatabaseName value is the database that will be created on the computer that is running SQL Server. Perform an IIS reset.

Installing the Reporting Services Add-in after SharePoint installation

  1. Install the SSRS Add-in according to the steps at https://go.microsoft.com/fwlink/p/?LinkID=164654&clcid=0x409

  2. Activate the SSRS feature according to the steps at https://msdn.microsoft.com/en-us/library/bb677366(SQL.105).aspx

To install the Reporting Services Add-in for Connected Mode

  1. Install the SSRS Add-in either before or after SharePoint Server installation according to the session above.

  2. Configure Report Server Integration in SharePoint Central Administrationhttps://msdn.microsoft.com/en-us/library/bb326213(SQL.105).aspx

  3. Copy the attached Script to the server where the SQL Reporting Services is running.

  4. Rename the file as Access.PS1 by removing the .txt extension.

  5. Launch the SharePoint 2010 Management Shell (Run As Administrator) or SharePoint 2013 Management Shell (Run As Administrator) and follow the steps

    1. Type cd \

    2. Type cd Temp

    3. Type “.\Access.PS1”

  6. Change the rssrvpolicy file on RS server.

    1. Add the following XML code in the file under the <NamedPermissionSets> node.

      <PermissionSet class="NamedPermissionSet" version="1" Name="ReportExpressionsDefaultPermissionSet">
<IPermission class="SecurityPermission" version="1" Flags="Execution" />
<IPermission class="Microsoft.Office.Access.Server.Security.AccessServicesPermission, Microsoft.Office.Access.Server.Security, 
     Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" version="1.0" Flags="CalculationCallback" />
</PermissionSet>

    2. In the <CodeGroup>node, find the following line and change PermissionSetName from “Execution” to “ReportExpressionsDefaultPermissionSet”

      <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="Execution" Name="Report_Expressions_Default_Permissions"
 Description="This code group grants default permissions for code in report expressions and Code element. ">

  7. Enable Remote Errors for Reporting Services by following the instructions at https://go.microsoft.com/fwlink/p/?LinkId=183457&clcid=0x409.

Important

Update the rssrvpolicy.config on all servers that are running SQL Reporting Services. Go to Central Administration | Manage Services on Server. It is not required to stop/start SQL Reporting Services.

See Also

Other Resources

Resource Center: What's New in Microsoft SharePoint Server 2010
Access Services Report in SharePoint Site shows error for data extension ‘ADS’