Tuesday, March 10, 2009

SQL Server Reporting Services 2008 in SharePoint Integrated Mode - Distributed Server Deployment with Three computers

An example of deployment Topology for SQL Server Reporting Services 2008 in SharePoint Integrated Mode:
Distributed Server Deployment with Three computers.

 

Download a .doc version of this post

Summary

Introduction

1 - Prerequisites

01 - Windows Server Domain and Active Directory

02 - Service Accounts

03 - Available Server

2 - Installation Overview

01 - Topology description

- Computers

- Service Accounts

02 - Recommended installation sequence (installation overview - workarounds) 

3 - Installation

3.1 - Step 1: Installation of SQL Server 2008 on SQLSERVER2008

01 - Start SQL Server 2008 installation

02 - Selecting Installation features

03 - Server Configuration

04 - Not to forget

3.2 - Step 2: Installation of SQL Server Reporting Services 2008 on SSRS2008SPIM

01 - Start SQL Server 2008 installation

02 - Selecting Installation features

03 - Server Configuration

04 - Reporting Services Configuration Page

05 - Check Installation

3.3 - Step 3: Installation of SharePoint 2007 on SHAREPOINT2007

01 - Create SharePoint databases and grant permissions to SPS_SETUP domain account

02 - Start SharePoint installation on SHAREPOINT2007

03 - Running SharePoint Product and Technologies Configuration Wizard

03 - Creating a Web Application on port 81 and a TestReportingServices Site Collection

3.4 - Step 4: Installation of SharePoint 2007 on SSRS2008SPIM

01 - Start SharePoint installation on SSRS2008SPIM

02 - Check SharePoint installation on SSRS2008SPIM

3.5 - Step 5: Configuration and test of the Report Server on SSRS2008SPIM

01 - Preparing SSRS_SERVICE account to configure Reporting Services

02 - Creating report Server databases

03 - Provisioning Report Server Web Service

3.6 - Step 6: Installation of SQL Server Reporting Services 2008 Add In for SharePoint on SHAREPOINT2007, Configuration and Check

01 - Installation of SQL Server Reporting Services 2008 Add In for SharePoint on SHAREPOINT2007

02 - Configuration of reporting services in SharePoint Central Administration

3.7 - Step 7: BIDS installation, Report creation, deployment and test

01 - BIDS installation

02 - Report Creation

03 - Report Deployment

04 - Report Data Source Configuration

05 - Report Displaying


Introduction

This tutorial includes the key steps for deploying and configuring:
  • SQL Server 2008 Database Engine
  • SQL Server Reporting Services 2008
  • SharePoint 2007 (Windows SharePoint Services 3.0)
in an environment composed of three computers.

This tutorial is completing these previous ones:

Integrating Reporting Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 Installation
Integrating Reporting Services 2008 with SharePoint 2007 Step 2 - SharePoint Installation
Integrating Reporting Services 2008 with SharePoint 2007 Step 3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1
Integrating Reporting Services 2008 with SharePoint 2007 Step 4 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 2
Installing SQL Server 2008 samples: Adventure works (Integrating Reporting Services 2008 with SharePoint 2007 Step 5)
Integrating Reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment

that was showing how to install SQL Server Reporting Services 2008 in SharePoint integrated mode with SharePoint 2007 but on a single computer. In this tutorial, we are going to build a real Production Environment.
Of course it would be better before doing this installation to have done at least once the installation on a single machine with one service account: local administrator account.
01- Tutorial goal
In this installation tutorial, I will show a complete example of installation with the required service accounts, in order to allow an Infrastructure Administrator to install SQL Server Reporting Services 2008 in SharePoint Integrated mode with SharePoint 2007 on three different computers for a real production environment compliant with the principle of least privilege administration.

What I have noticed performing several interventions for different clients, is that the knowledge required installing even Microsoft Office SharePoint Server 2007 in a QA, staging, or Production Environment is increasing more and more because the involved products (SharePoint, SQL Server, IIS, etc.) are becoming more and more complex. For that reason, it is difficult for people working in Infrastructure and Network Area to have the entire required acknowledgement, and very often they need the support of SharePoint developers that are used to install SharePoint on their own development environment.
However, on the opposite, SharePoint developers are not deploying very often the products on several machines using the several service accounts required by the least-privilege administration policy that is recommended when you configure a Production Environment. They are often, not either aware of the configuration key elements that are needed by SharePoint, SQL Server, IIS, etc. to work on a multiple server environment.
If you add SQL Server Reporting Services 2008 to SharePoint 2007, the number of operations to perform and choices to take, much more increases.
So there is a knowledge gap to fill...
1 - Prerequisites
01 - Windows Server Domain and Active Directory
The machines ready for installation have joined a Domain. An Active Directory is available in this Domain.
The name of the Domain used in this tutorial is "CORPORATE".

02 - Service Accounts
As this tutorial is rather made for people working in Infrastructure Area, I do not show how to create a domain account in Active Directory, and I put in the prerequisites the presence in the Active Directory of the following Service Accounts in order to be able to build an environment compliant with the principle of least privilege administration (Plan for administrative and service accounts (Office SharePoint Server)):
  • SQL_SERVICE
  • SPS_SETUP
  • SPS_FARM
  • SSRS_SERVICE
  • SPS_AppPoolWebApp81
I will explain these accounts properties and roles in the next section.

03 - Available Server
Of course you have three available Servers running Windows 2003 Server OS ready for the installation. The machine names I use in this post are the followings:
  • SQL2008
  • SSRS2008SPIM (for SQL Server Reporting Services 2008 in SharePoint Integrated Mode)
  • SHAREPOINT2007
I will indicate these servers role in the next section (that is to mean if they need to be Application Server or not, thus if IIS is needed to be installed or not).
2 - Installation Overview
01 - Topology description
this is a part of the MSDN article (Deployment Topologies for Reporting Services in SharePoint Integrated Mode)
[...]
Three-Computer Deployment


The following illustration shows components for a three-computer deployment.



The first computer hosts an instance of a SharePoint product or technology. The second computer hosts SQL Server Reporting Services. The third computer hosts an instance of the Database Engine.

In the illustration, notice that the Database Engine does not require a local installation of a report server or an instance of a SharePoint product or technology.

To use a remote database server with a deployment of a SharePoint product or technology, choose the Advanced installation option when installing an instance of a SharePoint product or technology, or use Central Administration to modify the database server setting. To select a remote database server for Reporting Services, use the Reporting Services Configuration tool. For more information about edition and connection requirements for a report server database, see Creating a Report Server Database and How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration) .
[...]
- Computers:
Machine 1 Name: SQL2008
Operating System: Windows 2003 Server
Server Role none (no more need of IIS for SQL Server 2008)
Deployed products: SQL Server 2008 (Database Engine, Full Text, Basic Management Tools, Management Tools Complete)
Topology: one instance (no named)

Machine 2 Name: SSRS2008SPIM (for SQL Server Reporting Services 2008 SharePoint Integrated Mode)
Operating System: Windows 2003 Server
Server Roles: Application Server (no more need of IIS for Report server 2008 since it has its own IIS, but we need IIS for SharePoint)
Deployed products: SQL Server 2008 Reporting Services, Windows SharePoint Services 3.0
Topology: SQL Server 2008 Reporting Services: one instance, simple (no Scale Out)
Windows SharePoint Services 3.0: Minimal Installation

Machine 3 Name: SHAREPOINT2007
Operating System: Windows 2003 Server
Server Roles: Application Server (we need IIS for SharePoint)
Deployed products: Windows SharePoint Services 3.0
Topology: simple Farm

- Service Accounts:
Account Name and Password: SQL_SERVICE Passw0rd
Account Description: SQL Server prompts for this account during SQL Server Setup. This account is used as the service account for the following SQL Server services:
  • SQL Server Database Engine
  • SQL Server Agent
we will use it also for:
  • SQL Full-Text Daemon Launcher
Domain vs. Local Domain Account (can be a local account)
Permissions no special permissions required
Referenced documentation: Technet: Plan for administrative and service accounts

Account Name and Password: SPS_SETUP Passw0rd
Account Description: SharePoint SetUp user Account on SHAREPOINT2007 Server
SharePoint SetUp user Account on SSRS2008SPIM Server

The service account that is used to run:
  • Setup of SharePoint on each server computer
  • The SharePoint Products and Technologies Configuration Wizard
  • The Psconfig command-line tool
  • The Stsadm command-line tool
  • Setup of Reporting Services on Report Server computer
  • Reporting Services Configuration Manager on Report Server computer
(this account might be used in operations of recovery, when you need to reinitialize Reporting Services Account for example if you tried to change this account or its password has expired)
cf. : Configuring the Report Server Service Account
Domain vs. Local Domain Account (cannot be a local account)
Permissions Administrator of SHAREPOINT2007
Administrator of SSRS2008SPIM

SQL Server login on the computer running SQL Server.
Member of the following SQL Server security roles:
  • securityadmin fixed server role
  • dbcreator fixed server role
  • dbowner of SharePoint_Config database
  • dbowner of SharePoint_AdminContent database
Referenced documentation: Technet: Plan for administrative and service accounts

Account Name and Password: SPS_Farm Passw0rd
Account Description: SharePoint Server Farm Account
This account is also referred to as the database access account.
This account is:
  • The application pool identity for the SharePoint Central Administration Web site.
  • The process account for the Windows SharePoint Services Timer service.
Domain vs. Local Domain Account (cannot be a local account)
Permissions no special permissions required
Its permission for SharePoint databases will be granted automatically during installation process
Referenced documentation: Technet: Plan for administrative and service accounts

Account Name and Password: SSRS_SERVICE Passw0rd
Account Description: SQL Server prompts for this account during SQL Server Reporting Services 2008 Setup. This account is used as the service account for the following SQL Server services:
  • SQL Report Server on SSRS2008SPIM
Domain vs. Local Domain Account (cannot be a local account in the current topology: Distributed Server Deployment with Three computers)
Permissions
The SharePoint Database Access will be granted during Report Server Configuration in SharePoint Central Administration on SHAREPOINT2007
Referenced documentation: msdn: How to: Configure a Service Account for Reporting Services

Account Name and Password: SPS_AppPoolWebApp81  Passw0rd
Account Description:

Application pool identity

This account will be used when creating the fist Web Application and its Application Pool.

Domain vs. Local Domain Account (cannot be a local account in the current topology: Distributed Server Deployment with Three computers)
Permissions

No manual configuration is necessary.

  • Use a separate domain user account for each application pool.

  • This account should not be a member of the Administrators group on any computer in the server farm

  • The following are automatically configured:

    • Membership in the db_owner role for content databases and search databases associated with the Web application.

    • Access to read from the configuration and the SharePoint_AdminContent databases.

    • Additional permissions for this account to front-end Web servers and application servers are automatically granted.


    Referenced documentation: msdn: How to: Configure a Service Account for Reporting Services

    02 - Recommended installation sequence
    The installation performed in this tutorial includes the following steps:
    • Step 1: Installation of SQL Server 2008 on SQLSERVER2008
    • Step 2: Installation of SQL Server Reporting Services 2008 on SSRS2008SPIM
    • Step 3: Installation of SharePoint 2007 on SHAREPOINT2007
    • Step 4: Installation of SharePoint 2007 on SSRS2008SPIM
    • Step 5: Configuration and test of the Report Server on SSRS2008SPIM
    • Step 6: Installation of SQL Server Reporting Services 2008 Add In for SharePoint on SHAREPOINT2007, Configuration and Check.
    • Step 7: BIDS installation, Report creation, deployment and test.

    WARNING:
    when you are installing SQL Server Reporting Services 2008 in SharePoint Integrated Mode, and want to keep being compliant with the least-privilege administration principle, this principle and its limited permissions become a problem because both, the configuration of report server and the installation of SQL Server Reporting Services Add in for SharePoint fail due to the limited access of all these accounts. But with a workaround you can install the products combination properly and restore the permissions keeping compliant with the least privilege administration. Read carefully the following:

    this is a part of the Technet article Plan for administrative and service accounts (Windows SharePoint Services)
    [...]

    Least-privilege administration requirements when using domain user accounts

    Least privilege administration is a recommended security practice in which each service or user is provided with only the minimum privileges needed to accomplish the tasks they are authorized to perform. This means that each service is granted access to only the resources that are necessary to its purpose. The minimum requirements to achieve this design goal include the following:

    - Separate accounts are used for different services and processes. 
    - No executing service or process account is running with local administrator permissions.

    By using separate service accounts for each service and limiting the permissions assigned to each account, you reduce the opportunity for a malicious user or process to compromise your environment.
    Least privilege administration with domain user accounts is the recommended configuration for most environments.

    [...]

    Regarding Windows SharePoint Services 3.0, let us examine the minimal services and processes needed and we will understand least privilege basic administration for SharePoint.
    • Setting up SharePoint on each server computer: task requiring local administrator permissions
    • Running the SharePoint Products and Technologies Configuration Wizard: task requiring local administrator permissions
    • Executing a Psconfig command-line: task requiring local administrator permissions
    • Executing an Stsadm command-line: task requiring local administrator permissions
    • Running the process of application pool identity for the SharePoint Central Administration Web site: process that does not require local administrator permissions
    • Running the Windows SharePoint Services Timer service: service that does not require local administrator permissions
    Looking at the previous enumeration, if we assign the four first tasks to an account that has the local administrator permission, and the two last to an account that has not the administrator permissions, we are compliant with the least privilege administration principle.
    Thus, the least privilege administration principle for SharePoint is based on two main user accounts:
    • Setup Account
    • Farm Account
    The Setup Account is Administrator of all the servers where SharePoint is deployed. It is used also to run the SharePoint Products and Technologies Configuration Wizard, and to execute Stsadm and Psconfig command. This account is also one of the Farm administrators, and has access to the entire operations provided by the SharePoint Central Administration.

    The Farm Account appears as System Account when you are signed in with it on a SharePoint site, but is not Administrator of any SharePoint server, thus you cannot perform a Psconfig or Stsadm command using it. You cannot run the SharePoint Products and Technologies Configuration Wizard with it.
    Furthermore, in SharePoint Central Administration, the Farm account cannot access to the entire administration tasks, for example, when you are connected to SharePoint Central Administration with this account, in the Tab Operations, you cannot see the following links:
    • Services on Server
    • Incoming e-mail settings
    And in Application Management Tab
    • Remove SharePoint from IIS Web Site

    The Application Pool Identity Account is an account used during the creation of the first Web Application. The least privilege administration requirements for this account are:

    • Use a separate domain user account for each application pool.

    • This account should not be a member of the Administrators group on any computer in the server farm.


    The Report Server Account

    Adding Reporting Services to SharePoint, there is a fourth account that must have least privilege, it is the Report Server account, here called SSRS_SERVICE. For this account, very simple, it has no permissions at all. However, it might be useful to use it during installation process, you will see why...

    The Installation Sequence overview with the workarounds


    Now let us see how we are going to use these accounts performing an installation of Reporting Services and Sharepoint on two different computers, and the workaroud to install the Reporting Services Add In for SharePoint.

    01 - Use the SharePoint Setup Account to install Reporting Services on the Report Server computer (it has local administrator permissions for this computer because it will be used to install SharePoint on this computer later).

    02 - Use the SharePoint Setup Account to install SharePoint on the SharePoint Server.

    03 - Use SharePoint Setup Account to create the Web Application but when prompted, refence the Application Pool Identity Account
    The additional operation will be to grant the dbo or db_owner permissions for the new created content database to the SPS_SETUP account as specified in Technet documentation previously mentioned:
    After each database has been created, change the database owner (dbo or db_owner) to the Setup User account.

    04 - Configuring Reporting Services on the Report Server.

    Here again there is additional operation to perform.
    Let us examine the permissions required by the account configuring SQL Server Reporting Services 2008.

    1. Local administrator of the Report Server computer
      If the account you are logged on with is not local admnistrator of the computer, you cannot open Reporting Services Configuration Manager.

    2. high privileges on the Database Engine
      During the Report Server Databases creation, the configuration process is not only creating the ReportServer and the ReportServerTemp Databases, but also configuring the Databases (rights generation, connection rights application), and the account used by the process needs high privileges on the Database Engine. For example, there is a request on msdb database :
      USE msdb
      if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
      BEGIN
       EXEC sp_addrole 'RSExecRole'
      END
      

      The consequence is that you cannot configure Reporting Services with SPS_SETUP account, since it has unfortunately not enough permissions on the database engine.


    You have two solutions:

    You grant temporary the database engine sysadmin permission to the Setup account
    You add temporary the SQL DataBase Engine administrator account to the administrators group of Report Server computer, and you use this account to configure reporting Services.

    (I am quite sure that the second solution will be never used).

    05 - Workaround for Add In installation:
    When you are installing Reporting Services Add In for SharePoint on the SharePoint computer, you have temporary to grant the database engine sysadmin permission to the Setup account AND to add temporary the Farm account to the machine Administrators, otherwise the Reporting Services Add In installation fail over.

    06 - Use the SharePoint Setup or Farm Account to configure Reporting Services on the SharePoint Server.


    Installation Trick:

    In the current installation tutorial I have taken advantage of this workaround to perform Reporting Services Configuration with the Reporting Services Account (here called SSRS_SERVICE). Doing that I am able to check that the Report Server Web Service works well before installing Reporting Services Add In for SharePoint.
    In order to be allowed to do that, I had temporary to grant the database engine sysadmin permission to the SSRS_SERVICE account and to temporary add this account to the Report Server computer Administrators. (only computer Administrators can configure Reporting Services)

    But you can skip this and configure Reporting Services with the SharePoint Setup account. In that case, you will still have to grant special and temporary sysadmin permissions for this account but will unfortunately have to wait the end of the installation to be sure that the Report Server Web Service works properly.

    I will remind these operations in time using the red color.


    3 - Installation
    3.1 - Step 1: Installation of SQL Server 2008 on SQLSERVER2008
    01 - Start SQL Server 2008 installation

    Logon to SQL2008 with a domain user account that is administrator of the computer.
    Launch SQL Server 2008 installation.
    Follow all steps of the previous post Integrating reporting Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 installation from step 1 to step 9.

    02 - Selecting Installation features

    On Step 10 - Selecting Installation features, select:
    • Database Engine Services
    • Full-Text Search (it is for SQL 2008 Samples: Adventure Works).
    • Management Tools - Basic (SQL Server Management Studio).
    • Management Tools - Complete (SQL Server Management Studio complement included those for Reporting Services).



    Follow the procedure until step 12

    03 - Server Configuration


    Use the same account for all services: SQL_SERVICE.
    Set the SQL Server Agent account to be started Automatically, because it will be used for Standard or Data Driven Subscriptions in Integrated mode and thus, has to be enabled and always started.





    04 - Not to forget

    Do not forget the Collation settings otherwise you will have an exception during SharePoint deployment.
    This is the Installation Complete Screen Shot.



    and the screen shot of the SQL Server Management Studio open.



    Do not forget to configure connections protocol with SQL Server Network Configuration Area

    3.2 - Step 2: Installation of SQL Server Reporting Services 2008 on SSRS2008SPIM
    01 - Start SQL Server 2008 installation

    Logon to SSRS2008SPIM with a domain user account that is administrator of the computer.
    Launch SQL Server 2008 installation.
    Follow all steps of the previous post Integrating reporting Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 installation from step 1 to step 9.

    02 - Selecting Installation features

    On Step 10 - Selecting Installation features, select:
    • Reporting Services



    Follow the procedure until step 12

    03 - Server Configuration


    Use the SSRS 2008 service account: SSRS_SERVICE.



    04 - Reporting Services Configuration Page

    We have to select the last option because SharePoint is not installed, neither on SHAREPOINT2007 computer nor on SSRS2008SPIM one.



    05 - Check Installation

    This is the Installation Complete Screen Shot.



    The screen shot of the Reporting Services Service.



    The screen shots of the welcome Pages of the Report Server Configuration Tool





    3.3 - Step 3: Installation of SharePoint 2007 on SHAREPOINT2007
    01 - Create SharePoint databases and grant permissions to SPS_SETUP domain account
    Logon to SQL2008 with SQL_SERVICE domain service account.
    open SQL Server management Studio.



    Create databases as in the previous post: Integrating Reporting Services 2008 with SharePoint 2007 - SharePoint Installation

    In Management Studio right click Login node click "New Login"



    On the "Login-New" Pop Up type "CORPORATE\SPS_SETUP" as new login name.



    You can check the new Login creation in SQL Server 2008 Management Studio



    Right click the new login and click "Properties"



    Login-properties Dialog is opening.



    Double click Server Roles, and in the opening Dialog, check "SecurityAdmin" and "DbCreator".



    double Click User mappings, and in the opening Dialog, locate the two SharePoint databases SharePoint_Config and SharePoint_AdminContent.
    For each of these databases, grant SPS_SETUP account the following permission:
    "DbOwner".



    02 - Start SharePoint installation on SHAREPOINT2007

    Logon to SHAREPOINT2007 with the SPS_SETUP domain user account.
    Launch SHAREPOINT 2007 installation as in the previous post.
    Follows the steps 4 to 9. (Until closing the first installation wizard with the check box unchecked).

    Open a command prompt and type the following line:
    psconfig -cmd configdb -create -server SQL2008 -database SharePoint_Config -user CORPORATE\SPS_FARM -password Passw0rd -admincontentdatabase SharePoint_AdminContent
    execute the command.



    You will obtain this window after performing the command.



    03 - Running SharePoint Product and Technologies Configuration Wizard
    Run the SharePoint Product and Technologies Configuration Wizard as in the previous post, but with the multiple Server Environment parameters.
    You can check the parameters on the following screen shots.







    At the end of the process as usual, you will obtain the displaying of the Central Administration Home page



    03 - Creating a Web Application on port 81 and a TestReportingServices Site Collection.

    In order to have a Site Collection with a Root Web Site that can display SQL Server reporting services 2008 Reports, DataSources and so on in a SharePoint 2007 document library,

    Perform the steps 2 to 4 of the previous post:

    Integrating Reporting Services 2008 with SharePoint 2007 step 4 - Configuring reporting services for SharePoint 3.0 Integration 

    For step 5 because we are in a multiple server environement and want to be compliant with the least privilege administration principle, do the following:

    In the "Create New Web Application" page, Application Pool section, select "Create new application pool" option, type the name of your new Application Pool, select "Cofigurable" and type the name and the password of the Application Pool Identity domain account.



    Then, perform your Site Collection creation and check that the Site Collection is properly created.

     

    Now, we have to grant the dbo permission to the SPS_SETUP account for the brand new created WSS_Content_WEBAPP81 content database. If we do not do that, we will not be able to perform operations using stsadm command for the new created Web Application, its Site Collections, its Web Sites, etc., because we will get an "Access Denied" exception.

    Logon to the SQL Server computer (SQL2008).
    Open the SQL Management Studio.
    Locate the login for SPS_SETUP account.
    Right Click the Login and select properties.
    In the opening dialog click "User Mapping".
    Locate the WSS_Content_WebApp81 database.
    Check the check box for the database.
    In "Database role member ship for: WSS_Content_WEBAPP81"  pane check the check box for db_owner.
    Click OK.



    3.4 - Step 4: Installation of SharePoint 2007 on SSRS2008SPIM
    01 - Start SharePoint installation on SSRS2008SPIM
    Logon to SSRS2008SPIM with the SPS_SETUP domain user account (that has Administrator permissions on this server too).

    First of all start by stopping the IIS default web site in order to avoid conflicts with the web service of Report server that also uses port 80.

    Launch SHAREPOINT 2007 installation as in the previous post: Integrating Reporting Services 2008 with SharePoint 2007 - SharePoint Installation

    Follows the steps 4 to 9. (Until closing the first installation wizard with the check box unchecked).
    When Setup finishes, a dialog box prompt you to complete the configuration of your server. Be sure that the "Run the SharePoint Products and technologies Configuration Wizard now" check box IS selected. (It is the opposite of what we have done for installation of SharePoint 2007 on SHAREPOINT2007 server).



    Click "Next". On the "Connect to a Server farm" dialog select "Yes I want to connect to an existing Farm".



    Click "Next". On the "Specify Configuration Database Settings" type "SQL2008" for the databases server name, then click "Retrieve Database Names" button.



    The wizard retrieves not only the SharePoint Configuration database, but also the Farm Service Account...



    ...so you just have the password to type.



    Click "next" to launch configuration and wait until the "Configuration Successful" dialog.



    As usual, when you close this Dialog, SharePoint Central Administration Web Site Home Page Appears.

     

    02 - Check SharePoint installation on SSRS2008SPIM


    Take advantage of this page to check that SSRS2008SPIM server has now joined the SharePoint Farm.
    Click Operation Tab, then servers in farm link. You can see the presence of the computer as a new Server of the SharePoint Farm.



    There is another thing you can notice since SSRS2008SPIM is a Web Front End. Open IIS and Notice that a "SharePoint - 81" Web Application in now available. Right click the Web Application node in IIS and click "Browse".



    The testReportingServices Site Collection Root Web Site will be served by the SSRS2008SPIM computer as it is become a new Web Front End of a SharePoint Farm.

    3.5 - Step 5: Configuration and test of the Report Server on SSRS2008SPIM
    I could keep using SharePoint Setup account (SPS_SETUP) to configure report server, but configuring Report Server with Reporting Services account will allow me to check the Report Server Web Service before having installed Reporting Services Add In for SharePoint on the SharePoint computer, and before having configured SharePoint for Reporting Services.

    (If you prefer to keep using SharePoint Setup account (SPS_SETUP) to configure report server do not forget to Logon to SQL2008 and grant the sysadmin permissions to SPS_SETUP since it has not enough permissions to configure the Report Server Databases.)

    01 - Preparing SSRS_SERVICE account to configure Reporting Services

    Add SSRS_SERVICE to the Administrators Group of the Report Server computer (SSRS2008SPIM). To use Report Server Configuration Manager you must be logged as an Administrator.

    Logoff from SSRS2008SPIM and re-logon with SSRS-SERVICE.

    Logon to SQL2008 and grant temporary the sysadmin permissions to SSRS_SERVICE

    02 - Creating report Server databases

    Open Report Server Configuration Manager and connect to the default instance of the current server



    On the left menu click database, then, on opening dialog, click change database to open the change database dialog.



    Select "create a new report Server Database", then click "Next".

    On database server dialog notice that the SQL 2008 Server Computer has been retrieved, and that the account is SSRS_SERVICE.
    Do not forget to test database connection.



    On the next dialog select SharePoint Integrated mode.



    On the next dialog select windows credential and type again SSRS_SERVICE



    Check the summary and launch database creation





    03 - Provisioning Report Server Web Service
    On the left menu of the report server Configuration Manager, this time click Web Service URL



    Click Apply to provisioning the Web Service.



    Click the URL to check the Web service. Notice that no mention of SharePoint is visible for the moment.



    Logon to SQL2008 and remove the sysadmin permissions to SSRS_SERVICE

    Click again the URL to check the Web service. Notice that the connection requires now the configuration in SharePoint. But we will not be able to perform it before having installed SSRS Add In for SharePoint on SHAREPOINT2007 computer.



    Remove SSRS_SERVICE from the Administrators Group of the Report Server computer (SSRS2008SPIM)



    3.6 - Step 6: Installation of SQL Server Reporting Services 2008 Add In for SharePoint on SHAREPOINT2007, Configuration and Check.
    01 - Installation of SQL Server Reporting Services 2008 Add In for SharePoint on SHAREPOINT2007

    Logon to SQL2008 and grant the sysadmin permissions to SPS_SETUP

    Logon to SHAREPOINT2007 and grant the Administrator permissions to SPS_FARM

    Then, perform SQL Server Reporting services 2008 Add In for SharePoint 2007 installation as shown in my previous post:

    Integrating Reporting Services 2008 with SharePoint 2008 Step 4 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 2: Operations performed in SharePoint Central Administration.
    Step 1 to 8.

    When it is done, go to SharePoint central Administration, Application Management Tab, and locate Reporting Services section.



    02 - Configuration of reporting services in SharePoint Central Administration
    Open "Manage Integration Setting" Page and type the report Server URL.

    For the "Authentication Mode" Section select "Trusted Account" that is the right mode for the present configuration in multiple servers with NTLM.
    For more information there is a good post of Reza Alirezaei:

    SSRS 2008 integrated mode: security



    Open the Grant Database Access Page, and type the Report Server Computer name.



    Click OK.
    In the Enter Credentials dialog box, specify the User Name and Password to connect to the report server to retrieve the service account information. Be sure to enter credentials for an account that is a member of the local Administrators group on the report server computer. In our case, SPS_SETUP has the required permissions to perform this task.



    When the page is refreshed, you can open a browser window and type the url of the report Server Web Service: http://ssrs2008spim/reportserver



    Notice that you are now connected to the SharePoint Sites and Libraries. Your Installation is done!

    Don't forget to:

    Logon to SQL2008 and remove the sysadmin permissions to SPS_SETUP

    Logon to SHAREPOINT2007 and remove SPS_FARM from the Administrators group.

    3.7 - Step 7: BIDS installation, Report creation, deployment and test
    01 - BIDS installation
    I have chosen to install BIDS on SSRS2008SPIM computer to be as close as possible to a real Production Environment where BIDS will not be installed on the SharePoint machine.
    Doing that, I will be sure to succeed in deploying report remotely using the current environment.

    Logon to SSRS2008SPIM with SPS_SETUP account. Run the SQL Server installation package, and select "Add a feature to the current installation". Select Visual Studio and wait until installation process is complete.

    02 - Report Creation
    Open BIDS.
    Create a report (if needed install Adventure Works as a sample Database).
    You can do my previous tutorial:

    Integrating reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment

    For the project properties, type the following:
    • TargetDataSourceFolder: http://SHAREPOINT2007:81/shared documents
    • TargetReportFolder: http://SHAREPOINT2007:81/shared documents
    • TargetServerURL: http://SHAREPOINT2007:81/



    03 - Report Deployment
    Perform deployment with BIDS.
    If you try to see the deployed report now, you will have a credential exception. You will be able to see the type of this exception only on the report Server machine: SSRS2008SPIM.

    The error type will only be displayed on the Report Server Web Service when you are logged on to SSRS2008SPIM. And Reporting Services errors can be examined. This is the path of the Log Files of SQL Server 2008 Reporting Services:

    c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles

    04 - Report Data Source Configuration
    To solve this error logon to SHAREPOINT2007.
    Go to the Shared Documents documents library of the TestReportingServices Web Site, and locate the file Report1.rdl (that has not its extension visible).



    Expand the Report contextual menu and select "Manage Data Sources".



    The Manage Data Sources Page is displaying



    Click the Datasource1 link to open the edit the data source connection information.
    Select "Stored Credentials"
    Use an account that have read permissions on the databse used for this report. For example in my case, I used the SSRS service account credentials, that will be quite never done in real life.
    Check the check box: "Use as Windows Credentials".



    05 - Report Displaying
    You can now display your report in:
    • RSViewerPage.aspx



    • Report Viewer Web Part



    • Report Server Web Service from the SHAREPOINT2007 computer





    • Report Server Web Service from the SSRS2008SPIM computer




    A special thank you to Mahmoud Meflah for his help regarding DataSource Configuration in SharePoint.

    32 comments:

    Fishman said...

    Marc,

    Great stuff thanks for going through the pain of figuring all this stuff out! We are implementing a couple 3 tier sharepoint environments that will include SSRS 2008.. Sharepoint is truely a manyheaded beast.

    If there is one thing I'd recommend with your blog is making the screen shots larger as they are pretty much unreadable from what I can tell.

    Keep up the good work!

    Fishman said...

    I was referring to how this stuff looks in printed form not online.. I guess I can't have my cake and eat it too! :-)

    Marc Charmois said...

    Hi Fisman,

    Thank you for your encouragements!

    You are right. I set this screen width at the beginning of that blog in order to make the C#, HTML, XML code samples to be easier to read, and I didn’t care to set the code font-size or enlarging the screen shots just for certain posts at this time instead of enlarging the screen shots for all the posts.

    Now, because I have written long posts with more texts and pictures than code samples, and because I have noticed that the posts concerning Reporting Services are often read, I was just planning to change the screen shots width.
    I have already put Bookmarks inside certain posts (like this one) to make the navigation friendlier.

    If I cannot manage to have the cake and eat it (good C# code reading in line and good printing form), I will make a Word version of that posts and share them on a SharePoint Document Library of one of our community SharePoint site.

    Good luck with the manyheaded beast ;-)

    Marc

    Fishman said...

    A Word doc would be awesome to use as a reference.

    Aashish said...

    Thank you for posting this. We are about to deploy MOSS 2007 with SQL 2008 Reporting Services in a 3 tier environment and your tutorial is serving to be an invaluable resource! Thank you so much once again.

    Marc Charmois said...

    Hi Aashish,

    You are welcome, I am happy if all this can help!

    As promissed to Fishman, I plan to post next week a .doc version of this post and share it.
    I will also add details on least privilege administration policy regarding the service account used for the Application Pool of the SharePoint Web Application on SHAREPOINT2007 computer.

    Good courage for your installation and configuration.

    Marc

    Aashish said...

    Hey Marc,

    I had a quick question for you:

    As previously mentioned we will be having a three prong Sharepoint/Reporting architecture:
    SQL 2008
    MOSS 2007 + Reporting Services Addin
    SQL Reporting Services

    We also have a few other servers:
    BI Star Schema, BI Analysis Services running their own SQL Servers...

    Do you have any insight as to how Reporting Services will tie in to the BI Star schema and Analysis services to create DataSources?

    Any info you may have or any links pertaining to the same would be greatly helpful!

    Thanks,
    Aashish

    Marc Charmois said...

    Hi Aashish,

    I have personally never used Analysis Services to build a Reporting Services reports, but what I know about reports, is that they need both a data source and a Query that are two distinct things. The interaction between the data source and the Query will product a DataSet, and the DataSet will be used to build the Report.

    The data source is just the link to Data, that is, for Reporting Services,
    a connection string plus,
    the data source type, plus
    the type of credentials, stored, windows, prompted etc., plus
    is the data source Shared or Custom.

    Here is a screenshot I have taken of the data source connection information page in SharePoint. Notice that the only information we have from SQL Server is a connection String.

    However, looking at Connecting to a Data Source (Reporting Services)article of MSDN I have noticed that an Analysis Services entity is a special type of data base for Reporting Services, but is referenced that way in the connection string:

    data source=localhost;initial catalog=Adventure Works DW

    Thus exactly as an SQL Server database. So, where do we specify the Analysis Services Type?

    When we first create a report, we are prompted to create a data source definition.

    We specify the specific type of data source, (in your case Analysis Services) by choosing this value from the list of supported data source types (drop down list of Types).


    Furthermore it seems there is an example of this in Adventure Works Samples. I don't know if they are using a Star Schema.

    What I suggest to you is to use the Wizard of BIDS to reference your SQL Server Analysis Services data source, then use the Query Designer to check what kind of tables are available and what kind of Queries you can do with it.

    As I did in that post.

    When you will close the Wizard, you will have your data source plus your dataset both based on Analysis Services.

    Regarding the way of building a significant Query against an Analysis Services type database based on a Star Schema, I think you should ask a BI specialist.

    I hope this can help.

    Marc

    Marc Charmois said...

    Hi Fisman,

    As promissed, please find at the top of the post a link toward a .doc version of this post.
    I had also added details on least privilege administration policy regarding the service account used for the Application Pool of the SharePoint Web Application on SHAREPOINT2007 computer.

    Have a good day.

    Marc

    Fishman said...

    Marc,

    Thanks for creating the Doc. I cannot seem to download it from Firefox or IE though.

    In IE I get an error trying to download from osyqiw.bay.livefilestore.com.. not able to open site.

    Marc Charmois said...

    Hi Fishman,

    I have just fixed it.
    Would you try again...

    Fishman said...

    It works now thank you!

    Marc Charmois said...

    Very clever SkyDrive, I think they change the link to the document every day so you cannot reference the document but have to reference the page where you can download the document.
    Doing that, people that wants to download the document have to display the page and see advertising!

    Aashish said...

    Thanks Marc for the downloadable document. I have a question relating to the hardware specs for a 3 server deployment:
    I have 2 servers that I can choose from for SQL 2008 Database Engine and the SSRS Server as follows:
    1. Quad Proc Quad Core (Xeon) with 32 GB RAM and 2. Dual Proc Quad core (Xeon) with 16 GB RAM.

    Which server should I use to host the Database Engine and which one for the Reporting Services?

    The 3rd Server (for MOSS) is identical to server 2 (Dual Proc, 16GB RAM). Thanks once again for all your help.

    -Aashish

    Vlad said...

    It's a great post. Thank you.

    I've built my own VM environment but installed everything on one VM. I have MOSS 2007 (Version: 12.0.0.6219) and SQL Server 2008 (10.0.1798.0).

    All works fine but there is only one issue I can't resolve.

    There are no "View Subscription", "Manage Data Sources", "Manage Parameters", "Manage Processing Options" and "View History" options in the Report contextual menu.
    It looks like insufficient permissions problem but I’m Local, SQL sa, Farm, Site Admin and also have “Full Control” on the report library.

    I posted this question on MS Forum sites but no answer.

    Your help would be much appreciated.

    Vlad

    Vlad R said...
    This comment has been removed by a blog administrator.
    Marc Charmois said...

    Hi Vlad,

    Thank you, I am happy if you liked it.
    Regarding your issue, I am quite sure it is just because

    Report Server Integration Feature

    is not activated on your Site Collection.
    Am I right?

    Marc Charmois said...

    By the way, Vlad, I have deleted the wrong comment. I thought you had just published it twice. I have just noticed that the second comment was made with your brand new Blogger account. I should have deleted the first one.

    Sorry.

    Marc

    Marc Charmois said...

    Hi Aashish

    I would definitively use the most powerfull computer for the Databases, since it will host the Report server and the SharePoint Databases I presume.
    And don't forget there is periodically synchronization jobs between the SharePoint and the Report Server databases.
    And users maybe will use Subscriptions that will give some more work to the SQL Server Agent.

    Except once, (but the SharePoint application had to make very complicated calculations), all the performances problems I have encountered was due to the Database Server.


    And you can check in the following Microsoft documentation that much more RAM is recommended for SQL Server:

    Microsoft Office server system requirements

    Last, if later, you want to increase the performances of your farm, it is always easier to add a new SharePoint Web front End or a new Report Server Computer by using the Scale Out deployment for Report Server than to migrate your Databases in a more powerfull server, isn't it?

    Aashish said...

    Hi Marc,

    Thank you! I will be installing everything into production tomorrow and will let you know how it all goes1

    Thank you very much for all your help.

    -Aashish

    Vlad R said...

    Marc,

    You are absolutly righ!!! The options became available as soon as I activated "Report Server Integration Feature" on my Site Collection.

    Thanks a lot!

    Vlad

    Marc Charmois said...

    You are welcome Vlad.
    I am happy if it was helpful...

    Marc

    Anonymous said...

    Marc,

    I think this is by far the best documentation on how to integrate MOSS and SSRS. I followed your document and was able to get everything up and running except when I try to deploy a SSRS report to my newly created sharepoint team site using BIDS, I get an error "The request failed with HTTP status 400: Bad request". When I upload the RDL file manually, it works but when I choose the "Manage Data Sources" contextual menu, I once again get the same error.

    I verified that my SSRS URL works and is correctly entered into the "Reporting Services" section in my MOSS settings. I also verified that the "Report Server Integration Feature" setting is activated in my Site Collection Features. I even tested my SSRS by switching back to "Native" mode and was able to confirm that my SSRS is working fine.

    The logs (both SSRS and MOSS) don't seem to tell me anything in terms of why I'm getting this error.

    Do you have any idea and/or suggestion?

    By the way, I'm testing the integration so both my MOSS and SSRS are running on the same machine. MOSS uses port 80 and SSRS uses port 8080.

    Thanks in advance!

    Ed

    Marc Charmois said...

    Ed,

    Thank you for your compliment.

    Regarding your issue, can you go to the IIS error log file and check it?
    (c:\WINDOWS\system32\LogFiles\HTTPERR)
    We will maybe able to get more information especially the type of the Bad request issue:
    (Invalid Hostname)?
    (invalid URL)?

    Here is an interesting article on the bad request types:

    HTTP 400-Cannot Resolve the Request (IIS 6.0)

    So, it seems that it is due to a malformed request.

    Just to be sure...

    You got the issue when trying to deploy the report in SharePoint using BIDS?
    The issue message has appeared in the Output pane of BIDS when trying to deploy?

    What exactly did you type in the BIDS Project Property Pages in the Deployment Section for:
    TargetDataSourceFolder
    TargetReportFolder
    TargetServerURL

    I have noticed issues if you don't type URL with the machine name.

    Last, you can deploy .rdl manually to SharePoint, but you cannot do the same with Datasource.
    There is a workaround to do it :

    Deploying Reports in Integrated Mode

    Hope this helps.

    Marc

    Anonymous said...

    Hi Marc,

    Good call on the IIS error log. It revealed exactly what my problem was -- somehow MOSS was trying to connect to my SSRS via secure connection (port 443) which was not valid. I removed the SSL setting in the SSRS configuration and everything worked like a charm. Once again thanks for your help!!

    Marc Charmois said...

    Ed,

    you are welcome.

    Very happy you have found the solution!

    Cheers.

    Marc

    Marc Charmois said...

    Hi Aashish,

    I have just published a post that shows how Reporting Services 2008 will tie in to Analysis Services 2008 to create DataSource.

    Create a Report from an Analysis Services 2008 Database and deploy it in SharePoint 2007

    Hope this helps.

    Marc

    Unknown said...

    I have to say, this is one of the best documentation i have come accross for SharePoint and Reporting Services integration. I have a question, I have a 2 WFE, 1Appserver, 1DB server configuration. SSRS2008 is installed on the appserver. I am able to create reportes and publish it. When i visit the site with the Machine name, the report showsup and life is good. When i use an Alias, the get error say 400 - unauthorized. any idea what do i do. I have AAM created and all that but no luck.

    Shri Kulkarni said...

    Marc,
    Great blog, thank you. I'm using MOSS 2007 and followed your blog until the beginning of "5.4 Step 4 - Check Report Server URL."

    I have three site collections and did activate the integration feature on all the three site collections but still cannot see the Reporting Services section? Any help will be greatly appreciated.

    Tom said...

    Hello, thank you for posting this!!
    How should one adjust your instructions for using just ONE backend SQL server with everything in it instead of two backend servers?? Combine the instructions for both SQL servers??

    Also, it seems that the SQL service account and all the WSS service accounts should have SPNs on both the WSS and SQL servers??

    Thank you, Tom

    Tom said...

    Would appreciate comments/information on how to set this up with Kerberos. Thank you, Tom

    Mark said...

    Nice article, very informative.
    The only difficulty I have had so far is that you have multiple steps with the same number in your articles. Once I realized that the ones I needed to go through were actually sub-steps (specifically when referring to "Integrating reporting Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 installation" ), everything made more sense.