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.
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
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.
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.
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
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
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.
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
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
by Reporting Services in SharePoint Integrated Mode
Running Reporting Services in SharePoint Integrated Mode
Reporting Services for SharePoint 2010 Integration
Introduction to Microsoft Sharepoint Portal Technology
See All Articles by Columnist