How to: Redirect Reporting Services to Connect to a Different Data-Tier Server

  • Article

You must redirect SQL Server Reporting Services to connect to a different data-tier server whenever you move, rename, or fail over a data-tier server for Team Foundation. 

To redirect Reporting Services to connect to a different data-tier server, you perform the following sequence of steps:

  1. Verify That Services for SQL Server Are Running

  2. Change the Database Connection for Reporting Services

  3. Update the Identity of the Service Account for Reporting Services

  4. (Optional) Redirect the Data Source for the Team System Cube

Required Permissions

To complete these procedures, you must be a member of the Administrators group on the old and new servers and a member of the Team Foundation Administrators group.

In addition to these permissions, you might need to address the following requirements on a computer that is running Windows Server 2008 or Windows Vista:

  • To follow a command-line procedure, you might need to open an elevated Command Prompt by clicking Start, right-clicking Command Prompt, and clicking Run as Administrator.

  • To follow a procedure that requires Internet Explorer, you might need to start it as an administrator by clicking Start, clicking All Programs, right-clicking Internet Explorer, and then clicking Run as administrator.

  • To edit web.config files, you might need to start the text editor as an administrator by clicking Start, clicking All Programs, right-clicking the editor, and then clicking Run as administrator.

  • To access Report Manager, reports, or Web sites for SQL Server Reporting Services, you might need to add these sites to the list of trusted sites in Internet Explorer or start Internet Explorer as an administrator.

For more information, see the Microsoft Web site.

Verify That Services for SQL Server Are Running

To verify that services for SQL Server are running

  • Log on to the appropriate server, open Computer Manager, and verify that the services in the following table are running.

    Server that hosts this program

    Service

    SQL Server Analysis Services

    • SQL Server Analysis Services (MSSQLSERVER or TFSInstance)

    Team Foundation databases

    • SQL Server (MSSQLSERVER or TFSInstance)

    • SQL Server Agent (MSSQLSERVER or TFSInstance)

    For more information about how to verify the status of these services, see How to: Stop and Start Services, Application Pools, and Web Sites.

Change the Database Connection for Reporting Services

To start Reporting Services and change the database connection in SQL Server 2008

  1. On the server that is running Reporting Services, click Start, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click Reporting Services Configuration Manager.

  2. In the Reporting Services Configuration Connection dialog box, make sure that the name of the current data-tier server appears and that the instance name is MSSQLSERVER, and then click Connect.

    Note

    If you used a named instance for Team Foundation Server, you must specify the instance name and not the default instance of MSSQLSERVER.

  3. In the Report Server Status pane, click Start.

  4. In the Explorer pane, click Database.

  5. In the Report Server Database pane, click Change Database.

    The Report Server Database Configuration Wizard opens.

  6. Click Choose an existing report server database, and then click Next.

  7. In Server Name, type the name of the new data-tier server, click Test Connection, and then click Next.

  8. In the Report Server Database list, click ReportServer$TFSInstance, and then click Next.

  9. In the Authentication Type list, click Service Credentials, and then click Next.

  10. On the Summary page, verify that the information is correct, and then click Next.

  11. Click Finish to close the wizard.

To start Reporting Services and change the database connection in SQL Server 2005

  1. On the server that is running Reporting Services, click Start, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click Reporting Services Configuration (in Windows Server 2003) or Reporting Services Configuration Manager (in Windows Server 2008).

  2. In the Report Server Installation Instance Selection dialog box, make sure that the name of the current data-tier server appears and that the instance name is MSSQLSERVER, and then click Connect.

    Note

    If you used a named instance for Team Foundation Server, you must specify the instance name and not the default instance of MSSQLSERVER.

  3. In the Explorer pane, click Server Status.

  4. In the Report Server Status pane, click Start.

  5. In the Explorer pane, click Database Setup.

  6. In Server Name, type the name of the new data-tier server, and then click Connect.

    The SQL Server Connection dialog box opens.

  7. In Database Name, type ReportServer, and click OK.

    Note

    If you used a named instance, this database will be named ReportServer$InstanceName.

  8. In the Database Connection pane, click Apply.

  9. In the SQL Server Connection Dialog dialog box, click OK.

Update the Identity of the Service Account for Reporting Services

You must update the identity of the service account for Reporting Services whenever you move, rename, or fail over the data-tier server.

When you modify that identity, each step is logged in the tasks panel at the bottom of the page. The Report Server service will be stopped and restarted during the account update. Errors might occur such as logon failures if you mistype an account or password.

Important noteImportant Note:

You should always use Reporting Services Configuration Manager to update the service account so that other settings that depend on the identity can be updated at the same time.

To update the identity in SQL Server 2008

  1. In the Explorer pane of the Reporting Services Configuration tool, click Service Account.

  2. In the Built-in Service Account list, click Local Service, click Network Service, and then click Apply.

  3. When you are prompted to back up the symmetric key, type a file name and location for the backup.

  4. Type a password to lock and unlock the file, and then click OK.

    If the report server uses the service account to connect to the report server database, the connection information will be updated to use the new account or password. If you update the connection information, you must connect to the database.

  5. In the SQL Server Database Connection dialog box, type credentials for an account that has permission to connect to the database, and then click OK.

  6. When you are prompted to restore the symmetric key, type the password that you specified in step 3, and then click OK.

  7. In the Results pane, review the status messages to verify that all tasks were completed successfully.

    "Restoring Encryption Key" appears last in the list of tasks.

    Important noteImportant Note:

    In the unlikely event that the symmetric key is not restored, you can perform this step yourself by opening the Encryption Keys page and clicking Restore. To verify that the account has been reset, you open a report that uses stored credentials. If you cannot restore the key, you must delete the encrypted content. For more information, see "Deleting and Re-creating Encryption Keys" and "Backing Up and Restoring Encryption Keys" in the Setup Help for SQL Server.

  8. Close Reporting Services Configuration Manager.

To update the identity in SQL Server 2005

  1. In the Explorer pane of Reporting Services Configuration Manager, click Windows Service Identity.

  2. In the Built-in Service Account list, click Local Service, click Network Service, and then click Apply.

    You are prompted to back up the symmetric key.

  3. In Password and Key File, type a password and a file name for the backup, and click OK.

  4. In the SQL Server Database Connection dialog box, type credentials for an account that has permission to connect to the database, and then click OK.

  5. When you are prompted to restore the symmetric key, type the password that you specified in step 3, and then click OK.

  6. In the Results pane, review the status messages to verify that all tasks were completed successfully.

    "Restoring Encryption Key" appears last in the list of tasks.

    Important noteImportant Note:

    In the unlikely event that the symmetric key is not restored, you can perform this step yourself by opening the Encryption Keys page and clicking Restore. To verify that the account has been reset, you open a report that uses stored credentials. If you cannot restore the key, you must delete the encrypted content. For more information, see "Deleting and Re-creating Encryption Keys" and "Backing Up and Restoring Encryption Keys" in the Setup Help for SQL Server.

  7. Close Reporting Services Configuration Manager.

Redirect the Data Source for the Team System Cube

If you have moved the Analysis Services database of the data warehouse for Team System to a different data-tier server, you must redirect the data source for the Team System cube.

Note

This step is required only if you have performed the procedures in How to: Move the Analysis Services Database to a Separate Server.

To redirect the data source for the Team System cube

  1. Log on to the server that hosts Reporting Services.

  2. (SQL Server 2005 only) Open Computer Manager, and start the ReportServer or ReportServer$InstanceName application pool.

  3. Open Internet Explorer, type the following string in the Address bar, and press ENTER:

    https://localhost/Reports

    If you have deployed a named instance on the data-tier server, type the following string instead:

    **https://localhost/Reports_**TFSInstance

  4. Click TfsOlapReportsDS.

  5. In the Connection string box, update the Data source argument with the name of the server that hosts SQL Server Analysis Services. For example, type the following string:

    Data source=NameOfAnalysisServicesDataTierServer\TFSInstance

  6. Click Credentials stored securely in the Report Server.

  7. Update the user name and password to accurately reflect the service account for Reporting Services, and then click Apply.

See Also

Tasks

Troubleshooting Team Foundation Reporting

How to: Move the Analysis Services Database to a Separate Server

How to: Restore Data for Team Foundation

How to: Fail Over to a Mirrored Data-Tier Server

How to: Move from a Single-Server to a Dual-Server Deployment

Other Resources

Managing SQL Server Services