Azure PostgreSQL as data source in Azure Data Factory

Question

Hello to all,

I have the following problem. I am working with Azure PostgreSQL and Azure Data Factory to make different data copy processes. The problem comes that when I use my Azure PostgreSQL DB as a sink it works fine, that is, if I make data copies from other elements to it. However, if I use it as a data source I get the following error:

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.,Source=System,''Type=System.Net.Sockets.SocketException,Message=A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond,Source=System,'

I don't understand why this is happening if it is enabled to work with all Azure services.

Thank you in advance

Answer 1

@Andres Esteban - Thanks for the question and using MS Q&A platform.

It seems like you are encountering an error while using Azure PostgreSQL as a data source in Azure Data Factory. The error message you provided indicates that the connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

There could be several reasons why this error is occurring:

Most often, such kind of errors are transient errors which gets resolved by itself using retry logic. - reason could be Azure PostgreSQL took time to start.

Another possible reason could be timeout. There is a default timeout of 30s for requests with a postgres connection. The only option that does something is to add Timeout=600;CommandTimeout=0; to your connection string in your linked service (if you use a key vault for exemple) or add the options in the linked service additionnal parameters as shown below:

Another possible reason is that there might be a firewall blocking the connection between Azure Data Factory and Azure PostgreSQL. You can check if the firewall is blocking the connection by ensuring that the IP address of the machine running Azure Data Factory is added to the allowed list of IP addresses in the firewall settings of your Azure PostgreSQL server.

Another possible reason could be that the connection string you are using to connect to Azure PostgreSQL is incorrect. You can check if the connection string is correct by verifying the server name, database name, username, and password.

Lastly, it is possible that there might be an issue with the Azure PostgreSQL server itself. You can check if there are any service outages or issues with the server by checking the Azure status page.

For more details, refer to Troubleshoot connection issues to Azure Database for PostgreSQL - Single Server.

I hope this helps you troubleshoot the issue. If you have any further questions or concerns, please let me know.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.