Possible to host SQL server 2022 in AWS RDS as SSRS database?

Question

Hello,

I would like to know is that possible to host SSRS database sql server 2022 in AWS RDS? I trying to host SSRS in EC2 and SQL server in RDS then I use rds admin user to connect database on Configuration manager set up and getting this permission error.

Is that due to RDS is AWS managed service and cannot grant to full permission on user?

Thanks Swe

Answer 1

Hi @Swe

currently i am using like that, SSRS in ec2 and sql server in RDS

Create a Non-RDS-Admin User:

-- On RDS SQL Server  
CREATE LOGIN [SSRS_User] WITH PASSWORD = 'YourPassword';  
CREATE USER [SSRS_User] FOR LOGIN [SSRS_User];  
ALTER ROLE [db_owner] ADD MEMBER [SSRS_User];  

Use this user (not the RDS admin) in SSRS Configuration Manager.

AWS RDS provides restricted stored procedures to manage permissions. Assigns the user to RDS_SSRS_ROLE, granting minimal required permissions:

-- Grant SSRS portal access (replace placeholders)  
EXEC msdb.dbo.rds_msbi_task  
  @task_type='SSRS_GRANT_PORTAL_PERMISSION',  
  @ssrs_group_or_username=N'SSRS_User';  

By the way, the connectivity errors may occur if the EC2 instance’s security group isn’t allowed to communicate with RDS on port 1433. Please confirm the RDS security group permits ​inbound traffic​ from the EC2 instance’s IP/security group.

If permission issues still exists, consider reaching out to AWS Support for assistance.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

AWS RDS for SQL Server does not support hosting SQL Server Reporting Services (SSRS). AWS RDS supports SQL Server databases but does not include SSRS, SQL Server Integration Services (SSIS), or SQL Server Analysis Services (SSAS).

However, you can still use SSRS with SQL Server on AWS by adopting one of the following approaches:

1. EC2 Instances:

• Deploy SQL Server with SSRS on an EC2 instance.
• You have full control over the environment, allowing you to install and configure SSRS.
• Connect the SSRS instance on EC2 to the RDS SQL Server database as the data source.

2. Hybrid Setup:

• Use AWS RDS for SQL Server as the database.
• Host SSRS on an EC2 instance and connect it to the RDS instance.
• This approach reduces the maintenance overhead of managing the SQL Server database directly on EC2.

3. AWS Native Reporting Alternatives:

• Consider using AWS-native services like AWS QuickSight for reporting and data visualization.
• QuickSight can connect to SQL Server RDS and other data sources.

4. Containers:

• Host SSRS using Docker containers on AWS ECS or EKS.
• Use RDS as the backend database.AWS RDS for SQL Server does not support hosting SQL Server Reporting Services (SSRS). AWS RDS supports SQL Server databases but does not include SSRS, SQL Server Integration Services (SSIS), or SQL Server Analysis Services (SSAS). However, you can still use SSRS with SQL Server on AWS by adopting one of the following approaches: 1. EC2 Instances:
  • Deploy SQL Server with SSRS on an EC2 instance.
  • You have full control over the environment, allowing you to install and configure SSRS.
  • Connect the SSRS instance on EC2 to the RDS SQL Server database as the data source.
  2. Hybrid Setup:
  • Use AWS RDS for SQL Server as the database.
  • Host SSRS on an EC2 instance and connect it to the RDS instance.
  • This approach reduces the maintenance overhead of managing the SQL Server database directly on EC2.
  3. AWS Native Reporting Alternatives:
  • Consider using AWS-native services like AWS QuickSight for reporting and data visualization.
  • QuickSight can connect to SQL Server RDS and other data sources.
  4. Containers:
  • Host SSRS using Docker containers on AWS ECS or EKS.
  • Use RDS as the backend database.