Permission error when trying to connect SSRS on Amazon EC2 to Amazon RDS MSSQL
I am trying to deploy SSRS on an Amazon EC2 instance and connect it to a DB report on RDS. Since amazon RDS does not support SSRS, my other option is to open a dedicated EC2 instance with an MSSQL server on it.
I tried to create ReportDB from SSRS configuration utility - get configuration exception: "User does not have permission to do this action." This is most likely caused by insufficient SQL permissions. Amazon does not provide you with a sa user, only the processadmin and setupadmin server roles are provided. In the databases you create, you get dbowner.
Also, I created ReportDB separately and pushed it to RDS, connected SSRS to it, and when it finally recognized it as a proper ReportDB, I got the same exception.
Any thoughts?
source to share
Reporting Services will try to create its own database anyway, so if you don't have permission to create databases, you're stuck. Using the Reporting Services Configuration Tool to manually create databases will still not work if you do not have permission. The configuration also involves using RSExecRole for the SSRS service account, which also requires permission. See Validate Credentials to Create and Provide a Database on this MSDN.
Alternatively, use Microsoft Amazon Machine Image optimized for SQL Server 2008 R2 and all of its services (including SSRS)
source to share