cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reporterman5000
New Member

Power BI Service - On-Premises Gateway @ Amazon EC2 & Amazon RDS PostgreSQL

Hello,

 

I've been trying to setup a test Power BI Service environment to test if this is a right product for our organization. The actual data resides in Amazon RDS PostgreSQL relational database.

 

I have succesfully connected to the database with Power BI Desktop. I had to install the Postgres Unicode ODBC 64 bit, which I have also installed on the Amazon EC2 virtual Windows Server 2019 to get the ODBC connection to work. I can succesfully form a connection from my PC and the Amazon EC2 virtual instance to the Amazon RDS Postgres - database with Power BI Desktop programs. So that works fine. I also installed Npgsql 4.0.1.0 on the EC2 Instance, and now the On-Premises Gateway Network Port Tests gives a succesful results, before installing Npgsql 4.0.1.0 it failed.

 

I can see the actual Gateway in the PowerBI Service, but when I try to add the Postgres SQL via Add Data Source --> ODBC --> it gives me the error shown below.

 

 

"Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."

 

 

I tried to add the port to connection string (suffix: :12345) (same port as in ODBC driver), but it didn't help.

 

I feel like I am missing something small here. Any ideas? Is the actual connection string wrong? It is a basic amazon cloud url: "string5235234.area.rds.amazonaws.com" without any prefix or suffix Thanks.

 

 

 

 

 

1 ACCEPTED SOLUTION
Reporterman5000
New Member

Thanks for the reply, I've read that one before, and if one has read that it does not really give help to the actual Power BI Service connection in this particular connection, and the correct syntax for ODBC connection, which is the cause here. 

I can reveal the solution here, as we found in-house today:

 

Remember to put correct ODBC syntax in the connection string:
Driver={PostgreSQL Unicode};Server=amazonaddress.rds.amazonaws.com;Port=32535;Database=YourPostgresDataBase;sslmode=require;

View solution in original post

2 REPLIES 2
Reporterman5000
New Member

Thanks for the reply, I've read that one before, and if one has read that it does not really give help to the actual Power BI Service connection in this particular connection, and the correct syntax for ODBC connection, which is the cause here. 

I can reveal the solution here, as we found in-house today:

 

Remember to put correct ODBC syntax in the connection string:
Driver={PostgreSQL Unicode};Server=amazonaddress.rds.amazonaws.com;Port=32535;Database=YourPostgresDataBase;sslmode=require;

View solution in original post

selimovd
Community Champion
Community Champion

Hey @Reporterman5000 ,

 

to get Power BI Service running with AWS it's a little more work.

Check the official white paper from Amazon on how to connect AWS databases with Power BI Services:

https://docs.aws.amazon.com/whitepapers/latest/using-power-bi-with-aws-cloud/using-power-bi-with-aws...

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps