Edit

Share via

Configure a report server database connection (Report Server Configuration Manager)

  • Article

Applies to: SQL Server 2016 (13.x) Reporting Services and later  Power BI Report Server

For content related to previous versions of SQL Server Reporting Services (SSRS), see What is SQL Server Reporting Services?

In this article, you learn about the SQL Server report server database connection and essential information about how it works. This information includes how to configure the connection and what considerations to keep in mind during configuration.

Each report server instance requires a connection to the report server database that stores the reports, shared data sources, resources, and metadata managed by the server. You can create the initial connection during a report server installation if you install the default configuration. In most cases, use the Reporting Services Configuration tool to configure the connection after the setup is complete. Modify the connection at any time to change the account type or reset credentials. For more information on how to create the database and configure the connection, see Create a Native mode report server database (Report Server Configuration Manager).

When to configure a report server database connection

Configure a report server database connection in the following circumstances:

How Reporting Services connects to the database engine

Report server access to a report server database depends on:

  • Credentials and connection information: Specified for the report server database and used exclusively by the report server.
  • Encryption keys: Necessary for storing and retrieving sensitive data. Created automatically when you configure the database for the first time. After you create the keys, you must update them if you change the Report Server service identity. For more information about working with encryption keys, see Configure and manage encryption keys (Report Server Configuration Manager).

The report server database is an internal component, accessed only by the report server. The credentials and connection information you specify for the report server database are used exclusively by the report server. Users who request reports don't require databases permissions or a database sign-in for the report server database.

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

You can verify that the instance accepts remote connections by opening SQL Server Configuration Manager and confirming that the TCP/IP protocol is enabled for each service. Enabling remote connections also activates the necessary client and server protocols. To confirm that these protocols are enabled, open SQL Server Configuration Manager, go to SQL Server Network Configuration in the left pane, and choose Protocols for MSSQLSERVER. For more information, see Enable or disable a server network protocol in SQL Server.

Define a report server database connection

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

  • Database Engine instance name: The name of the Database Engine instance hosting the report server database.
  • Report server database name: When creating a connection for the first time, you can create a new report server database or select an existing database. For more information, see Create a report server database, Report Server Configuration Manager.
  • Credential type: You can use the service accounts, a Windows domain account, or SQL Server database sign-in credentials.
  • User name and password: Required only if you're using Windows domain account or SQL Server sign-in credentials.

The credentials you provide must be granted access to the report server database. If you use the Reporting Services Configuration tool, this step occurs automatically. For more information about the permissions required to access the database, see the How Reporting Services connects to the database engine section in this article.

Store database connection information

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

Not all values are set for every connection type. If you configure the connection by using the default values for the service accounts to make the connection, <LogonUser>, <LogonDomain>, and <LogonCred> are 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 sign-in credentials and you then change the account or sign-in credentials, remember to update the stored values.

Choose a credential type

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

  • Windows integrated security with the Report Server service account: Because the report server is implemented as a single service, only the account under which the service runs requires database access.
  • 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, use a domain account.
  • SQL Server sign-in credentials: Use SQL Server sign-in credentials to authenticate and connect to the report server database. This option is useful when the database server is in a different domain or when you use workgroup security instead of domain security.

Note

You can't use a custom authentication extension to connect to a report server database. Custom authentication extensions are used only to authenticate a principal to a report server. They don't affect connections to the report server database or to external data sources that provide content to reports.

Note

When you use Azure SQL Managed Instance to host report server databases, SQL Server authentication is the only supported credential type. In addition, Managed Instance can't host report server instance.

Use Windows integrated security with the Report Server service account

You can use Windows integrated security to connect through the Report Server service account. The account is granted sign-in rights to the report server database. This credential type is the default chosen by Setup if you install Reporting Services in the default configuration.

The service account is a trusted account that provides a low-maintenance approach to managing a report server database connection. Because the service account uses Windows integrated security to make the connection, the credentials don't have to be stored. However, if you then change the service account password or identity, be sure to use the Reporting Services Configuration tool to make the change. The tool automatically updates the database permissions to use the revised account information. For more information, see Configure the report server service account (Report Server Configuration Manager).

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

If you configure the instance of the Database Engine for Windows Authentication, ensure the instance is in the same domain or a trusted domain with the report server computer. You can then configure the connection to use the service account. Alternatively, you can use a domain user account that you manage as a connection property through the Reporting Services Configuration tool. If the database server is in a different domain, or if you use workgroup security, configure the connection to use SQL Server database sign-in credentials. In this case, be sure to encrypt the connection.

Use a Windows user account

You can specify a Windows user account for the report server connection to the report server database. If you use a local or domain account, 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.

Use SQL Server sign-in credentials

You can specify one set of SQL Server sign-in credentials 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 help secure the transmission of data between the servers. If you use database sign-in credentials, 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:

Database Role
ReportServer public
RSExecRole
master
msdb
ReportServerTempDB		 RSExecRole

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 specify a different account for the connection, update the SQL Server sign-in credentials for that new account. You can create script files in the Reporting Services Configuration tool that update the SQL Server sign-in credentials for the report server.

Verify the database name

Use the Reporting Services Configuration tool to determine 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.

Use a different report server database or move 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 Upgrade and migrate Reporting Services in SQL Server.

Configure multiple report servers to use the same report server database

You can configure multiple report servers to use the same report server database. This deployment configuration is called a scale-out deployment. This configuration is a prerequisite if you want to run multiple report servers in a server cluster. However, you can also use this configuration if you want to segment service applications. You can use it to test the installation and settings of a new report server instance to compare it with an existing report server installation. For more information, see Configure a Native mode report server scale-out deployment.

Related content

More questions? Try asking the Reporting Services forum.