I am trying to set up a PowerBI onPremise Gateway with a gcp CloudSQL-Postgres instance as a datasource. I am using the public IP address of a CloudSQL instance as a connection endpoint. In PowerBI I am using the regular Postgres Connector. The connection is also working fine, but only if I don’t encrypt it. When I try to apply encryption, it has the following Error: : An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'
The Gateway is installed on a Windows Server 2022. I downloaded the CloudSQL-Server Certificate and added it to the “Computer account” using MMC. I added it to the “Trusted Root Certificates”. Unfortunately, the Error above didn’t change. What am I doing wrong/missing? Any help is highly appreciated.
Just in case someone comes across this in the future. I “solved” the problem by using the ODBC protocol. The protocol allows you to provide a key, client-, and Servercertificate for the connection. So, all I did was creating a client certificate in the CloudSQL instance and provide it to the psql driver. Reference here: https://odbc.postgresql.org/docs/config.html
In Datasource you can navigate to page 3 and you will find a textbox where you can enter the following:
sslrootcert=C:\myfolder\myroot sslcert=C:\myfolder\mycert sslkey=C:\myfolder\mykey
After that the connection worked for me and the gateway as well as the PB-Desktop version where able to connect to the Datasource using the public IP-Adress as well as being encrypted.