Issue while migrating Power BI server database to AWS RDS

[RVK]

HI All,

We are facing issues when trying to migrate the power bi report server database which we have  on the standalone SQL server instance to a AWS RDS. We were able to restore the database to RDS using native back up file but unable to configure the power bi report server with the restored database. We get below error message when trying to configure the database. Not sure what permissions we are missing. What permissions we will need to be able to make this work. 

System.Data.SqlClient.SqlException (0x80131904): User does not have permission to perform this action.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
ClientConnectionId:3abb6216-bd88-451a-bb3a-8cea85ba9d11
Error Number:15247,State:1,Class:16

Thank you,

[d_gosbell · Accepted solution]

I'm not sure if RDS is supported as a backend for Report Server so it will be interesting to see if this works for you. To configure the back end database I believe the person running the configuration tool needs to have sysadmin rights. This is because a new application role needs to be created in MSDB and the PBIRS service account needs to be added to some of the SQL Agent operator roles so that it can create SQL Agent jobs in order to trigger scheduled reports and refreshes. Once the configuration has been done you can remove the sysadmin rights from that person. 

[RVK]

Thank you very much. I really doubt if AWS would allow sysadmin rights atleast for a second 😀. 

Not sure on how to take this forward.