Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
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.