Configuring a Report Server Database Connection

Each report server instance requires a connection to the report server database that stores reports, report models, shared data sources, resources, and metadata managed by the server. The initial connection can be created during a report server installation if you are installing the default configuration. In most cases, you will use the Reporting Services Configuration tool to configure the connection after Setup is complete. You can modify the connection at any time to change the account type or reset credentials. For step-by-step instructions on how to create the database and configure the connection, see How to: Create a Report Server Database (Reporting Services Configuration).

You must configure a report server database connection in the following circumstances:

• Configuring a report server for first use.
• Configuring a report server to use a different report server database.
• Changing the user account or password that is used for the database connection. You only need to update the database connection when the account information is stored in the RSReportServer.config file. If you are using service accounts for the connection (which use Windows integrated security as the credential type), the password is not stored, eliminating the need to update the connection information. For more information about changing accounts, see Configuring Service Accounts and Passwords in Reporting Services.
• Configuring a report server scale-out deployment. Configuring a scale-out deployment requires that you create multiple connections to a report server database. For more information about how to perform this multi-step operation, see How to: Configure a Report Server Scale-Out Deployment (Reporting Services Configuration).

How Reporting Services Connects to the Database Engine

Report server access to a report server database depends on credentials and connection information, and on encryption keys that are valid for the report server instance that uses that database. Having valid encryption keys is necessary for storing and retrieving sensitive data. Encryption keys are created automatically when you configure the database for the first time. After the keys are created, you must update them if you change the Report Server Windows service identity. For more information about working with encryption keys, see Managing Encryption Keys.

The report server database is an internal component; it is accessed only by the report server. The credentials and connection information that you specify for the report server database are used exclusively by the report server. Users who request reports do not require databases permissions or a database login for the report server database.

Reporting Services uses System.Data.SqlClient to connect to the instance of Database Engine that hosts the report server database. If you are using a local instance of the Database Engine instance, the report server will establish the connection using shared memory. If you are using a remote database server for the report server database, you might have to enable remote connections depending on the edition you are using. If you are using the Developer Edition, remote connections are turned off by default. If you are using the Enterprise Edition, remote connections are enabled for TCP/IP by default.

To verify that the instance accepts remote connections, click Start, click All Programs, click Microsoft SQL Server 2005, click Configuration Tools, click SQL Server Surface Area Configuration, and then click Surface Area Configuration for Services and Connection. For more information, see Surface Area Configuration for Services and Connections (Remote Connections) - Database Engine.

When you enable remote connections, the client and server protocols will also be enabled. To verify the protocols are enabled, click Start, click All Programs, click Microsoft SQL Server 2005, click Configuration Tools, click SQL Server Configuration Manager, click SQL Server Network Configuration, and then click Protocols for MSSQLSERVER. For more information, see How to: Enable or Disable a Server Network Protocol (SQL Server Configuration Manager).

Defining a Report Server Database Connection

To configure the connection, you must use the Reporting Services Configuration tool or the rsconfig command line utility. A report server requires the following connection information:

• Name of the SQL Server instance hosting the report server database. You can use a local or remote SQL Server 2000 or SQL Server 2005 Database Engine instance. If you are using SQL Server 2005 Express with Advanced Services, you must choose the local Database Engine instance.
• Name of the report server database. When creating a connection for the first time, you can create a new report server database or choose an existing database. For more information, see Creating a Report Server Database.
• Credential type. You can use the service accounts, a Windows domain account, or a SQL Server database login.
• User name and password (required only if you are using Windows domain account or a SQL Server login).

The credentials that you provide must be granted access to the report server database. If you use the Reporting Services Configuration tool, this step is performed automatically. For more information about the permissions required to access the database, see the "Database Permissions" section in this topic.

Storing Database Connection Information

Reporting Services stores and encrypts the connection information in the following RSreportserver.config settings. You must use the Reporting Services Configuration tool or rsconfig utility to create encrypted values for these settings.

Not all of the values are set for every type of connection. If you configure the connection using the default values (that is, using the service accounts to make the connection), <LogonUser>, <LogonDomain>, and <LogonCred> will be empty, as follows:

<Dsn></Dsn>
<ConnectionType></ConnectionType>
<LogonUser></LogonUser>
<LogonDomain></LogonDomain>
<LogonCred></LogonCred>

If you configure the connection to use a specific Windows account or database login, you must remember to update the values that are stored if you subsequently change the account or login.

Choosing a Credential Type

There are three types of credentials that can be used in a connection to a report server database:

• Windows integrated security using the Report Server Web service and Windows service accounts.
• A Windows user account. If the report server and the report server database are installed on the same computer, you can use a local account. Otherwise, you must use a domain account.
• A SQL Server login.

Using Service Accounts and Integrated Security

You can use Windows integrated security to connect through the Report Server Web service and Windows service accounts. Both service accounts are granted login rights to the report server database. This is the default credential type chosen by Setup if you install Reporting Services in the default configuration.

Service accounts are trusted accounts that provide a low-maintenance approach to managing a report server database connection. Because service accounts use Windows integrated security to make the connection, the credentials do not have to be stored. If you subsequently change the service account password or identity (for example, switching from a built-in account to a domain account), you must update the access rights for the account on the SQL Server instance that hosts the report server database. The Reporting Services Configuration tool will perform these steps for you. For more information, see Configuring Service Accounts and Passwords in Reporting Services.

If you configure the database connection to use the service accounts, the accounts must have network permissions if the report server database is on a remote computer. Do not use the service account if the report server database is on a different domain, behind a firewall, or if you are using workgroup security instead of domain security. Use a SQL Server database user account instead.

Using a Domain User Account

You can specify that a single account is used to connect to the report server database. Both the Report Server Web service and Windows service will use this account to connect. If you use a local or domain account, you must update the report server database connection every time you change the password or the account. Always use the Reporting Services Configuration tool to update the connection.

Using a SQL Server Login

You can specify a single SQL Server login to connect to the report server database. If you use SQL Server authentication and the report server database is on a remote computer, use IPSEC to secure the transmission of data between the servers. If you use a SQL Server login, both the Report Server Web service and Windows service will use this account to connect. If you use a database login, you must update the report server database connection every time you change the password or the account.

Database Permissions

Accounts used to connect to the report server database are granted the following roles:

• public and RSExecRole roles for the ReportServer database.
• RSExecRole role for the master, msdb, and ReportServerTempDB databases.

When you use the Reporting Services Configuration tool to create or modify the connection, these permissions are granted automatically. If you use the rsconfig utility, and you are specifying a different account for the connection, you must update the SQL Server login for that new account. You can create script files in the Reporting Services Configuration tool that will update the SQL Server login for the report server.

Verifying the Database Name

Use the Reporting Services Configuration tool to find out which report server database is used by a particular report server instance. To find the name, connect to the report server instance and open the Database Setup page.

Using a Different Report Server Database or Moving a Report Server Database

You can configure a report server instance to use a different report server database by changing the connection information. A common case for switching databases is when you deploy a production report server. Switching from a test report server database to a production report server database is typically how production servers are rolled out. You can also move a report server database to another computer. For more information, see Moving a Report Server Database to Another Computer and Migrating Reporting Services.

Configuring Multiple Reports Servers to Use the Same Report Server Database

You can configure multiple report servers to use the same report server database. Typically, this deployment configuration is considered a scale-out deployment model that you implement when you want to run multiple report servers in a server cluster. However, you can also use this configuration to test the installation and settings of a new report server instance to compare it against an existing report server that works the way you want it to. For more information, see Configuring a Report Server Scale-Out Deployment.

Change History

See Also

Concepts

Creating a Report Server Database
Reporting Services Configuration How-to Topics
Configuring Service Accounts and Passwords in Reporting Services
Connecting to a Data Source
Configuring Reporting Services Components
RSReportServer Configuration File

Other Resources

rsconfig Utility

Help and Information

Getting SQL Server 2005 Assistance