Changing SQL Reporting Services from Native mode to Integrated mode in a BI test environment


Applies to: SharePoint Server 2010 Enterprise

Topic Last Modified: 2012-02-06

In our baseline configuration, SQL Server Reporting Services is installed in native mode. To use any of the Reporting Services features on SharePoint such as PowerPivot, Access Services, or Report Builder 3.0, you must change your configuration of SQL Server Reporting Services to SharePoint integrated mode. This article describes the steps that are required to switch to integrated mode.

To use a reporting server in the SharePoint farm, you first have to install SharePoint Server components on the Contoso-SQL computer.

You must have an internet connection to perform this procedure. Refer to Create a baseline environment for a BI test environment for instructions.
To install SharePoint Server 2010 prerequisites
  1. Log on to the Contoso-SQL virtual machine with the Contoso\SQLAdmin account.

  2. On the SharePoint Server 2010 DVD, run default.hta.

  3. On the SharePoint Server 2010 opening page, click Install software prerequisites.

  4. Follow the wizard to complete installation of all prerequisites.

    Depending on your configuration, you may have to restart your computer during this process.
  5. When the wizard has finished, click Finish.

Once the prerequisites are installed, you can install SharePoint Server 2010 itself. Perform the following procedure to install SharePoint Server 2010 on Contoso-AppSrv.

To install SharePoint Server 2010
  1. On the SharePoint Server 2010 DVD, run default.hta.

  2. On the SharePoint Server 2010 opening page, click Install SharePoint Server.

  3. On the Enter your Product Key page, type your product key, and then click Continue.

  4. On the Read the Microsoft Software License Terms page, read the license agreement and accept the terms by selecting the I accept the terms of this agreement check box.

  5. Click Continue.

  6. On the Choose the installation you want page, click Server Farm.

  7. On the Server Type page, select the Complete – Install all components option.

  8. Click Install Now.

  9. On the Run Configuration Wizard page, leave the Run the SharePoint Products and Technologies Configuration Wizard now check box selected, and then click Close.

  10. On the Welcome to SharePoint Products page, click Next.

  11. On the warning dialog box, click Yes.

  12. On the Connect to a server farm page, select the Connect to an existing server farm option, and then click Next.

  13. On the Specify Configuration Database Settings page:

    1. In the Database server box, type the name of the database server (Contoso-SQL).

    2. In the Database name text box, keep the default database name of SharePoint_Config.

    3. Type the user name and password of the database access account (Contoso\SQLAdmin).

    4. Click Next.

      If the wizard cannot connect to Contoso-SQL, check the firewall settings in Windows Server.
    5. On the Completing the SharePoint Products and Technologies Configuration Wizard page, click Next.

    6. Select Do not use this machine to host the web site. Click OK.

    7. On the Configuration Successful page, click Finish.

To configure SSRS in SharePoint integrated mode
  1. Log on to Contoso-SQL as Contoso\SQLAdmin.

  2. Click Start, select All Programs, Microsoft SQL Server 2008 R2, and Configuration Tools, and then click Reporting Services Configuration Manager.

  3. In the baseline line configuration, SQL Server Reporting Services is installed on the default instance. The server name CONTOSO-SQL and the report server instance, MSSQLSERVER, are shown as the defaults. Click Connect.

  4. On the report server status page, notice that the current Report Server mode is Native.

  5. From the left navigation pane, click Database.

  6. Click Change Database.

  7. Select Create a new report server database, and then click Next.

  8. Accept defaults for server name and credentials. Click Next.

  9. In the Database Name box type BILabReportServer and change Report Server Mode to SharePoint integrated mode. Click Next.

  10. Accept the default credentials and then click Next.

  11. Verify the summary, and make sure that Report Server Mode is set to SharePoint integrated mode. Click Next.

  12. Click Finish.

  13. In the left navigation pane of Reporting Services Configuration Manager, click Web service URL, and then click the URL link to open it.

    You will receive an error because you must still configure Reporting Services integration in SharePoint Server.

Finish the configuration of integrating Reporting Services with SharePoint Server 2010 by completing the following steps:

To configure SharePoint Server 2010 for Reporting Services integration
  1. Log on to Contoso-AppSrv as Contoso\FarmAdmin.

  2. Open SharePoint Central Administration.

  3. On the left navigation pane, click General Application Settings. In the center page in the Reporting Services section, click Reporting Services Integration.

  4. In the Report Server Web Service URL field, type http://CONTOSO-SQL/REPORTSERVER.

  5. In the Authentication Mode section, select Trusted Account.

  6. In the Credentials box, enter the user name CONTOSO\SQLAdmin and the password.

  7. In the Activate the reporting services feature section, select Activate feature in all existing site collections.

  8. Click OK.