Configure SQL Server Reporting Services with AWS RDS SQL Server

Updated: Mar 29


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).

Create new option group for AWS RDS SQL Server

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


add SSRS component

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%.

Default SQL Server Reporting Service configuration in AWS RDS

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

New Option Group

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

Modify RDS instance for the new option group

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'AD\user'

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

https://<rds-endpoint>:<port>/Reports


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


SQL Server Reporting Service Web Portal

Similarly, the report server URL is as below

https://<rds-endpoint>:<port>/Reportserver



https://www.buymeacoffee.com/RAJENDRAGUPTA

176 views0 comments