I can't Connect PostgresSQL db with PBI SERVICE ( Web version ) remote certificate invalid

Power BI Online Service
1

Hello everyone

I am currently experiencing some issues connecting my Power BI Service with my Postgres SQL database hosted on an EC2 Ubuntu machine on AWS.

Important: please note that i mean the PBI Service ( dataflows ) because all answers & ref i get is for PBI Desktop

While the database works fine on PGAdmin and also works with ODBC Power BI desktop, I am unable to use Power BI Dataflows due to the following error message: “the remote certificate is invalid”

image

I suspect that the issue might be related to encryption settings, but I’m not sure how to proceed., and I am using the correct username and password to authenticate to the database. is there a way to upload certificate to PBI Service?

image

If anyone has any experience with this issue, I would appreciate your help and suggestions. Thank you in advance!

2

Yes, it’s possible to upload a custom SSL certificate to Power BI Service (dataflows) to establish a secure connection with your PostgreSQL database hosted on AWS EC2. Here are the general steps:

  1. Export the SSL certificate from your PostgreSQL server:
  • Open PGAdmin and connect to your server.
  • Select the server name and navigate to “Certificates” in the left-hand menu.
  • Right-click on the certificate you want to use and select “Export”.
  • Choose a file name and location to save the certificate file.
  1. Convert the certificate to the appropriate format:
  • Open a command prompt or terminal and navigate to the location where you saved the certificate file.
  • Convert the certificate file to the PEM format by running the following command: openssl x509 -in certificate.crt -out certificate.pem -outform PEM
  1. Upload the certificate to Power BI Service:
  • Open Power BI Service and go to the workspace where you want to create a dataflow.
  • Click on “Dataflows” in the left-hand menu and select “New dataflow” to create a new dataflow.
  • In the “Dataflow details” section, select “Connect to a database” and choose “PostgreSQL” as the database type.
  • Enter the connection details for your PostgreSQL server, including the server name, port number, database name, username, and password.
  • Under “SSL Mode”, select “Require SSL” and then click on “Choose a certificate file” to upload the PEM file you created earlier.
  • Test the connection to ensure that it works properly.

Note: If you encounter any issues with the SSL certificate, you may need to contact your database administrator or AWS support to troubleshoot the issue.

3

I can’t find the “dataflows details” you mentioned, i cant find the require SSL you mentioned can you provide some screenshots pleaase?

image
image1134×752 25.9 KB

Theres only " use encrypted …" which means force SSL but no where to upload a certificate

4

I apologize for the confusion. Upon further research, it appears that Power BI Service does not currently support SSL client authentication for dataflows. This means that it is not possible to upload a client certificate to authenticate with the Postgres SQL database on your EC2 instance.

In this case, you might need to consider other options such as using a VPN to establish a secure connection to your database or creating a web service that can communicate with your database and then using Power BI to consume the data from the web service.

Alternatively, you can also consider using Power BI on-premises data gateway to securely connect to your database from Power BI Service. The on-premises data gateway acts as a bridge between your data source and Power BI Service, allowing you to securely access your data without exposing it directly to the internet. You can refer to this Microsoft documentation for more information on how to use the on-premises data gateway with Power BI Service: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

I hope this helps.

5

i apreciate your effort but it appears to be an AI generated answers which is not helpful at all thanks.

6

Bumping this post for more visibility from our experts and users.

7

Hi @Dilawdata - Are you using Gateway to connect to Postgres SQL server. In my case, I had to install the NpgSQL on the Server/VM hosting the Gateway to enable connectivity from Power BI Service. Can you please try that and let us know if it works. More details in below document.

Thanks
Ankit J

8

Update on the matter:
I reached out to the DBA to request the deactivation of the SSL requirement for testing purposes. As expected, the connection worked properly.
Now, I’m wondering if there’s a way to upload the SSL certificate to PBI Service. If not, it seems like we’ll have to use a Windows VM with a gateway and install the certificate on that VM to connect to the database using PBI Service Dataflows.

9

Hi @Dilawdata - I have searched and couldn’t find a way to update a certificate on PBI service. All the posts are related to settings on the VM Server hosting the Gateway.

If no further information is required, please mark this post as closed.

Thanks
Ankit J

10

Hey. We have been facing this very issue for years. Finally got a solution a few days back. There’s just not enough information available on the web at all for this. Posting this for anyone who will need it.

This process works both on PowerBI Desktop as well as a cloud datasource on PowerBI Service. There is NO NEED for a gateway required with this!

I am putting an entire step-by-step process here right from spinning up an EC2 Ubuntu machine to getting a certificate that PowerBI service accepts.

  1. Start a new EC2 instance with Ubuntu 22.04 installed. I started a t3a.nano instance. 8 GB SSD GP3 storage.

Allocate and associate an Elastic IP and point it to the domain yourdb.domain.com .

  1. Create a new security group for PostgresDB. Open HTTP and HTTPS ports for anywhere access which is required for requesting the SSL certificate.

Open Postgres port 5432 to allow remote access to Postgres.

  1. Install Postgres using following commands:

sudo apt update

sudo apt install postgresql postgresql-contrib

  1. Change password of user ‘postgres’ using the following commands:

sudo -i -u postgres

psql

ALTER USER postgres WITH PASSWORD ‘’;

  1. Create Database with follwing command:

CREATE DATABASE “”;

  1. Install Certbot on the server to get free letsencrypt ssl certificate using the following command:

sudo snap install --classic certbot

  1. Generate Certbot certificate using the following command:

sudo certbot certonly --standalone -d yourdb.domain.com

  1. Add the following line to /etc/postgresql/14/main/pg_hba.conf :

hostssl all all 0.0.0.0/0 md5

  1. Change listen_address = ‘*’ in /etc/postgresql/14/main/postgresql.conf to allow remote access

Make the following changes as well:

ssl = on

ssl_cert_file = ‘/etc/postgresql/14/main/fullchain.pem’

ssl_key_file = ‘/etc/postgresql/14/main/privkey.pem’

  1. Run the following commands to copy letsencrypt certificates from its folder to the postgres folder and allow access. Restart postgres.

sudo cp /etc/letsencrypt/live/yourdb.domain.com/fullchain.pem /etc/postgresql/14/main/fullchain.pem

sudo cp /etc/letsencrypt/live/yourdb.domain.com/privkey.pem /etc/postgresql/14/main/privkey.pem

sudo chmod 600 /etc/postgresql/14/main/fullchain.pem /etc/postgresql/14/main/privkey.pem

sudo chown postgres:postgres /etc/postgresql/14/main/fullchain.pem /etc/postgresql/14/main/privkey.pem

sudo systemctl restart postgresql

  1. The letsencrypt certificate has to be renewed using cron since it expires every three months. For this a bash script ‘renew-certificate.sh’ has been written using the above point 10 in the directory /home/ubuntu/renew-certificate. The file can be made executable chmod +x renew-certificate.sh

Following is the bash script:

sudo certbot renew --post-hook "sudo cp /etc/letsencrypt/live/yourdb.domain.com/fullchain.pem /etc/postgresql/14/main/fullchain.pem &&

sudo cp /etc/letsencrypt/live/yourdb.domain.com/privkey.pem /etc/postgresql/14/main/privkey.pem &&

sudo chmod 600 /etc/postgresql/14/main/fullchain.pem /etc/postgresql/14/main/privkey.pem &&

sudo chown postgres:postgres /etc/postgresql/14/main/fullchain.pem /etc/postgresql/14/main/privkey.pem && systemctl restart postgresql"

  1. Set the following is the cronjob using the command crontab -e:

0 0 1 * * /home/ubuntu/renew-certificate/renew-certificate.sh