Issue while migrating Power BI server database to AWS RDS

RVK — Wed, 16 Mar 2022 15:29:15 GMT

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,

Re: Issue while migrating Power BI server database to AWS RDS

d_gosbell — Wed, 16 Mar 2022 21:43:46 GMT

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.

Re: Issue while migrating Power BI server database to AWS RDS

RVK — Thu, 17 Mar 2022 05:35:25 GMT

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.

topic Re: Issue while migrating Power BI server database to AWS RDS in Report Server

Issue while migrating Power BI server database to AWS RDS

Re: Issue while migrating Power BI server database to AWS RDS

Re: Issue while migrating Power BI server database to AWS RDS