SQL Server Reporting Services (SSRS) is a powerful reporting solution for Microsoft SQL Server. It allows you to create and share reports with a large number of people, as well as create charts and graphs within the reports. In this blog post, we will explore how you can configure SSRS with AWS RDS SQL Server, a service provided by Amazon Web Services that allows you to run Microsoft SQL Server in the cloud.

Requirements for using SSRS with Amazon RDS for SQL Server

In order to use SSRS with Amazon RDS for SQL Server, you need to meet the following requirements:

• 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

Configuring SSRS with Amazon RDS for SQL Server

Assuming you already have a running AWS RDS SQL Server instance with SQL Server 2019, follow these steps to configure SSRS:

1. Navigate to the RDS dashboard in the AWS portal and create a new option group that aligns with your RDS instance’s major version and edition (standard or enterprise).
2. Select and edit the newly created option group. In the options menu, choose SSRS.
3. Configure the following properties:

• Port: By default, SSRS in RDS uses port 8443. You can choose any port from 1150 to 49151, as long as it does not conflict with reserved ports 443 and 80.
• VpcSecurityGroupMembership: Select the security group 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 SSRS. The default value is 45%. If SSRS reaches the maximum memory threshold, it will try to free up memory or refuse further requests. Valid max memory values are 10-80%.

Once you have created the new option group with the SSRS feature for AWS RDS SQL Server, go back to the RDS dashboard and select the instance you want to modify. Set it to use the new option group instead of the default option groups.

After modifying the instance, you will see two new databases in your RDS instance: rdsadmin_ReportServer and rdsadmin_ReportServerTempDB.

To provide admin (content manager) permissions, run the following stored procedure with your AD user:

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

You can access the SSRS web portal by using the following URL format: https://<endpoint>:<port>/Reports. Replace <endpoint> with the endpoint of your RDS instance and <port> with the SSRS port configured in the option group.

Similarly, the report server URL is as follows: https://<endpoint>:<port>/Reportserver.

By following these steps, you can successfully configure SQL Server Reporting Services with AWS RDS SQL Server and leverage the power of SSRS in the cloud.