Configure SQL Server Reporting Services with AWS RDS SQL Server

This blog will look at how you can configure SQL Server Reporting Services with AWS RDS SQL Server.

Introduction

SQL Server Reporting Services is a powerful reporting solution for Microsoft SQL Server. It allows you to share your reports with a lot of people. You can also create charts and graphs in the reports. Creating reports is a lot easier with SQL Server Reporting Service (SSRS).

Amazon RDS for SQL Server is a service from Amazon Web Services. This service lets you run Microsoft SQL Server in the cloud.

SSRS configuration with Amazon RDS for SQL Server

Suppose you use the AWS-managed relational database – AWS RDS SQL Server. You need to use it for reporting purposes. SQL Server on-prem provides SQL Server Reporting Service (SSRS) to deploy, schedule, and manage the SSRS reports.

You can also configure AWS RDS SQL Server for SSRS Reports. Here are the requirements to use the SSRS with Amazon RDS for SQL Server.

Use SQL Server 2016 (13.0058220.21.v1 or above) or 2017(14.00.3223.3.v1 or above) or 2019 Standard or Enterprise editions

The instance should join to AWS Directory Service for Microsoft Active Directory

Let's say you already have a running AWS RDS SQL Server instance with SQL Server 2019. Navigate to the RDS dashboard in the AWS portal and create a new option group to use the SSRS.

This option group should align with your RDS instance's major version and edition (standard or enterprise).

Once the new option group is deployed, select and edit it. In the options menu, choose the SSRS as shown below.

It uses the following default properties.

• Port: By default, SSRS in RDS uses port 8443. You can use any port from 1150 to 49151. However, this port should not conflict with any reserved ports 443 and 80.
• VpcSecurityGroupMembership: Select the security group that is associated with your RDS instance. This security group should allow inbound access to SSRS on the configured port
• Max Memory: This option configures the maximum memory for the SSRS. The default value is 45%. If the SSRS reaches the max memory threshold, it tries to free up the memory or refuse further requests. The valid max memory values are 10-80%.

As shown below, we created the new option group with the SSRS feature for AWS RDS SQL Server.

Now, in the RDS dashboard, select the instance and modify it to use the new option group. By default, it uses the default option groups as shown below

Once the instance is modified, you can see two new databases in your RDS instance.

rdsadmin_ReportServer

rdsadmin_ReportServerTempDB

Now, run the following stored procedure with your AD user to provide admin (content manager) permissions.

exec msdb.dbo.rds_msbi_task
 @task_type='SSRS_GRANT_PORTAL_PERMISSION',
 @ssrs_group_or_username=N'ADuser'

You can launch the SSRS web portal from the URL in the following format.

https://:/Reports

Here the is the endpoint of the RDS instance, and is the SSRS port configured in the option group.

Similarly, the report server URL is as below

https://:/Reportserver