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

Manjusha 21 Reputation points
2021-01-15T14:56:47.39+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,751 questions
Azure Database for PostgreSQL
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 46,427 Reputation points Microsoft Employee
    2021-01-19T22:31:33.2+00:00

    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.

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

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,427 Reputation points Microsoft Employee
    2021-01-19T22:27:11.943+00:00

    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.

    58293-image.png

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

    Looking forward to your confirmation.

    Thank you

    1 person found this answer helpful.