Issue with connecting ADF to PostgreSQL

Question

Hey,

We are building our data factory on Azure and we are trying to connect a new dataset from PostgreSQL source.

We took the CRT files and generated a PFX file from it, we import it into azure key vault.

Then we went ahead and created the dataset, we insert the server, port, database name, user & password

Then we select the Azure Key Vault, selecting the secret name that we created with the certificate.

Once it's all configured I am clicking test connection and getting this error:
28000: no pg_hba.conf entry for host "40.74.27.93", user "dealio_rep", database "dealio", SSL off Check network connectivity setting of your Azure Postgre SQL flexible server, if Private Access(VNet integration) is selected, please use SHIR to connect.

28000: no pg_hba.conf entry for host "40.74.27.93", user "dealio_rep", database "dealio", SSL off

My question is, if it's something we missed while configuring things? or it's on the server the PostgreSQL is hosted on?

Thanks

Igal

Answer 1

@Igal Cohen | CMTrading

Thanks for using MS Q&A platform and posting your query.

The error message "28000: no pg_hba.conf entry for host ..., user ..., database ..., SSL off" indicates an issue with the access control configuration for your PostgreSQL database.

1. Missing Configuration: The administrator of the PostgreSQL server might not have added an entry in the pg_hba.conf file to allow connections from your Azure Data Factory's IP address with the specified user and database.
2. Network Restrictions: If you're using Azure Database for PostgreSQL with Private Access (VNet integration), the standard connection method might not work. In this case, you'll need to use Secure Sockets Layer with Hostname Resolution (SHIR) for connections.

Here's how to troubleshoot the issue:

• Check with the PostgreSQL Server Administrator: If you don't have access to the server yourself, you'll need to contact the administrator and inform them about the error message. They can check the pg_hba.conf file and add an appropriate entry to allow access from your Azure Data Factory.
• Verify Network Connectivity: Make sure your Azure Data Factory can reach the PostgreSQL server. If you're using Azure Database for PostgreSQL with Private Access, refer to the Azure documentation for connecting using SHIR: https://learn.microsoft.com/en-us/azure/postgresql/
• SSL/TLS Connection: If your PostgreSQL server requires TLS/SSL connections (which is on by default in Azure Database for PostgreSQL servers), you might need to ensure that your connection from Azure Data Factory is also using TLS/SSL
• Firewall Settings: Check if the IP address 40.74.27.93 is allowed through any firewalls that might be in place. This could be on the network level or configured in the Azure settings.

Hope this helps. Do let us know if you any further queries.

Answer 2

• I am also facing the similar issue. Here is my issue.

Problem: I am using the PostgreSQL connector to connect to the on-premises PostgreSQL database. It was working fine and suddenly the connector giving us the problem(Message=Could not load type 'NpgsqlTypes.NpgsqlInterval' from assembly 'Npgsql, Version=4.0.9.2).

Solution: In analysis I found that self-hosted integration run-time(IR) has been updated and problem started after that. I have restarted the self-hosted runtime from the server node.
Now it is running perfectly fine.