Tuesday, January 27, 2009

Integrating Reporting Services 2008 with SharePoint 2007 Step 3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1

Step 3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1 : Operations performed with Reporting Services Configuration tool

Introduction

This post is the third of a group showing how to use SQL Server 2008 Reporting Services with SharePoint 2007.
The previous steps are:
The next steps are I have chosen a Simple Farm Topology with SQL Server 2008 and Windows SharePoint Services 3.0 on the same Virtual Machine. This machine has just Application Server role and not Domain Controller role, so the Service Accounts for both, SharePoint and SQL will be machine local accounts.
The VM runs Windows 2003 Server OS
The goal is to quickly allow someone to create reports and to discover SharePoint Integrated Mode features: deployment, Web Parts dashboards, subscription, snapshotings, etc..
Even planning for further deployments in a Farm environment with several servers and Active Directory, it may be usefull to have been familiarized with involved products installation, configuration and use, by having tested it in a Simple Farm Topology with one computer and local accounts.

1 - Environment Configuration

Number of Virtual machines: 1
Operating System: Windows 2003 Server
Server Roles: Application Server
Topology: Simple Farm
Service Accounts: SQL Server Service Account: Local Administrator
Setup user account: Local Administrator
Server farm account/Database access account: Local Administrator

2 - Referred Documentation

3 - Prerequisites
4 - Configuration Overview

Notice that Report Server Configuration for SharePoint Integrated Mode includes two main parts, performed with two different tools:
  • This post presents the first part of Report Server Configuration for SharePoint Integrated Mode. This is the part you perform with Reporting Services Configuration tool.
  • The second part, performed in SharePoint Central Administration is available in:
    Step 4 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 2: Operations performed in SharePoint Central Administration (includes Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies Installation) (to be posted)
This part one of Report Server Configuration for SharePoint includes the following steps:
  • Step 1: Start the Reporting Services Configuration tool and connect to the report server instance.
  • Step 2: Configure a URL (Reporting Services Configuration).
  • Step 3: Create a Report Server Database
  • Step 4: Verify the part 1 of Reporting Services Configuration


5 - Configuration
5.1 Step 1: Start Reporting Services Configuration
1 - Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then point to Configuration Tools.
2 - Click Reporting Services Configuration Manager



3 - In Server Name, specify the name of the computer on which the report server instance is installed. The name of the local computer appears by default, but you can type the name of a remote SQL Server instance if you want to connect to a report server that is installed on a remote computer. In our case, installation on a single machine we just have to let the default option.
4 - In Report Server Instance, select the SQL Server Reporting Services instance that you want to configure. Only SQL Server 2008 report server instances appear in the list. You cannot configure earlier versions of Reporting Services. In our case, default option can be used.
5 - Click Connect.



5.2 Step 2: Create a Report Server Database
1 - Click Database in the left menu to access the Database page



2 - On the Database page, click Change Database.
3 - Click Create a new database.



4 - Click Next.

5- Connect to the instance of the Database Engine that you will use to create and host the report server database:
For the current installation on a single machine we can keep default options



6 - Click Test Connection to validate the connection to the server.

7 - Click Next.

8 - Specify properties used to create the database.
The default option can be used, excepted the server mode

9 - Select SharePoint Integrated Mode.




10 - Click Next.

11 - Specify the credentials used by the report server to connect to the report server database.
Here also, default options are fine.



12 - Click Next.

13 - Review the information on the Summary page to verify the settings are correct.



14 - Click Next.

15 - wait while Report server Database Configuration Wizard configures report Server Database.




16 - Click Finish
Next figure shows the last page of Report Server database Configuration Wizard when configuration is complete.



5.3 Step 3: configure a URL for the Report Server Web service

1 - Click Web Service URL in the left menu.





2 - Look at default values.
Prerequisites:
  • Verify that IIS (if it is installed) does not have virtual directory with the same name you plan to use.
  • Verify that the Report Server Web Service URL is not already used especially by a SharePoint site.
In our case, with the current environment and topology, we can use default values.

3 - Click on Apply



5.4 Step 4: Verify the part 1 of Reporting Services Configuration

Verify the connection by clicking the URL on the Report Server URL page.

If a Web Page is launched as shown in next figure, the part 1 of your Report Server Configuration for SharePoint Integrated Mode is done.




3 comments:

Yang Li said...

at the end of this step, I got the following error when I click on the web service URL:

"The report server has encountered a configuration error. If the report server is configured to use SharePoint integrated mode, verify that the server is joined to a SharePoint farm and that the Report Server service account has been granted access to the SharePoint farm. (rsServerConfigurationError)"

any idea what I missed? Thanks

Marc Charmois said...

Yang Li,

This "error" message, is not necessary wrong.
If for example, you have used for running the Reporting Services service an Account that has not enough permissions on SharePoint Databases, it is the normal message you will get when you have achieved the first part of Report Server configuration and try to display the web page provided by the Report Server Web Service.
To fix this "error", then, you have to perform the second part of Report Server configuration (the one in SharePoint Central Administration), that will grant SharePoint databases access to the account that is running Reporting Services service.

So, check what account you are using to run Reporting Services service, and perform the second part of the configuration. At the end of the configuration this account should have been granted access to the SharePoint Databases, and you should be able to display the Report Server Web Service page properly...

Good Luck.

Marc

Dhiman Halder said...

Hi Yang/Marc,

I think you guys are missing are missing a step of installing Microsoft® SQL Server® 2008 R2 Reporting Services Add-in for Microsoft SharePoint® Technologies 2010

Link: http://www.microsoft.com/en-us/download/details.aspx?id=622

thanks.