How to: Create a Report Server Database (Reporting Services Configuration)

Reporting Services uses a SQL Server database for internal storage. The database is required and it is used to store published reports, models, shared data sources, session data, resources, and server metadata.

To create a report server database or to change the connection string or credentials, use the options in the Database page in the Reporting Services Configuration Manager.

When to Create or Configure the Report Server Databases

You must create and configure the report server database if you installed the report server in files-only mode.

If you installed Reporting Services in either the default configuration for native mode or SharePoint integrated mode, the report server database was created and configured automatically when the report server instance was installed. You can use the Reporting Services Configuration Manager to view or modify the settings that Setup configured for you.

Before You Start

Creating or configuring a report server database is a multi-step process. Before you create the report server database, consider how you want to specify the following items:

  • Select a database server
    Reporting Services requires a SQL Server 2005, 2008, or 2008 R2 Database Engine instance to host the report server database. You can use a local or remote server.

  • Check edition requirements
    Verify that the edition of the Database Engine is compatible with the report server edition you are using. For more information about edition compatibility, see Creating a Report Server Database.

  • Enable TCP/IP connections
    Enable TCP/IP connections for the Database Engine. Some editions of the Database Engine do not enable TCP/IP by default. Instructions are provided in this topic.

  • Open port for SQL Server
    For a remote server, if you are using firewall software, you must open the port that the Database Engine listens on.

  • Decide on a mode
    A report server database support native mode or SharePoint integrated mode. Native mode is the default. Use this mode when you want to deploy Reporting Services as a stand-alone application server.

    SharePoint integrated mode is used when you plan to deploy the report server together with an instance of a SharePoint product or technology. The database will be created when you run the wizard, but you will not be able to add content or view items until you add the report server to a SharePoint farm. For more information, see Planning a Deployment Mode in SQL Server Books Online.

  • Decide on report server credentials
    Decide how the report server will connect to the report server databases. Credential types include domain user account, SQL Server database user account, or the Report Server service account.

    These credentials are encrypted and stored in the RSReportServer.config file. The report server uses these credentials for ongoing connections to the report server database. If you want to use a Windows user account or a database user account, be sure to specify one that already exists. Although the Reporting Services Configuration Manager will create a login and set the necessary permissions, it will not create an account for you. For more information, see Configuring a Report Server Database Connection.

  • Decide on a report server language
    Choose a language to specify for the report server. Predefined role names, descriptions, and the My Reports folders do not appear in different languages when users connect to the server using different language versions of a browser. For more information about how language resources are used in a report server deployment, see Solution Design Considerations for Multi-Lingual or Global Deployments (Reporting Services) in SQL Server Books Online.

  • Check credentials to create and provision the database
    Verify that you have account credentials that have permission to create databases on the Database Engine instance. These credentials are used for a one-time connection to create the report server database and RSExecRole. If a login does not already exist, a database user login will be created for the account used by the report server to connect to the database. You can connect under the Microsoft Windows account you are logged in as, or you can enter a SQL Server database login.

To enable access to a remote report server database

  1. If you are using a remote Database Engine instance, log on to the database server to verify or enable TCP/IP connections.

  2. Point to Start, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and click SQL Server Configuration Manager.

  3. Open SQL Server Network Configuration.

  4. Select the instance.

  5. Right-click TCP/IP and click Enabled.

  6. Restart the service.

  7. Open your firewall software and open the port that SQL Server listens on. For the default instance, this is typically port 1433 for TCP/IP connections. For more information, see How to: Configure a Windows Firewall for Database Engine Access in SQL Server Books Online.

To create a local report server database

  1. Start the Reporting Services Configuration Manager and connect to the report server instance for which you are creating the database. For more information, see How to: Start Reporting Services Configuration Manager.

  2. On the Database page, click Change Database.

  3. Click Create a new database, and then click Next.

  4. Connect to the instance of the Database Engine that you will use to create and host the report server database:

    1. Type the SQL Server Database Engine instance that you want to use. The wizard will display a local Database Engine that runs as the default instance if it is available. Otherwise, you must type the server and instance to use. Named instances are specified in this format: <servername>\<instancename>.

    2. Enter the credentials used for a one-time connection to the Database Engine for the purpose of creating the report server databases. For more information about how these credentials are used, see Before You Start in this topic.

    3. Click Test Connection to validate the connection to the server.

    4. Click Next.

  5. Specify properties used to create the database. For more information about how these properties are used, see Before You Start in this topic:

    1. Type the name of the report server database. A temporary database is created along with the primary database. Consider using a descriptive name to help you remember how the database is used. Note that the name you specify will be used for the lifetime of the database. You cannot rename a report server database after it is created.

    2. Select the language in which you want role definitions and My Reports to appear.

    3. Select the server mode. Select Native Mode unless you are planning to deploy the report server with a SharePoint product or technology.

    4. Click Next.

  6. Specify the credentials used by the report server to connect to the report server database.

    1. Specify the authentication type:

      Select Database Credentials to connect using a SQL Server database login that is already defined. Using database credentials is recommended if the report server is on a computer that is in a different domain, a non-trusted domain, or behind a firewall.

      Select Windows Credentials if you have a least-privileged domain user account that has permission to log on to the computer and the database server.

      Select Service Credentials if you want the report server to connect using its service account. With this option, the server connects using integrated security; credentials are not encrypted or stored.

    2. Click Next.

  7. Review the information on the Summary page to verify the settings are correct, and then click Next.

  8. Verify the connection by clicking a URL on the Report Server URL page or Report Manager URL page. The URLs must be defined in order for this test to work. If the report server database connection is valid, you will see either the report server folder hierarchy or Report Manager in a browser window. For more information, see How to: Verify a Reporting Services Installation in SQL Server Books Online.