This article guides you on using the host account of an ApsaraDB RDS for SQL Server instance to log in and manage the SQL Server databases using SQL Server Reporting Services (SSRS).
ApsaraDB RDS for SQL Server offers a Webshell feature, which allows users to log in to the operating system of an RDS SQL Server instance through a web interface. With Webshell, users can execute commands, upload and download files, and perform various operations on the RDS SQL Server instance. Webshell provides a convenient and efficient remote management method, especially when an SSH client is not available.
SQL Server Reporting Services (SSRS) is a Microsoft enterprise-level reporting service that enables users to generate various types of reports from SQL Server databases and other data sources. These reports include tabular reports, chart reports, crosstab reports, and multi-dimensional data reports. SSRS can connect to an RDS SQL Server database and use it as a data source to generate different types of reports. This combination allows for efficient data management, analysis, and report generation to support business decision-making and management.
For example, suppose you are a database administrator for a small or medium-sized enterprise, responsible for managing the company's SQL Server databases. As the company's workload and database access requests increase, you need to use SSRS to analyze and monitor the databases. However, since the company's server is located elsewhere, you cannot directly log in to the host to perform these operations.
ultimately access the host where the RDS SQL Server instance resides. Once you are logged in, you can easily manage and operate the SQL Server databases using SSRS.
Ensure that the RDS instance meets the following requirements:
• The RDS instance is located in a region other than the China (Zhangjiakou).
• The RDS instance: RDS Basic Edition, RDS Cluster Edition, or RDS High-availability Edition. If your RDS instance runs RDS High-availability Edition, make sure that the instance runs SQL Server 2012 or later.
• The RDS instance belongs to the general-purpose or dedicated instance family. The shared instance family is not supported.
• The RDS instance resides in a virtual private cloud (VPC). For more information about how to change the network type of an RDS instance, see Change the network type of an ApsaraDB RDS for SQL Server instance.
The creation time of the RDS instance meets the following requirements:
• If the RDS instance runs RDS High-availability Edition or RDS Cluster Edition, the instance is created on or after January 1, 2021.
• If the RDS instance runs RDS Basic Edition, the instance is created on or after September 2, 2022.
Note: You can view the Creation Time parameter of an RDS instance in the Status section of the Basic Information page in the ApsaraDB RDS console.
• An Alibaba Cloud account is used to log on to the RDS instance.
• The permissions to create a system admin account are granted. For more information, see Create a system admin account for an ApsaraDB RDS for SQL Server instance.
• A system admin account is created. For more information, see Create a system admin account for an ApsaraDB RDS for SQL Server instance.
• A host account whose account type is System Admin Account is created. For more information, see Create a host account for an ApsaraDB RDS for SQL Server instance and use the host account for logons.
Note:
The system admin account or the host account of an RDS instance has the permissions that are beyond the management scope of ApsaraDB RDS. After you create this type of account for an RDS instance, the system does not provide the service availability that is specified in Alibaba Cloud service level agreement (SLA) for the RDS instance.
1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
2. In the left-side navigation pane of the page that appears, click Accounts.
3. On the page that appears, click the Host Account tab. Find the required host account and click Remote Connection (Primary) in the Actions column.
4. In the Remote Connection dialog box, enter the password of the host account.
5. Click OK.
The system generates a webshell URL and automatically connects to the host on which the RDS instance resides by using the URL. The system displays a webshell page in a pop-up window. The page may be blocked by the browser. If the page is blocked, you can configure the browser to allow the page to be displayed. The following figure shows an example.
Before you configure SSRS for the host on which the RDS instance resides, make sure that SQL Server Reporting Services (MSSQLSERVER) is enabled and running. For more information about how to view or change the status of SSRS, see View or modify the status of SSRS.
When your RDS instance runs RDS High-availability Edition or RDS Cluster Edition, the RDS instance is in the mirroring state or Always On availability group state. This may cause an error in the SSRS configuration. If an error occurs, you must use the system admin account to log on to the RDS instance and execute the following statements to resolve the error:
Note: RDS instances regularly build images or availability groups. When you configure SSRS, multiple configuration errors may occur. To resolve the error, you must log on to the RDS instance by using the system admin account each time the error occurs and execute the required statements.
-- Execute the following statements to disable the image of the database on an RDS instance that runs RDS High-availability Edition:
ALTER DATABASE [ReportServer] SET PARTNER OFF;
ALTER DATABASE [ReportServerTempDB] SET PARTNER OFF;
-- Execute the following statements to remove the database from the ag-rds availability group of an RDS instance that runs RDS Cluster Edition:
ALTER AVAILABILITY GROUP [ag-rds] REMOVE DATABASE [ReportServer];
ALTER AVAILABILITY GROUP [ag-rds] REMOVE DATABASE [ReportServerTempDB];These statements ensure that SSRS can be properly configured and run. If your RDS instance runs RDS Cluster Edition, you must use a system admin account to log on the RDS instance and execute the following statements to add the database to the availability group after SSRS is configured. This helps ensure high availability and proper database backup and restoration.
ALTER AVAILABILITY GROUP [AG-RDS] ADD DATABASE [ReportServer]
ALTER AVAILABILITY GROUP [AG-RDS] ADD DATABASE [ReportServerTempDB]1. Use the system admin account to log on to the RDS instance and execute the following statement:
DISABLE TRIGGER [_$$_tr_$$_rds_alter_database] ON ALL SERVER;Note:
For more information about how to connect to an RDS instance, see Connect to an ApsaraDB RDS for SQL Server instance.
2. Click the 
> icon and select Reporting Services Configuration Manager.
3. In the dialog box that appears, confirm the name of the report server and click Connect.
Note: If your RDS instance runs RDS High-availability Edition or RDS Cluster Edition, you may fail to connect the RDS instance to a report server due to the mirroring or availability group state. If the connection fails, you can resolve the issue based on Usage notes.
4. In the left-side navigation pane, click Service Account and Web Service URL and configure the parameters based on your business requirements.
Note: For more information, see Official documentation.
5. In the left-side navigation pane, click Database. On the right side of the page, click Change Database to create a report server database on the host.
a) Select Create a new report server database and click Next.
b) Confirm the server name, configure the following parameters, and then click Next.
| Parameter | Description |
| Server Name | The name of the server. The name cannot be changed. |
| Authentication Type | The authentication type. Select SQL Server Account. |
| Username | The username of the host account that is created for the RDS instance. |
| Password | The password of the host account that is created for the RDS instance. |
c) Enter a name for the report server database and select a language for the script. Then, click Next.
d) Specify the credentials for the account to connect to the report server and click Next.
e) Confirm the information on the Summary page and click Next. Wait until the report server database is created. Then, click Finish.
6. Use the system admin account to log on to the RDS instance and execute the following statements:
USE [master]
GO
-- Change the recovery model of the database to FULL. Use the NO_WAIT parameter to allow the change to immediately take effect.
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 vary based on your business requirements. For more information, see Step 6 to Step 8 in Install and use SSRS on an ECS instance or official documentation.
1. Log on to the host on which the RDS instance resides. In the search box, enter services.msc to open Services.
2. In the Services (Local) window, view the status of SQL Server Reporting Services (MSSQLSERVER).
Note:
SSRS supports the following startup types:
• Manual: The service does not automatically start when the system starts and must be manually started.
• Automatic: The service automatically starts when the system starts.
• Automatic (Delayed Start): The service automatically starts a period of time after the system starts.
• Disabled: The service is disabled and cannot be started.
3. Optional. Change the status of SSRS and start the service.
a) Double-click the service. In the dialog box that appears, change the value of the Startup type parameter.
b) Right-click the service. In the dialog box that appears, click Start.
c) View the new status of SSRS.
Can I use API interfaces to obtain the hostname of my RDS instance and the webshell URL to connect to the host of my RDS instance?
Yes, you can use the DescribeDBInstanceIpHostname interface to obtain the hostname of your RDS instance, and then call the DescribeHostWebShell interface to obtain the LoginUrl (webshell URL).
Note
• The webshell URL is valid for 2 minutes. You must use the URL at the earliest opportunity.
• If the URL becomes invalid, you can call the API to obtain the new URL.
About Database Kernel | How to Analyze Semi-Structured Data Efficiently Through PolarDB IMCI
ApsaraDB - March 4, 2021
Alibaba Cloud Security - December 25, 2018
Alibaba Cloud Security - August 29, 2018
Alibaba Clouder - July 12, 2019
Alibaba Clouder - December 14, 2017
Alibaba Cloud Security - December 25, 2018
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn More
Database Overview
ApsaraDB: Faster, Stronger, More Secure
Learn More
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn More
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by ApsaraDB
