Power BI Report Server for Amazon RDS

[krecks1234]

Hi Power BI Support,

I just wounder how I can install Power BI Report Server on the Amazon RDS.

According to the Database Permissions from Configure a Report Server Database Connection (SSRS Configuration Manager) which I found here https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/configure-a-report-server-da....

I need to set following Database Permissions:

- public and RSExecRole roles for the ReportServer database.
- RSExecRole role for the master, msdb, and ReportServerTempDB databases.

But RSExecRole for master and msdb can not be set in Amazon RDS.

I get the following error:
System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'sysusers', database 'mssqlsystemresource', schema 'sys'.
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:f1c5e83e-a222-453f-a45f-98b16e5fa347
Error Number:229,State:5,Class:14

Is there any way to install/configure Power BI Report Server on Amazon RDS?

kind regards

[v-yuezhe-msft]

@krecks1234,

I am a bit confused about Amazon RDS which you mentioned. Do you want to install Power BI report server in a  AWS VM? As far as I know, Amazon RDS is a managed relational database service that provides you database engine.

In your scenario, when do you get the above error message? Do you use Reporting Services Configuration tool to configure the connection(database permissions are granted automatically in this case) or directly grant account required permissions in SQL Server Management Studio(SSMS)?


The above error message indicates that your account has no permission to perform select operation in required SQL object, please ensure that you are using SQL Server admin account such as sa to login in SSMS and then grant permissions for other accounts.



Regards,
Lydia

[krecks1234]

@v-yuezhe-msft

I am using the Report Server Database Configuration Wizard as below

and the error appers here (this was the first error i got). I am also using the Admin User which i bacame from RDS.

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:c82ffdcb-ebf7-4afa-99a7-60bfc996bbe9Error Number:15247,State:1,Class:16

The Scenario is following:

1 Application Server - EC2 (AppServer): m5.large 2 Cores, 8 GB RAM (Windows Server 2016)

1 Database Server - RDS (Datenbase): db.r4.large 2 Cores, 16 GB RAM (SQL Server Standard Edition)

On the Application Server Power BI Report Server and Power BI Desktop for RS is installed.

Now I stuck with the configuration for the Report Server Database Configuration Wizard with the Amazon Relational Database Service.

Regards,

Kevin

[v-yuezhe-msft]

@krecks1234,

I  am a bit confused about the  Amazon Relational Database Service you refer to.

Do you want to host report server database in  Amazon Relational Database Service? Or do you create a VM in Amazon and install on-premises SQL Server in the VM to host report server database? If it is the former case, it is not supported. As per this article, the following are the supported versions of SQL Server Database Engine that can be used to host the report server databases:

SQL Server 2016 (13.x)

SQL Server 2014 (12.x)

SQL Server 2012 (11.x)

SQL Server 2008 R2

SQL Server 2008

If you are using on-premises SQL Server, please login the SQL instance using SSMS with your account and check if it has admin permission in SQL Server.

Regards,
Lydia

[mpi_gov_vn]

@krecks1234 have you resolved this? I have the exact issue and cannot bypass