An error happened while reading data from the provider. The remote certificate is invalid according to the validation procedure

Question

I'm trying to connect Postgres Database on AWS EC2 instance to Microsoft PowerBI. I tried various method available on internet but its showing the above error. Although I've done this connection on AWS RDS. I installed required dependencies (GAC) and all the certificates required for PowerBI.

Answer 1

Following worked for me:

File / Options and settings / Data source settings

then select data source

and Edit Permissions and uncheck encrypt connections

Answer 2

I've came across the same issue, unfortunately I didn't find solution to resolve it. But I found an alternative.

You can connect PostgreSQL using ODBC Connection.

Setup ODBC for PostgreSQL: https://www.postgresql.org/ftp/odbc/versions/msi/

After Installation, Select ODBC Connection under "Get Data"

Select "None" under Data Source Name and add following statement under connection string:

Driver={PostgreSQL ANSI(x64)};Server=<host>;Port=5432;Database=<dbname>

Click "Next"

Enter Username and Password, Click Connect. If credentials are correct, you will see list of tables available in database.

Follow this reference for detailed description: http://niftit.com/connecting-power-bi-to-postgresql/

Answer 3

Two hours wasted, so writing in full.

Download and install postgresql ODBC driver

Goto below URL

URL: https://www.postgresql.org/ftp/odbc/versions/msi/

Scroll down
Click on a zip file to download  (i took psqlodbc_12_00_0000-x64.zip)
Unzip file
Run the .MSI file
   Take all defaults given and install it

Create ODBC data source

Find the ODBC data source using below (I took "ODBC Data Sources (64-bit)"):

Windows > Start button > search for "ODBC Data Sources"
User DSN > PostgreSQL ANSI(x64) > Finish
Give details
  Data source name : PostgreSQL30                ###
  DB name *** , server, port, username, pwd
  SSL mode = disabled (default)
click on Test button
  "Connection successful" message will come, else re-check details above.

We now have a new User DS, with PostgreSQL30 created

Download Power BI

Windows > Start button > Microsoft Store
Search for "Power BI Desktop" > Get/Install

Source: https://learn.microsoft.com/en-us/power-bi/desktop-get-the-desktop#download-power-bi-desktop

Running the Power BI

Get Data (On the left)
Search for "odbc" > Select "ODBC" on right pane > Connect
  DSN > PostgreSQL30 ( --- this is same as above ### --- )
  OK

"Navigator" dialog is shown, with "Display Options"

ODBC ... PostgreSQL30
> DB name   ( --- this is same as above *** --- )
  Open the > button on left of DB name
  select the tables
  click "Load" button 

Done. You have successfully connected Power BI (PBI) to your postgres or other database.

Hope this helps.

Answer 4

For AWS look at this case https://community.powerbi.com/t5/Desktop/The-remote-certificate-is-invalid-according-to-the-validation/td-p/531475

For non-AWS issue you should configure SSL according to Npgsql documentation https://www.npgsql.org/doc/security.html or you can disable SSL option in postgresql.conf on postgresql server.

Answer 5

adding onto @chirag sanghvi answer

You may get an error ERROR: character 0xefbfbd of encoding "UTF8" has no equivalent in "WIN1252"

because your database is encoded in UTF-8

Install this driver

https://github.com/npgsql/npgsql/releases/tag/v4.1.3

then in your connection string use the Unicode driver

Driver={PostgreSQL Unicode(x64)};Server=;Port=5432;Database=

Answer 6

I got the same error, but at least in my case the problem was due to the fact that the certificate I was using was self-signed. Once I generated a new one with certbot/letsencrypt properly signed by a CA, Power BI didn't give me any more trouble to connect.