Redirect Reporting Services to Connect to a Different Server

  • Article

If your deployment of Visual Studio Team Foundation Server uses SQL Server Reporting Services, you might need to redirect Reporting Services to the location of its databases. Specifically, you must redirect Reporting Services whenever you move, rename, or restore the databases that it uses to a new location. 

Note

You can edit the settings for a server that is running Reporting Services from the administration console for Team Foundation. For more information, see Add a Report Server to Your Deployment.

To redirect Reporting Services to connect to a different location, 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 to the Database for Analysis Services

Required Permissions

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

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 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)

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 server and the instance are both correct, and then click Connect.

  3. In the Report Server Status pane, click Start if the server is not already started.

  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 server where you restored the databases, click Test Connection, and then click Next.

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

    Note

    If you used a named instance, the name of the database will be ReportServer$TFSInstanceName.

  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.

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 restore the databases to a different 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

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. Under Report Server Service Account, click Use built-in account, 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

    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.

Redirect the Data Source to the Database for Analysis Services

If you have moved the Analysis Services database of the data warehouse for Team Foundation to a different server, you must redirect the data source to use that database.

To redirect the data source for the warehouse

  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

Concepts

SQL Server and Team Foundation Server

Other Resources

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