Integrating SSRS 2008 into a SharePoint Server 2010 Farm


SharePoint and SQL Server Reporting Services can be
integrated together to provide a one-stop shop for users to explore all content
for a site. Deploying reports within your SharePoint site can simplify
deployment and maintenance of the solution while providing for a consistent
look and feel for the user. This article walks the Database Administrator
through the integration process.

Integration modes

SharePoint integration for Reporting Services has two
modes: local and connected. Local mode can be used when all you need to do
is serve up reports from within SharePoint and do not need or have a separate
Report Server. If you have a separate Report Server or need the additional
functionality garnered by using one, connected mode will work in this
situation. Connected mode allows your Reporting Server to handle scheduling,
snapshots, and report processing. This article covers configuration for
connected mode.

Integration Requirements

To install Reporting Services in connected mode with
SharePoint, SharePoint must be installed as a SharePoint farm. Additionally,
the same edition of SharePoint must be installed on the Reporting Server. This
is because it will need to be joined to the SharePoint farm. Also, note that
since SharePoint comes in only a 64-bit install, Reporting Services will also
need to be installed on a 64-bit OS.

On the database side, both Reporting Services and SharePoint
require a SQL Server database. However, Reporting Services requires a full SQL
Server edition whereas SharePoint needs only the SQL Server Embedded Edition.
SharePoint and Reporting Services can share the same database, but it must be a
full SQL Server edition. The workgroup and express editions will not be
sufficient. Choose between Enterprise, Standard, and Developer editions.

The Reporting Services Add-in for SharePoint Products will
need to be added. If you are using SQL Server 2008 R2 for Reporting Services,
the database engine for SharePoint will need to be SQL Server 2008 R2 as well.

Installation

An existing installation of a Reporting Services database
not set for connected mode cannot be configured for SharePoint integration.
Instead, the option to run Reporting Services in SharePoint connected mode must
be chosen during the Reporting Services install. Or alternatively, for an
existing Report Server, you can use the Reporting Services Configuration tool
to add an integrated reporting services database in integrated mode.

It’s important to note that all reporting services
databases associated with a Report Server or scale-out must be all integrated
or all not integrated. They cannot be mixed and they can’t be reconfigured.
This is because SharePoint becomes the repository for the reports, data
sources, and models when in integrated model. The Reporting Services database
is then used to store the schedules, snapshots, history, and subscriptions.

Once Reporting Services is installed, as I mentioned above,
the same SharePoint product that is running in the SharePoint farm must now be
installed on the Reporting Server. This installs the Web Front End (WFE) for
the Reporting Server.

To install the Web Front End, begin the SharePoint product
install and choose Server Farm for the Installation Type. Choose Complete for
the Server Type. You will need to be a SharePoint Farm Administrator to add
the SharePoint products. Once the installation is complete, configuration will
need to be performed on both servers. Note that a single server install
doesn’t require the WFE.

Configuration

The Web Front End now can be joined to the SharePoint farm.
You will need the SharePoint farm Passphrase to complete this step.

You will utilize the Microsoft SharePoint 2010 Products
Configuration Wizard to add the WFE. Select Connect to an existing server
farm. You will be asked for the configuration database’s particulars and then
for the passphrase.

For the SharePoint product, if you haven’t already installed
the Reporting Services add-in, you will need to do that. Once installed, you
can use SharePoint Central Administration to configure it. In General
Application Settings under Reporting Services, choose Reporting Services
Integration. This screen allows you to specify the URL of the Report Server
Web Service to be integrated, the authentication mode, and whether it should be
activated for all or select site collections.

The authentication modes available here are Trusted Account
and Windows Authentication. With Trusted Account, the Web Front End passes the
logged in user’s credentials through to the Report Server for further
authentication. Windows Authentication is used for Kerberos enabled
environments or when the Report Server and SharePoint Server are on the same
box.

Reporting Services always requires a valid security context,
so there can be a problem here if you have a site that allows for anonymous
access and you would like to provide Reporting Services content to the users of
that site. Out-of-the-box, anonymous access to Reporting Services is denied.
However, each anonymous request could be wrapped by a valid security context to
allow Reporting Services to authenticate that account.

On the Report Server, you will need to make sure to set up
an Execution Account. This account is used by Reporting Services to connect to
external resources and for data sources that don’t require credentials. It can
be set up using the Report Server Configuration Manager. Make sure to use an
account with the bare minimum permissions needed and to be careful not to use a
user account or the Reporting Services service account.

Deploying Reports to SharePoint

Now that everything is configured, it’s time to get some
reports up on the SharePoint site. Let’s assume you already have a SQL Server
Business Intelligence Development Studio (BIDS) project containing several
reports, data sources, and report parts you wish to deploy to your SharePoint
site.

To deploy to the SharePoint site, you will need to right
click on your solution in BIDS and choose Properties. In the properties window
you can specify the relative URL for data sets, data sources, reports, and report
parts in addition to the site URL and option for overwriting data sets and data
sources. A set of deployment options might look like the following.

OverwriteDataSets                     No
OverwriteDataSources                  No
TargetDataSetFolder                   http://MySharePoint/sites/Reports/Documents
TargetDataSourceFolder                http://MySharePoint/sites/Reports/Data%20Connections
TargetReportFolder                    http://MySharePoint/sites/Reports/Documents
TargetReportPartFolder                http://MySharePoint/sites/Reports/Documents
TargetServerURL                       http://MySharePoint/sites/Reports/
TargetServerVersion                   SQL Server 2008 R2

Once that is all set up, right click your solution and
select Deploy. Your Reporting Services content is now up on your SharePoint
site.

Conclusion

Integrating SQL Server Reporting Services with SharePoint
allows the products to share content databases and security models. Reports
can then be displayed and managed in your SharePoint site right alongside other
SharePoint content on your site.

Configuring the integration between the two is not obvious.
With a little guidance and some up front planning, however, the integration can
be setup rather quickly.

For More Information

Features Supported
by Reporting Services in SharePoint Integrated Mode

Requirements for
Running Reporting Services in SharePoint Integrated Mode

Configuring
Reporting Services for SharePoint 2010 Integration

Related Articles

Introduction to Microsoft Sharepoint Portal Technology
Internet.com: Sharepoint

»


See All Articles by Columnist

Deanna Dicken

Deanna Dicken
Deanna Dicken
Deanna Dicken is an architect with approximately 20 years of IT experience. In that time she has worked on many large-scale, mission-critical applications and has been involved at all phases of the lifecycle. She has also contributed to three SQL Server MCSE books, co-authored Learn SQL in a Weekend, and tech edited many other titles. She lives on the outskirts of Indianapolis, Indiana with her husband, Curtis, and children, Kylee and Zach.

Latest Articles