question

AzevedoEmanoel-9781 avatar image
0 Votes"
AzevedoEmanoel-9781 asked ShaikMaheer-MSFT commented

Canot copy data from Source Oracle to SInk Azure Sql

Hi, I

have created a process to copy data to a data warehouse from on-premises Oracle DB(source) to a Azure SQL DB(sink), but I'm facing the below issue when execute the copy pipeline:

Operation on target Copy data1 failed: ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
Database: 'Xxxxxxxxxxxxxxxx', User: 'xxxxxxxxxxxxxxxxx'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,'
'Type=System.Data.SqlClient.SqlException,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - 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=.Net SqlClient Data Provider,SqlErrorNumber=10060,Class=20,ErrorCode=-2146232060,State=0,
Errors=[{Class=20,Number=10060,State=0,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - 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.),},],''Type=System.ComponentModel.Win32Exception,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=,'

I have already tested the conection and change the configuration on SQL server firewal:
123161-image.png


The conection test with the SQL server:
123162-image.png

Can someone help on it?




azure-data-factoryazure-sql-database
image.png (34.8 KiB)
image.png (25.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AzevedoEmanoel-9781 ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query here.

As per documentation,
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.

Hence, Could you please try to to add your Source IR IP address to Azure SQL firewall rules and see if that works?

0 Votes 0 ·

Hi @ShaikMaheer-MSFT ,

As I could verify, the problem os not related to the source IP. I'm able to access and copy data from this source, but just when I copy it to an sink that doesn't use an integration runtime.

To elucidate: I have created a stage area on my blob storage to copy the data there and them copy to the Azure SQL, it works with no problem.

I could note that when I'm executing the copy, just the integration runtime from the on-primesses source is running, it looks like I'm able to execute just one integration runtime per execution.

Do you have any idea about why I'm not able to copy the data direct from the on-premisses source to the Azure SQL sink?

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @AzevedoEmanoel-9781 ,

"I have created a stage area on my blob storage to copy the data there and them copy to the Azure SQL, it works with no problem."
Here, Self-hosted IR will do copy between On-prem Oracle to Blob and Blob storage doesn't have any IP filter firewall rules hence it works fine. And when data moves from blob to Azure SQL - here Azure IR will get use that do the job without any issue.

But when you try directly, from On-prem to Azure SQL, in this case your On-prem IR will take to respnsibility of loading data in to Azure SQL and we should make sure our On-prem IR IP address whitelisted under Firewall rules of Azure SQL.

Could you please try to add your self-hosted IR IP to firewall rules in Azure SQL and see how it behaves?

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AzevedoEmanoel-9781 ,

Just checking you get chance to check above comments and share updates? Thank you.

0 Votes 0 ·

Hi @AzevedoEmanoel-9781 ,

Following to check you get chance to check above comments and share updates? Thank you.

0 Votes 0 ·

Hello @ShaikMaheer-MSFT , I was out from the office. I will test your suggestion on the next days and update you about the result.

Thanks in advance!

0 Votes 0 ·

Hi @AzevedoEmanoel-9781 ,

Thank you for the update. Please feel free to share if any queries further. Thank you.

0 Votes 0 ·

Hi @AzevedoEmanoel-9781 ,

Is above answer helps you? If yes, Please Accept answer. If there are any further queries please feel free to let us know. Thank you.

0 Votes 0 ·
Show more comments