oracle - On Prem to Cloud with Data Factory

Question

I have one On Prem Oracle database and one Azure SQL Database and want to use a Copy Data activity to transfer this data.

I have now created a Self Hosted IR for the Oracle database, and I am able to connect to it and preview data from Data Factory editor:

I have a Azure SQL Database that I want to recieve data, and it is set up with AutoResolveIntegrationRuntime, with Connection successful. I am also able to preview data from this database:

When I try to run this Copy Data activity I get following error message:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'sqlsrv', Database: 'database', User: 'user'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

Based on all Docs/Tutorials I have read this should not be failing. I have tried to open up the SQL Server to allow all IP adresses in firewall rules.

Any ideas what I might be doing wrong here?

question from:https://stackoverflow.com/questions/65857955/on-prem-to-cloud-with-data-factory

Answer 1

Since the integration runtime is a bridge across on prem and cloud , you need to check whether you are able to access the onprem database and the Azure SQL database through the VM in which the IR is installed. The VM hosting the IR should be able to access both the source and sink for the copy activity to be successful in case if either of source or sink is using the self hosted runtime. So the issue is not w.r.t Azure SQL database but in the VM hosting the IR