Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thomasegdf
New Member

Unable to connect to PostgreSQL with SSL

Hello,

 

I am encountering a serious issue today and I think you could help me solve it.

 

I already set up a machine using Windows Server 2019 with Power BI Report Server on it.

We have another machine with a PGSQL 13 database installed on it (also Windows Server 2019).

This database is configured to only accept SSL connections.

 

In order to connect to it, I installed NPGSQL 4.0.10 in the "GAC" via the provided MSI. Power BI Report Server sees NPGSQL and I am able to try to connect to the database.

 

Unfortunately we still get an error when connecting because of the "SSL off".

 

 

2022-05-25 09:49:40.646 CEST [3544] FATAL: aucune entrée dans pg_hba.conf pour l'hôte « X.X.X.X», utilisateur « yyyyy », base de données « zzzzzz », SSL inactif

 

 

Everythings works using Power Bi Desktop, but we're unable to refresh it on Report server.

Are we the only ones to have this problem and if so what are our options? 

 

Thanks in advance.

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @thomasegdf ,

This error  main casuse by configuring the wrong pg_hba.conf file. You needed to configure the file in test-qa-db because that was where the database resided.

 connect locally with psql and run this

SHOW hba_file;

Then from outside edit that file. Perhaps you have two versions of PostgreSQL or you built your own. Or, you're restarting the wrong PostgreSQL. Perhaps make sure you're restarting the right one by connecting, again locally over psql, and running

SELECT pg_reload_conf();

Make sure you're editing the pg_hba.conf on the SERVER you're trying to connect to and not the CLIENT. 

And a tip need to notise:

The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file.

 

Similar question refer:

https://dba.stackexchange.com/questions/161104/pgadmin-postgresql-no-pg-hba-conf-entry-for-host 

https://dba.stackexchange.com/questions/77099/no-pg-hba-conf-entry-for-host-10-1-1-91-user-user-data... 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Hello and thank you Lucien,

 

I don't really understand what you think is wrong with my configuration file. Could you be more explicit?

 

The connection works fine when using Power BI Desktop on another machine. When I use Power BI Report Server on this same machine, it does not work anymore.

 

Here is my configuration : 

 

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
#host    all             all             163.84.146.147/32       scram-sha-256

hostssl  all  			all  			0.0.0.0/0  				scram-sha-256 
#host    all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
#hostssl  all  			 all  			 ::1/128  				scram-sha-256 clientcert=verify-full
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
#host    replication     all             127.0.0.1/32            scram-sha-256
#host    replication     all             ::1/128                 scram-sha-256

 

The only difference between Desktop and Report Server seems to be that Report Server does not support SSL connections to PostgreSQL. ("ssl off" when SSL is required in log message)

 

Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.