ptg
531
Reporting Services Add-In for SharePoint
31
Report Server DB. Report Server ensures that the copy of the reports in Report Server
DB is kept in sync with the master copy in the SharePoint Content DB via a catalog-
synchronization feature. Any metadata associated with the reports such as schedules,
subscriptions, and snapshots for report history or report execution is stored only in
the Report Server DB.
Figure 31.1 shows catalog synchronization as a feature in Report Server in SharePoint inte-
grated mode. This is a background process that is triggered automatically whenever a
report item is created, updated, or retrieved. It ensures that the copies kept in Report
Server DB are in sync with the SharePoint Content DB.
When report items are deleted from the SharePoint site, the Report Server performs peri-
odic verification and removes any copies from the Report Server database along with any
associated report snapshots, subscriptions, and other metadata for the report. At daily
intervals, the Report Server runs a cleanup process to verify that items stored in the Report
Server database are associated with a report in the SharePoint Content database. The
frequency of the cleanup process is controlled by the
DailyCleanupMinuteofDay
property
in the
RSReportServer.config
file.
Security Management
For authentication, both the Windows integrated and trusted account modes are
supported between SharePoint Server and Report Server. Figure 31.2 shows how the
authentication information flows between the SharePoint and Report Server.
In SharePoint integrated mode, SSRS uses a security extension to maintain report security
in MOSS or WSS. SharePoint security features can be used to access report items from
SharePoint sites and libraries. Once you integrate Report Server and SharePoint, the exist-

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
532
CHAPTER 31 SSRS 2008 SharePoint Integration Architecture
reports, data sources, and report models on the SharePoint site. A list of SharePoint
permissions and how they map to Report Server operations is provided in Chapter 33.
Deployment Architecture
Prerequisites for SSRS to integrate with SharePoint include the following:
. Install SSRS 2008 in SharePoint integrated mode, which is available in the following
editions: Developer, Evaluation, Standard, and Enterprise.
. Install the same type and version of SharePoint WFE on the Report Server machine
as is on the SharePoint Server that will be used for integration. Integration is
supported for WSS 3.0 and MOSS 2007 Standard or Enterprise editions. If you inte-
grate with WSS, install the WSS WFE on the Report Server machine; for MOSS,
install the MOSS WFE.
. Install the RS add-in on each SharePoint WFE that will be used to view and man-
age reports.
To plan your system architecture, here are the variations of deployment topologies to
consider:
. Single machine: Figure 31.3 shows all SSRS and SharePoint components working
together on the same machine. Putting everything on a single computer may not be
practical for an enterprise production deployment, but it is attractive in a develop-
ment or testing environment to save costs (for example, hardware and software
licensing costs).
. Distributed servers: It is common to separate the application server and database
server on separate machines even for a single instance of SSRS or SharePoint Server.
RS
Add-in
Clients
Clients

instances of the same server component can be deployed, such as multiple Report
Servers or multiple SharePoint sites (also called a SharePoint farm). Figure 31.4 shows
a series of computers being used for SSRS scale out and a series of computers being
used for a SharePoint farm. NLB in Figure 31.4 stands for network load balancer. The
entire SharePoint farm must be configured to use a virtual Report Server URL as a
single point of entry. Individual SharePoint sites in a farm cannot be configured
against different Report Servers. SSRS does not provide load-balancing features or the
ability to configure a virtual server URL out of the box. Therefore, a hardware or
software load-balancing solution must be used.
Summary
SSRS SharePoint integration is enabled via deep database and security integration between
Report Server and SharePoint via the Report Server SharePoint integrated mode. An RS add-
in is required to be installed on the SharePoint web application to view and manage
reports and to interact with SSRS. All user actions are initiated via the SharePoint UI, which
uses a proxy to communicate with Report Server and complete any actions on report items.
A variety of deployment topologies can be picked for integration between SharePoint and
SSRS, such as single machine, distributed servers, and scalable deployments.
Clients
Clients
Clients
NLB
Flat Files,
OLE DB,
ODBC
SQL, AS,
DB2, Oracle,
Teradata, etc.
NLB
RS Server
+ SharePoint WFE

CHAPTER
32
Installation of Reporting
Services Integrated with
SharePoint
IN THIS CHAPTER
. Installing Reporting Services
. Installing SharePoint
. Configuring Report Server in
SharePoint Integrated Mode
. Installing Reporting Services
Add-In for SharePoint
. Configuring Report Server
Integration Via SharePoint
Central Administration
. Upgrading from SSRS2K5 SP2
. Scaling-Out Deployments
. Troubleshooting
T
he preceding chapter covered deployment architectures,
which can help you to decide whether to integrate
SharePoint with Reporting Services on a single machine,
distributed servers, or scalable farms.
Traditionally, you can launch Microsoft software installa-
tion by clicking
setup.exe
without much planning and
troubleshoot if something goes wrong. Customers have
found that installation and configuration of the integration
between SharePoint and Reporting Services can be hard to

connections needed for access.
An alternative is to pick the Install, but Do Not Configure the Report Server option. This is
called a Files Only mode of installation. This will require post-installation configuration
steps that provide more opportunities to pick URLs, port numbers, and names for web
services and databases.
Installing SharePoint
You can do a fresh install of Windows SharePoint Services 3.0 (WSS) or Microsoft Office
SharePoint Server 2007 (MOSS) or use existing SharePoint deployments to integrate with
Reporting Services. Refer to tutorials or books on WSS and MOSS for information about
topics such as administration of SharePoint farms. For many readers, you are likely to have
existing installations of WSS or MOSS, and your SharePoint administrator can help you
with the integration tasks.
If you are installing a new SharePoint Server, you can reduce the number of database
engines to manage by reusing the SQL Server 2008 database you just installed with SSRS
2008 as your storage location for SharePoint.
NOTE
If your deployment topology includes installing the Report Server and SharePoint Server
on separate machines, remember to install a SharePoint Web Front End (WFE) on the
Report Server computer, too. The WFE type and version should be the same as on the
SharePoint Server (WSS or MOSS) that you are integrating with SSRS. Follow steps 1
through 3 described in the instructions to set up WSS 3.0.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
537
Configuring Report Server in SharePoint Integrated Mode
Here are the basic steps to set up WSS 3.0 to use for reporting integration:
1. WSS 3.0 is available as a free download as a setup file called
SharePoint.exe
.

Configuring Report Server in SharePoint Integrated
Mode
You can use the Report Server Configuration tool to create a Report Server database in
SharePoint integrated mode and configure the Report Server Service.
Chapter 34, “Tools Support for SSRS Integrated with SharePoint,” is about using tools with
SharePoint mode, and Figure 34.3 shows the Report Server Database Configuration
Wizard, which you can use to create the Report Server database in SharePoint mode.
Note that you have to configure the Report Server Service to run under a domain account
if Report Server and application databases are on one computer and the SharePoint web
application is on another computer. Chapter 33, “SharePoint Mode Administration,”
provides more information about security.
32
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
538
CHAPTER 32 Installation of Reporting Services Integrated with SharePoint
FIGURE 32.1
SharePoint Central Administration: Reporting Services management.
Installing the RS Add-In for SharePoint
Go to www.microsoft.com/downloads and search for “Reporting Services add-in for
SharePoint.”
NOTE
There are multiple versions of the SSRS add-in. You need to download the 2008
Reporting Services add-in for SharePoint for the language of your choice. Version
10.00.2531.00 released on April 7, 2009 is the most current update and includes the
Report Builder 2.0 Click Once update (www.microsoft.com/downloads/
details.aspx?displaylang=en&FamilyID=58edd0e4-255b-4361-bd1e-e530d5aab78f).
Run the
rsSharePoint.msi

Once the Reporting Services section shows up under Application Management, you can
use the various links under it to configure SharePoint to talk to Report Server.
First, click Manage Integration Settings (see Figure 32.4). In the first field, you can specify
the Report Server web service URL, which represents the target Report Server in SharePoint
mode. This is the same value as the web service URL from the Reporting Services
Configuration tool. The second field is a drop-down choice for authentication mode
(between Windows authentication or trusted authentication), which can be selected based
on what type of authentication mode is used for the SharePoint web application.
CHAPTER 32 Installation of Reporting Services Integrated with SharePoint
Now, click Grant Database Access (see Figure 32.5) to allow the Report Server Service to
access the SharePoint Configuration and Content databases. Specify the Report Server
name and database instance name. When you click OK, a pop-up dialog will request
credentials for connecting to the Report Server.
The last link under Reporting Services Application Management is Set Server Defaults (see
Figure 32.6).
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
541
Configuring Report Server Integration Via SharePoint Central Administration
32
The Set Server Defaults option enables you to specify the default for the following
Reporting Services features:
. Report History Default: The ability to limit the default number of snapshots that
can be stored for each report.
. Report Processing Timeout: The ability to time out report processing after certain
number of seconds.
. Report Processing Log: The ability to generate trace logs for report processing.
. Enable Windows Integrated Security: The ability to connect to report data
sources with the user’s Windows security credentials.

. Configure the SharePoint application pool process account to run as a domain user.
. Configure the Report Server Service to run as a domain user account.
Traditional steps for setting up SharePoint farms (refer to SharePoint documentation or
books) and scale-out Reporting Services can be applied. Here are some additional princi-
ples that have to be followed for SSRS scale-out deployments with SharePoint:
. All Report Servers in a scale-out deployment must run in SharePoint integrated
mode. It is not possible to mix and match modes.
. The instance of the SharePoint product (WSS 3.0 or MOSS 2007) that you install on
the Report Server must be the same version as the other nodes in the farm.
. There must be a single URL for the scale-out deployment that is used for configura-
tions in SharePoint farms because there is no support for configuring an individual
SharePoint WFE with individual Report Servers. You can create a single point of
entry to the scale-out deployment via a URL that resolves to a virtual IP for the NLB
cluster for Report Server instances.
Make sure you install the minimum SharePoint installation such as WFE on the SSRS
machines. Otherwise, you will see the error
The Report Server cannot access settings
in the SharePoint Configuration database
.
NOTE
SQL Server Books Online has a helpful article available titled “How to Configure
SharePoint Integration on Multiple Servers” (http://technet.microsoft.com/en-us/
library/bb677365.aspx).
There is also a helpful blog post on distributed server deployment for SharePoint inte-
grated mode at http://mosshowto.blogspot.com/2009/03/reporting-services-share
point-multiple.html.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
543

, because the user who ran the
MSI was not a site collection administrator. To view the RS integration feature in the
site, you need the site collection administrator to activate the Report Server feature.
NOTE
There is a white paper titled “Troubleshooting Integration with SQL Server 2005 and
Microsoft SharePoint Technologies” at http://msdn.microsoft.com/en-us/library/
bb969101.aspx. Even though it was created for 2005 SP2, it is relevant for 2008 inte-
gration, too.
Summary
Plan your deployment architecture for integrating Reporting Services with SharePoint care-
fully and follow these setup steps in this order:
1. Install Reporting Services.
2. Install SharePoint technology.
3. Configure Report Server for SharePoint mode.
4. Install the RS add-in for SharePoint.
5. Configure SharePoint to work with Report Server.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
This page intentionally left blank
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
CHAPTER
33
SharePoint Mode
Administration
IN THIS CHAPTER
. Security Overview
. User Authentication with

The other challenges for administration are security, autho-
rization, and programmability. The rest of the chapter
covers these areas.
Security Overview
For SharePoint integrated mode, the Report Server uses the
authentication and authorizations defined in the
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
546
CHAPTER 33 SharePoint Mode Administration
SharePoint web application to control access to report operations. This makes administra-
tion much simpler and primarily driven by the SharePoint administrator.
Reporting Services will process requests based on the SharePoint web application authenti-
cation settings, such as the following:
. Windows with integrated security (Kerberos enabled)
. Windows without impersonation
. Forms authentication
Kerberos is better compared to NTLM when multiple hops are required. So, it is good for
single-server or multiserver deployment scenarios and when external data sources are
involved that use Windows integrated credentials.
Custom security extensions for Reporting Services are not supported with SharePoint
integrated mode. All access to a Report Server in SharePoint Integrated mode originates
from the SharePoint web application. Report Server just sticks to the SharePoint authenti-
cation scheme.
Authorization to access Report Server items from SharePoint sites and libraries is mapped
to the built-in permission model for SharePoint. This means that after SharePoint is inte-
grated with Reporting Services, the existing permission levels of SharePoint users (for
example, Read, Contribute, or Full Control) for the site will apply to report operations,
too. This allows users to publish reports, view reports, create subscriptions, or manage

An understanding of the various security connections that are involved in completing a
reporting request from a SharePoint site comes in handy when planning or troubleshoot-
ing security for your deployment.
Windows Integrated Security
Figure 33.1 shows the authentication workflow for a SharePoint application that is config-
ured to use Windows integrated security and is integrated with Reporting Services. The
components in the diagram should be familiar from the chapter on the architecture of
SharePoint integration with Reporting Services.
33
To understand the various connections involved in the workflow, follow the numbered
arrows in Figure 33.1:
1. Windows User1 makes a request to render a report from the Report Viewer web part
via SharePoint.
2. The Reporting Services proxy connects to Report Server using the Windows User1
credentials and token.
3. If the connection is successful, Report Server needs to verify whether User1 has
permissions to access and render the report. This is done by connecting to the
SharePoint object model to verify the SharePoint permissions for User1 for the report.
4. If access is allowed, the Report Server proceeds to render the report.
5. Report Server will use the User1 credentials to retrieve and sync the latest copy of
the report from the SharePoint Content DB and then execute the report.
6. The report results are sent back to be displayed in the Report Viewer.
User1
(User1)
HTTP Req
(User1)
HTTP Req
Render
(User1)
(User1)

FIGURE 33.1
Authentication workflow using Windows integrated security.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
548
Trusted Account with Windows or Forms
Authentication
Figure 33.2 shows the authentication workflow for a SharePoint application that is config-
ured to use forms authorization or Windows without Kerberos. It relies on a predefined
trusted account that has permission to impersonate a SharePoint user on the Report Server.
CHAPTER 33 SharePoint Mode Administration
To understand the various connections involved in the workflow, follow the numbered
arrows in Figure 33.2:
1. Windows User1 makes a request to render a report from the Report Viewer web part
via SharePoint.
2. The SharePoint web application authenticates User1 against the SharePoint object
model and creates a SharePoint user token that contains the user identity and group
membership for User1.
3. The Reporting Services proxy connects to Report Server using User2, the trusted
Windows service account under which the SharePoint web farm is running, and
sends along the User1 SharePoint user token.
4. The Report Server validates whether the connection request is from a trusted
account by comparing User2 to account information that the Report Server retrieved
from the SharePoint Configuration databases when the Report Server started.
5. If the authentication is valid, the rendering request can proceed along with the
User1 SharePoint user token.
6. Report Server needs to verify whether the User1 SharePoint token contains the user
identity and permissions needed to access and render the report.
User1

3
5
7
6
2
8
4
Report
Server DB
SharePoint Config/ContentDB
Data Management
Processing
and
Rendering
On-
Demand
Sync
FIGURE 33.2
Authentication workflow using trusted account authorization.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
549
User Authorization with SharePoint
33
7. If access is allowed, the Report Server retrieves and syncs the latest copy of the report
from the SharePoint Content DB, and then executes the report.
8. Report Server returns the report results back to the SharePoint WFE using the
Windows trusted account, User2.
9. Reporting Services proxy returns the report results back to the Report Viewer web

tions, and opening and editing reports in Report Builder. The Members and Owners
groups provide these rights, but they provide other privileges, too. If you don’t want
your Report Builder users to have those privileges, you can create a custom group in
SharePoint and assign limited permissions.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
550
CHAPTER 33 SharePoint Mode Administration
. System User, System Administrator, My Reports: These roles don’t have an
equivalent mapping because they are not relevant in SharePoint mode.
Table 33.1 is a reference list of SharePoint permissions, regardless of whether they are
included in default SharePoint groups, and the Report Server operations that get enabled
with the permission.
TABLE 33.1
SharePoint Permissions
SharePoint
Permission
Owners Members Visitors Report Server Operation
Manage
Lists
X Create a folder in a SharePoint
library
Manage
report
history
Add Items X X Add reports, report models, shared
data sources, and external image
files to SharePoint libraries
Create shared data sources