ApsaraDB RDS:Log on to a SQL Server host with WebShell and use SSRS

Prerequisites

Before you configure SSRS on the SQL Server host, make sure the SQL Server Reporting Services (MSSQLSERVER) service is enabled and running. To view or modify the SSRS service status, see View or modify the SSRS service status.

Confirm that the service's Log On As account is set to an NT Service account.

Considerations

For High-availability Edition or Cluster Edition instances, SSRS configuration may fail because the database is in a mirroring or Always On availability group state. If this issue occurs, use a System Admin account to log on to the SQL Server instance and run the following commands to resolve the issue.

-- For High-availability Edition instances (turn off database mirroring)
ALTER DATABASE [ReportServer] SET PARTNER OFF;
ALTER DATABASE [ReportServerTempDB] SET PARTNER OFF;
-- For Cluster Edition instances (remove databases from the ag-rds availability group)
ALTER AVAILABILITY GROUP [ag-rds] REMOVE DATABASE [ReportServer];
ALTER AVAILABILITY GROUP [ag-rds] REMOVE DATABASE [ReportServerTempDB];

These operations ensure that SSRS can be configured and run properly. For Cluster Edition instances, to ensure high availability and normal backup and recovery, after the SSRS configuration is complete, use a System Admin account to log on to the SQL Server instance and run the following commands to add the databases back to the availability group.

ALTER AVAILABILITY GROUP [AG-RDS] ADD DATABASE [ReportServer]
ALTER AVAILABILITY GROUP [AG-RDS] ADD DATABASE [ReportServerTempDB]

Procedure

1. Log on to the SQL Server instance with a System Admin account and run the following command.

   DISABLE TRIGGER [_$$_tr_$$_rds_alter_database] ON ALL SERVER;

   Note

   For information about how to connect to an SQL Server instance, see Connect to an ApsaraDB RDS for SQL Server instance.

2. Click the Start icon >Reporting Services Configuration Manager.

3. In the dialog box that appears, confirm the report server name and click Connect.

   In the Report Server Instance drop-down list, confirm that the instance is MSSQLSERVER.

   Note

   If your RDS SQL Server instance is a High-availability Edition or Cluster Edition instance, the connection may fail due to database mirroring or availability groups. If this issue occurs, see Considerations in this topic.

4. In the left-side navigation pane, configure Service Account and Web Service URL.

   Note

   For more information about the configuration methods, see the official Microsoft documentation.

5. In the left-side navigation pane, select Database, and then click Change Database to create a report server database on the host.

   1. Select Create a new report server database and click Next.

   2. Confirm the server name, configure the following parameters, and then click Next.

      Parameter	Description
      Server Name	The server name. This is a fixed value and does not need to be modified.
      Authentication Type	Select SQL Server Account.
      Username	Enter the username of the System Admin account that was created on the User Account tab of the RDS SQL Server instance.
      Password	Enter the password of the System Admin account on the User Account tab.

   3. Enter a name for the report server database, select a language, and then click Next.

      In the Database Name field, enter ReportServer. From the Language drop-down list, select Chinese (Simplified, PRC).

   4. Set the credentials for the account to connect to the report server and click Next.

   5. Confirm the summary, click Next, wait for the report server database to be created, and then click Finish.

6. Log on to the SQL Server instance by using a System Admin account and run the following commands.

   USE [master]
   GO
   -- Change the recovery model of the database to FULL and use the NO_WAIT parameter to make the change take effect immediately.
   ALTER DATABASE [ReportServer] SET RECOVERY FULL WITH NO_WAIT
   GO
   ALTER DATABASE [ReportServerTempDB] SET RECOVERY FULL WITH NO_WAIT
   GO
   -- Enable a specific trigger on all databases on the server.
   ENABLE TRIGGER [_$$_tr_$$_rds_alter_database] ON ALL SERVER;

7. Subsequent operations, such as creating a data source, depend on your business requirements. For more information, see the official Microsoft documentation or Report Builder in SQL Server.