Lesson 1: Configuring the SSRS Architecture and Instances

  • Article

Estimated lesson time: 45 minutes

As Chapter 1, Installing SQL Server 2005 Business Intelligence Tools, explained, SSRS can be installed with the default configuration and be immediately ready to use for report deployment and report access by users. However, if you installed a second instance of SSRS that needs to be configured separately, or if you need to set up an SSRS server farm, or if you need to configure SSRS for Internet deployment, your SSRS environment will require special configuration. This lesson focuses on these SSRS configuration scenarios.

Using the Report Server Configuration Manager Tool for Server Setup and Management

The installation of the SSRS server component includes the Reporting Services Configuration Manager tool, a user interface (UI) administration tool for configuring SSRS server settings. It can be found on the Start menu in All Programs\SQL Server 2005\Configuration Tool.

This tool performs the common setup and configuration tasks required to implement an SSRS instance, including:

  • Creating the Virtual Directories in IIS.
  • Configuring the Service Startup account.
  • Defining the ASP.NET account.
  • Setting up the database connection to the SSRS Repository.
  • Managing the symmetric encryption keys.
  • Performing initialization steps to enable new instances for a scale-out deployment, which is commonly called a Web farm.
  • Defining operational accounts for email and other administration tasks.

When you install SSRS as described in Chapter 1, and you choose not to apply the default configuration to SSRS during the installation, then you will need to use the Reporting Services Configuration Manager to set up the components and settings that are required for the SSRS instance to be enabled. Figure 2-1 shows the Reporting Services Configuration Manager tool as it will appear for an instance that was installed without the default configuration options.

Cc304416.Figure_C02623415_1(en-us,TechNet.10).png

Figure 2-1 The RSCM consolidates all configuration tasks needed to set up SSRS after installation.

If you are configuring a new instance of SSRS on a machine that has IIS installed, you can follow these steps to enable an instance for development and reporting:

  1. Create the Virtual Directories in IIS. In the Report Server Configuration Manager, navigate to the Report Server Virtual Directory property page on the left navigation pane. You then have the option to create a new Virtual Directory for the Report Server. Figure 2-2 shows the dialog box that appears when you select New in the Report Server Virtual Directory settings page.

    Cc304416.Figure_C02623415_2(en-us,TechNet.10).png

    Figure 2-2 You can define a new Virtual Directory in the Report Server Virtual Directory dialog box.

  2. The Web service that SSRS uses to perform report management, publishing, and rendering operations is located in the Report Server Virtual Directory. By default, the Virtual Directory is named ReportServer, but this can be changed if an instance that uses the Virtual Directory already exists or if your application requires a name that is directly related to the application.

  3. Create the Virtual Directory for the Report Manager, which is the Web-based management tool for Reporting Services to set up directories and manage security on the Web site. Configure this in the same way you configured ReportServer. The default name for the Virtual Directory for Report Manager is Reports. Using Report Manager is reviewed in Chapter 18, Managing and Securing SSRS Reports.

  4. The Windows Service Identity property page allows you to select a local or domain account/group to be used to run the Windows service. Likewise, the Web Service Identity property page allows you to select a local or domain account/group that is used to run the ASP.NET service account.

  5. Set up the SSRS repository databases, which are used by SSRS to store the report definitions, data sources, virtual folders for Report Manager, and security, and for temporary operations such as report caching. The databases can be created on the same local machine or on a remote machine. To create the databases, navigate to the Database Setup page, connect to the database instance (local) or a remote database instance, and then select New next to the Database name drop-down list. Figure 2-3 shows the new database entry screen, where you can set the Reporting Services database name and the credentials needed for database creation rights.

    Cc304416.Figure_C02623415_3(en-us,TechNet.10).png

    Figure 2-3 SSRS uses a SQL Server database to store report definitions, data sources, and security settings.

  6. By default, the repository database is named ReportServer. A temporary database is also created that uses the base name of the repository database appended with TempDB. So the default name of the temporary database is ReportServerTempDB.

  7. Initialize the instance. If the Initialization settings page is still marked as unconfigured (a red X appears next to Initialization in the navigation list), then the final step is to go to the Initialization Settings page, and click the Initialize button. Note that the Initialization page can also be used for scale-out deployment to add additional SSRS instances to a Web farm. See the section titled Using the Reporting Services Command-Line Tools to Manage SSRS later in this chapter to find out how the rskeymgmt.exe command-line tool is used to handle scale-out server management.

  8. Start the SSRS instance. To do this, navigate back to the Server Status settings page, and click the Start button to start the service. To verify that the instance is running correctly, open a Web browser and connect to the Report Manager at https://localhost/reports, or connect to the virtual directory you created for the Report Manager in Step 1.

Although your SSRS instance is now installed and started, it is a good idea to go one step further and back up your encryption key, which you will need if you have to recover the content of an SSRS installation. To back up the encryption key, navigate to the Encryption Key settings page in the Reporting Services Configuration Manager. Figure 2-4 shows the options within the tool.

Cc304416.Figure_C02623415_4(en-us,TechNet.10).png

Figure 2-4 The RSCM lets you back up and restore your encryption keys.

Your choices are to:

  • Back up the key to store it in a password protected file.
  • Restore a key in the case of rebuilding a standalone instance.
  • Change the key, which will re-encrypt the content with a newly generated encryption key.
  • Delete the encrypted content, which will remove the deployed content items from the Report Server database.

Managing the keys is also a function of the rskeymgmt.exe command-line tool. See the following section, Using the Reporting Services Command-Line Tools to Manage SSRS, to find out about encryption key management.

Two final options are available. First, for content delivery and notifications through email, you can go to the Email Settings property page to set an email address for the sender and to define an SMTP server through which the email will be routed. Second, you can define an execution account that has the appropriate security access to the data sources, local machine, or network resources to let SSRS perform operations such as unattended report execution. Set this account by using the Execution Account setting page and defining the domain account name and password. You can also set this through the rsconfig.exe command-line tool. See the following section, Using the Reporting Services Command-Line Tools to Manage SSRS, to find out about setting an execution account.

Using the Reporting Services Command-Line Tools to Manage SSRS

The installation of the SSRS server component includes three administration command-line tools to assist in configuring SSRS server settings. The rsconfig.exe tool assists in managing the SSRS instance connection to the repository database. The rskeymgmt.exe tool assists in the management of the encryption keys for operations such as backup, restore, and create. Finally, the rs.exe tool is a command-line tool assisting in the .NET scripting of report management operations.

Using rsconfig.exe

The rsconfig.exe tool manages the connection and settings for the SSRS instance, mainly to manage the repository database connection, but also to set up the default credentials for report execution against databases. The rsconfig.exe command parameters define the connection to the SSRS instance and then define the database connection to the Report Server database.

Use the parameters in Table 2-1 for the connection to the SSRS server and instance.

Table 2-1 rsconfig.exe SSRS Connection Parameters

Cc304416.table_C02623415_1(en-us,TechNet.10).png

If you are configuring the Report Server database connection, you will need to specify the connection details. Use the command-line parameters in Table 2-2 to set these.

Table 2-2 rsconfig.exe Database Connection Parameters

Cc304416.table_C02623415_2(en-us,TechNet.10).png

Finally, the rsconfig.exe tool can be used to define the credentials that are used for unattended report execution. An unattended report might be triggered by a Reporting Services event, such as a scheduled report. Use the following parameter with rsconfig.exe to define the account to be used.

Table 2-3 rsconfig.exe Unattended Account Parameter

Cc304416.table_C02623415_3(en-us,TechNet.10).png

Examples Using rsconfig.exe

The first example below uses the rsconfig.exe command statement to connect a locally installed SSRS instance to a local database called ReportServer using Windows Authentication:

rsconfig.exe /c /s (local) /d ReportServer /a Windows

In the next example, a locally installed SSRS instance is connected to a remote database server called ProdSQLSvr using the specified domain account Corporate\SSRSSvc:

rsconfig.exe /c /s ProdSQLSvr /d ReportServer /a Windows /u Corporate\SSRSSvc /p pass@word1

Note the difference between the preceding command-line example and the one that follows. In the next one, the /m switch is added, which specifies a remote SSRS instance. Also, the /a SQL parameter is added to specify that SQL authentication is used for the connection to the Report-Server database on ProdSQLServer:

rsconfig.exe /c /m ProdSSRSSvr /s ProdSQLSvr /d ReportServer /a SQL /u SSRS_Login /p pass@word1

Finally, this last example sets the account to be used for unattended report execution to a domain account called Guest and logs any errors to the SSRS trace:

rsconfig.exe /e /u Corporate/Guest /p pass@word1 /t

Using rskeymgmt.exe

SSRS includes a second command-line utility to assist in the management of the symmetric encryption keys that SSRS uses to secure and encrypt content in the Report Server database. This utility can perform common operations such as backup and restore, but it is also used to help in the management of SSRS instances that are part of scale-out deployments.

The parameters in Table 2-4 are used for general management of encryption keys such as backup, restore, and delete.

Table 2-4 rskeymgmt.exe Key Management Tasks

Cc304416.table_C02623415_4(en-us,TechNet.10).png

Examples Using rskeymgmt.exe for Standard Management Tasks

The following statement backs up the encryption key to a file named SSRS_Keys (no extension) with a password set:

rskeymgmt.exe /e /f c:\SSRS_Keys -p pass@word1

In the next example, the backed-up keys are restored to the local server with the named instance SSRSAdmin:

rskeymgmt.exe /a /f c:\SSRS_Keys /p pass@word1 /i SSRSAdmin

The final example deletes all the keys and encrypted content on the Report Server database the local instance is connected to:

rskeymgmt.exe /d

It is important to note that when you are performing any of the operations described above, you cannot run them against a remote server. They must be executed locally on the server. If, rather than the default, a named instance of SSRS exists, use the /i command-line parameter to specify the instance name.

You use the set of parameters in Table 2-5 to add and remove SSRS instances to help manage a scale-out SSRS deployment. These share the /i and /t parameters described in Table 2-4. The difference is that you can reference a remote SSRS instance that you want to add or remove from a scale-out deployment.

Table 2-5 rskeymgmt.exe Scale-Out Instance Management

Cc304416.table_C02623415_5(en-us,TechNet.10).png

Examples Using rskeymgmt.exe to Manage Scale-Out SSRS Installations

In the following example, the remote SSRS instance ProdSSRSSvr1 is joined to the scale-out implementation of SSRS shared by the local SSRS instance. The remote local administrator account is Corporate\SSRSSvc with the associated password:

rskeymgmt.exe \j \m ProdSSRSSvr1 \u Corporate\SSRSSvc \v pass@word1

The next example removes an instance of SSRS that is part of a scale-out deployment. The UID was acquired from the rsreportserver.config file:

rskeymgmt.exe -r {632e859c-5352-4712-a9e5-f28a0206a68f}

BEST PRACTICES Using Reporting Services Configuration Manager for scale-out deployment

Many of the functions that the command-line tools perform can be accomplished through the Reporting Services Configuration Manager. One of these functions is to manage a scale-out SSRS implementation and add or remove SSRS instances from the implementation. For more information, see the SQL Server 2005 Books Online (BOL) topic How to: Configure a Report Server Scale-Out Deployment (Reporting Services Configuration), ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en /rptsrvr9/html/b30d0308-4d9b-4f85-9f83-dece4dcb2775.htm.

Using rs.exe

The final command-line utility, rs.exe, lets you script SSRS report operations such as deployment and management and run scripts through the command line. The rs.exe tool references a Report Server Script (.rss) file, which contains Visual Basic .NET code based on the Web Service Description Language (WSDL) API. To see how to build an .rss file, read the SQL Server 2005 BOL topic Scripting with the rs Utility and the Web Service. The rs.exe tool works similarly to the way other SSRS command-line tools work, using the command-line parameters in Table 2-6.

Table 2-6 rs.exe Report Deployment and Management

Cc304416.table_C02623415_6(en-us,TechNet.10).png

In the example below, an RSscript.rss script is executed against a local instance of SSRS that was installed with the default ReportServer virtual directory for Report Server:

rs.exe /i RSscript.rss /s https://localhost/ReportServer

In the more complicated example below, a script is executed under the corporate\SSRSSvc account while passing the value ProdSQLSvr into the script for the variable named vDataSource:

rs.exe /i RSscript.rss /s https://localhost/ReportServer /v vDataSource="ProdSQLSvr" /u Corporate\SSRSSvc /p pass@word1

Configuring the Report Server for SSL Communication and Internet Deployment

You might need to enforce secure connections to SSRS, or you might want to place the SSRS virtual directories on the Web for Web deployment and reporting. These options are available with only a few setup requirements.

Secure Certificate-Based Communication

Report management can be deployed and rendered to leverage port 80 HTTP connections, but it can also be set up to require HTTPS Secure Sockets Layer (SSL) connections or a combination of connection types. SSRS allows four security levels for communication to the Report Server. To set SSRS to require SSL connections, you need to change the RSReportServer.config file, located in the Program Files\Microsoft SQL Server\MSSQL.#\Reporting Services \ReportServer folder. The # represents the SQL Server instance for the Reporting Services instance.

Open the RSReportServer.config file and locate the SecureConnectionLevel property, which will look like this:

<Add Key="SecureConnectionLevel" Value="0"/>

This property can be configured with four different values, from 0 to 3, that define the level of connection security that is required, as Table 2-7 shows.

Table 2-7 SecureConnectionLevel Values

Cc304416.table_C02623415_7(en-us,TechNet.10).png

In addition to setting this property, you can define the IIS authentication method, such as Basic Authentication, Windows Authentication, or Anonymous access, for the Virtual Directory. Windows Authentication is recommended for security, but Basic Authentication with SSL will provide secure connections from machines that are not logged on to your corporate domain.

Configuring SSRS for Internet Deployment

When you need to make the SSRS instance accessible on the Web for Internet deployment and rendering, certain settings will prevent unnecessary security risks. Take these considerations into account when setting up an SSRS instance that will be accessible from the Internet:

  • Your Report Server database should always remain behind the firewall.
  • You can install Report Manager on a remote Internet-facing server separate from Report Server if you need access to the Report Manager only. This will require a separate license for SQL Server 2005, and you will not have Report Builder capabilities or report drill-through capabilities (for example, to Excel, Web archive, and HTML3.2 formats).
  • Report Server and Report Manager can be installed together on an Internet-facing machine in order to connect to the Report Server virtual directory for deployment. When deploying Report Server and Report Manager over the Web to an SSRS instance, be sure to put the fully qualified domain name in the address.

For more about deploying SSRS on the Internet, see the SQL Server 2005 BOL topic Internet Deployment Considerations, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/b7feb272-ffcb-4e14-ab58-3212f3b0ac74.htm.

Practice: Managing SSRS Encryption Keys

In this practice, you use rskeymgmt.exe to back up and restore an encryption key to experience the importance of having a backup copy of the symmetric encryption key. The practice exercise assumes the default SSRS instance was installed on your local machine and was configured to use the default settings during installation.

Exercise: Back Up and Restore an Encryption Key

  1. Open a new browser and navigate to the Report Manager Web interface by connecting to the Reports virtual directory of your SSRS instance https://localhost/Reports.

  2. Click the New Folder button in the Reporting Services Home directory, and then create a folder named Test Encryption.

  3. Click the New Data Source button, and then create a New Data Source called Test Data Source. In the Connection String text box, enter:

    Provider=SQLNCLI.1;Data Source=(local);Integrated Security=SSPI;Initial Catalog=AdventureWorks

  4. Keep the connection defaults as they are and click OK to save the changes.

  5. Open a new command prompt window by typing CMD in the Start, Run window.

  6. Back up the current SSRS encryption key by running the following command:

    rskeymgmt.exe /e /f c:\SSRS_Key_OLD -p pass@word1

  7. When prompted, confirm that you want to back up the encryption key by typing y.

  8. Next, to generate a brand-new key and encrypt the existing SSRS content with the new key, run the following command-line statement:

    rskeymgmt.exe

    /s

  9. When prompted, confirm that you want to grant the Report Server access to the newly generated key by typing y.

  10. In the Report Manager browser, press F5 to refresh the window and confirm that you still have access to the content. Then select the Test Data Source link to open the Data Source properties.

  11. Back in the command window, run the following statement to back up the new key:

    rskeymgmt.exe /e /f c:\SSRS_Key_NEW -p pass@word1

  12. When prompted, confirm that you want to back up the key.

  13. Next, you will restore the original encryption key to your SSRS instance. Run the following command:

    rskeymgmt.exe /a /f c:\SSRS_Key_OLD -p pass@word1

  14. The Report Manager browser should still have the Test Data Source properties screen open. If it is, press F5 to refresh the content; if it is not, browse to the Test Data Source that you created in step 3. At this point, you will receive the error, The report server is unable to access encrypted data. Apply a back-up key or delete all encrypted content.

  15. This is because the existing content was encrypted with a new key, but the old key was restored. If you did not have the new encryption key, you would not be able to access the encrypted content, and it would have to be deleted.

  16. Restore the new encryption key to enable the existing content to be unencrypted by the SSRS instance; run the following command:

    rskeymgmt.exe /a /f c:\SSRS_Key_NEW -p pass@word1

  17. Navigate back to the Test Data Source properties in the Report Manager browser, or if you are already there, press F5 to refresh the content and confirm that the properties are viewable and modifiable.

Quick Check

  1. Which of the tools reviewed in Lesson 1 can change or create the Virtual Directories in IIS for Report Server and Report Manager after installation?
  2. What are the three SSRS command-line tools and their primary functions?

Quick Check Answers

  1. Only the Reporting Services Configuration Manager can create new Virtual Directories in IIS for the Report Server and Report Manager.
  2. rsconfig.exe is used to define the connection properties from the SSRS instance to the Report Server database; rskeymgmt.exe performs encryption key operations and scale-out deployment setup; rs.exe runs Report Server Script files that can perform report deployment and management operations.

< Back      Next >

 

 

© Microsoft. All Rights Reserved.