Connecting PostgreSQL hosted in Google Cloud Platform

[Alex_Ooi]

Hi there, most recently my company had moved its PostgreSQL RDS from AWS to GCP. Things were going fine until I reformatted my machine today. After hours of bantering through the solution here (https://community.powerbi.com/t5/Desktop/The-remote-certificate-is-invalid-according-to-the-validati...) I just realised the reason why I couldn't proceed would be due to the location where the server is hosted.

Right now, I am stuck because usually when I downloaded the npgsql driver, it would have worked when my PostgreSQL RDS was still hosted in AWS in the past. Like, I don't even know should I download pgAdmin (because I tried that and it still didn't work anyway).

The errors returned are a few iterative ones, which seems to be running in circles when I tried other solutions:

"We couldn't authenticate with the credentials provided. Please try again."

But I could run my queries in Navicat just fine with no connectivity issues like this.

"Could not receive data from client: No connection could be made because the target machine actively refused it."

I revoked my credentials, then made sure they are set in database level and not Windows level. The problem still persists.

"Could not connect to server: Connection refused (0x0000274D/10061)"

This happens to be the most technical problem that I don't even understand where to head after reading some articles.

I am not a programmer, neither am I an expert with softwares. I would really appreciate if there are any database architect who's able to explain in details to me how all these should work in a GCP environment.

[amitchandak]

@Alex_Ooi , Power BI will only take SSL enabled connection and you have load SSL certificate to windows

Do not miss any step in this -https://community.powerbi.com/t5/Power-Query/Connecting-to-PostgresSQL-hosted-on-AWS-RDS/td-p/135666

https://dataintoresults.com/post/powerbi-and-postgresql-ssl-lets-encrypt-and-gateway/

[Alex_Ooi]

Hi @amitchandak appreciate your reply. As mentioned in my post, that was the first thing I did.

Unfortunately that solution only worked when you're hosting your server on AWS. I am wondering if there is something similar for GCP that I could proceed with the other steps?

[v-shex-msft]

Hi @Alex_Ooi,

In fact, I think power bi may only support the scenario that you mentioned. For any advanced operations or other redirections, I don't think it is impossible to achieve.
Any other data connector drivers or API interfaces to link/package your data source connections with other authorizations modes without SSL?

Regards,

Xiaoxin Sheng

[Alex_Ooi · Accepted solution]

Hi @v-shex-msft after discussing with my DevOps, the root cause of this problem is not with the SSL certificate. Rather, it was with the proxy connection where the request was refused. When he gave me the principal IP to connect with, all problems solved. I will close this case now. Thank you for your response.