Azure Data Factory : Self hosted IR fails to connect to Azure PostgresSQL

Question

Hi,

We are trying to move data from Postgres DB running on On-Prem using Azure data factory copy data activity. We have installed Self hosted IR (SHIR) on one of our On-Prem machines. We are able to run lookup activity which connects to this On-Prem database table using SHIR successfully. But the copy activity with source as this On-Prem Postgres database table and Sink as Azure PostgreSQL database fails with following error:

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=Npgsql,'

Below error is seen in the event logs of windows host where SHIR is installed:
TraceComponentId: TransferTask
TraceMessageId: TransferRunFailed
@logId: Error
FunctionName: Execute
jobId: f97a6d67-5642-d27b-e551-bf16968da87a
activityId: 6802a759-6a04-426f-8ee8-77ad485e54eb
eventId: TransferRunFailed
message: Copy failed with error: '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=Npgsql,StackTrace= at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout)
at Npgsql.NpgsqlConnector.<RawOpen>d__153.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at Npgsql.NpgsqlConnector.<Open>d__149.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at Npgsql.ConnectorPool.<AllocateLong>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at Npgsql.NpgsqlConnection.Open()
at Microsoft.DataTransfer.Runtime.ConnectionContextExtension.ExecuteWithConnectionContext(IConnection connection, Action action, ConnectionContext context, Boolean needDisposeConnection)
at Microsoft.DataTransfer.Runtime.PipelineItemProfilerTelemetry.PreProcessSink()
at Microsoft.DataTransfer.Runtime.PipelineItemProfilerTelemetry.PreProcess()
at Microsoft.DataTransfer.TransferTask.CopyTask.PrepareCopyPipeline()
at Microsoft.DataTransfer.TransferTask.CopyTask.DoCopy()
at Microsoft.DataTransfer.TransferTask.CopyTaskBase.Execute(),'

Please let us know how to get resolve this?

Thanks,
-Manjusha

Answer 1

Continuation to above comment:

By default, Azure Database for PostgreSQL server that you create is not publicly accessible. You need to give permissions to your IP address.

Here are relevant docs related to Azure Database for PostgreSQL firewall configuration and troubleshooting guidelines.

• Configure a firewall rule for Azure Database for PostgreSQL
  
• Troubleshooting Azure Database for PostgreSQL firewall issues

Hope this helps. Please keep us posted how it goes.

Answer 2

Hi @Manjusha ,

Thanks for additional details. From the details provided it sounds like a firewall issue. Curious to know if you have also tried whitelisting SHIR machine IP address in Azure PostgressSQL DB? If not could you please try whitelisting SHIR machine IP address and see if that helps.

The reason I would like to check on this because when copying between a cloud data source and a data source in private network: if either source or sink linked service points to a self-hosted IR, the copy activity is executed on that self-hosted Integration Runtime.

Please try whitelisting SHIR machine IP address and let us know how it goes.

Looking forward to your confirmation.

Thank you