Connect Desktop Power BI to AWS RDS postgreSQL

[Kat7]

I know there are many posts about this issue, but so far, none of the answers have fixed the issue for me.

I'm trying to connect Power BI to an AWS RDS postgeSQL instance.  I've tried from a computer connected to the VPC via VPN, and on an EC2 instance in the same VPC as the RDS, and I get the same results on both machines.

I have tried:

• the latest version of Power BI desktop
• Installed npgsql v 4.0.10 with GAC
• Installed the AWS region certs and put them in the Trusted Root Certificates
• 5432  port is open in AWS security groups
• Turned off encryption in Power BI data source options
• Installed Microsoft on-premises data gateway and have allowed connectors and data refreshes through it.
• I've installed PsqlODBC and added the connection to it.  The software has no 'test' option though.
• I can access the database through pgAdmin

Any assistance would be greatly appreciated.

Thank you!

[Kalpeshb]

i have already created ODBC driver and as soon as connect through ODBC to power bi all data are converted in import mode but i want to connect power to srever through Direct Query .If anyone has solution how to do this please help me to solve this issue.Thanks

[Fralar]

This is still a big issue in my company, not being able to use the native PBI PostgreSQL driver with our AWS RDS PostgreSQL instance.

This is because we need to use Direct Query but it's not possible due to this errors that i see on every post on this topic.

The error I see is:

Unable to connect

We encountered an error while trying to connect.
Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"

I do not see why this driver should not work with AWS RDS PostgreSQL, does it only work with Azure Database for PostgreSQL?

It would be really disappointing.

Has anyone ever make this driver work or just using ODBC?

Thanks

[yunusgrgz1]

@Kat7 's solution works, use it

[Kat7 · Accepted solution]

For anyone experiencing this issue, this is how I got it working:

1. Install the latest version of psqlodbc (https://www.postgresql.org/ftp/odbc/versions/msi/).  Originally I had an older version of this software, and it didn't have the required drivers.

2. Open ODBC Data Sources (64-bit)

3. Under User DSN, add a new source, and select the PostgreSQL ANSI(x64) driver. 

4. Configure the connection:

     -The database value was not the db-identifier listed in the AWS RDS console, but the individual database name within the instance.

     - The server and port values are the endpoint name and port listed in the RDS console.

        - The username and password are an account that has the ability to access the database.

5. Save and test the connection to make sure it works.

6. In Power BI, add a new source using the ODBC connection.

6. Select the data sourcce you just created in ODBC.

7. When it asks for credentials, use the same ones you entered in ODBC.

8. Power BI should then load the database in the Navigator window.

Hope this helps!

[yunusgrgz1]

Thank you very much, it works

[Mariela-TS]

Hi @Kat7 ,

Is this solution going to work once the dashboard is published with auto refrsh for instance? 
Thanks, M

[Kat7]

@Mariela-TS Yes, what I ended up doing was setting up this connection on a server and then creating a Power BI Gateway on that same server.

Once I linked the sources on the dashboard throuh the Gateway it all worked perfectly.

[Anonymous]

Hi, @Kat7 

You can check your firewall settings and make sure that you don't have any local firewall settings blocking connections between Power BI and RDS in addition to AWS security groups.

Try using PostgreSQL's command-line tool to connect to RDS from the same machine to troubleshoot issues specific to Power BI.

If encryption is turned off in Power BI, make sure that the RDS instance is also configured as a non-encrypted connection.

Hopefully, the links below will be helpful to you:

Connecting Power BI to AWS RDS PostgreSQL - Saurabha Joglekar

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kat7]

I turned the firewall off on the server with Power BI to ensure that wasn't an issue.

I am able to connect to the database through SQL Shell, but I'm still getting the same error message in Power Bi.