locked
Copy from on-premises DB2 to Azure SQL error - Failed to connect to SQL server RRS feed

  • Question

  • I have a Data Factory with gateway and linked services configured so that my pipeline can copy data from a DB2 on-premises database to Azure SQL Database using a stored procedure previously created on Azure SQL. However, I am always getting an error while running the pipeline. The error message is the following:

    Copy activity encountered a user error: ErrorCode=UserErrorFailedToConnectToSqlServer,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Server: 'tcp:csn.database.windows.net,1433', Database: 'VIMS', User: 'convcsn@csn'.,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 - No connection could be made because the target machine actively refused it.),Source=.Net SqlClient Data Provider,SqlErrorNumber=10061,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=10061,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 - No connection could be made because the target machine actively refused it.),},],''Type=System.ComponentModel.Win32Exception,Message=No connection could be made because the target machine actively refused it,Source=,'.

    I checked my pipeline and connections by changing source and sink, one at a time, as follows:

    1. I changed my source from on-premises DB2 to a blob in the same resource group of my Data Factory, and keept the sink as SQL and running my stored procedure. The results were as expected.
    2. I kept the source as on-premises DB2 and changed the sink to a blob. The results were also as expected.

    Since both alternatives above worked, I could not find the problem that is causing the error, when I try to use on-premises DB2 as a source of data and Azure SQL Database as sink.

    Any ideas of what might be happening?

    Thank you.

    Friday, April 1, 2016 1:27 PM

Answers

  • Thanks for the questions. Such error message should be caused by that your Gateway machine cannot access the Azure SQL Server (e.g. caused by firewall setting).

    Your other 2 runs succeeded because (1) the first run doesn't have on-premised data sources and didn't run in your Gateway machine, and (2) the second run didn't connect to the Azure SQL Server. Such issue should be reproed if you copy from on-prems SQL Server to the Azure SQL Server.

    Tuesday, April 5, 2016 2:34 AM
  • This looks like the gateway machine cannot connect to the Azure SQL Database. Please check whether the client IP is in the allowed IP list on Azure SQL Database portal. You could try to use SMSS to connect to the Azure SQL Database from the gateway machine, which can prove the connectivity working or not.
    Wednesday, April 6, 2016 4:58 AM

All replies

  • Thanks for the questions. Such error message should be caused by that your Gateway machine cannot access the Azure SQL Server (e.g. caused by firewall setting).

    Your other 2 runs succeeded because (1) the first run doesn't have on-premised data sources and didn't run in your Gateway machine, and (2) the second run didn't connect to the Azure SQL Server. Such issue should be reproed if you copy from on-prems SQL Server to the Azure SQL Server.

    Tuesday, April 5, 2016 2:34 AM
  • This looks like the gateway machine cannot connect to the Azure SQL Database. Please check whether the client IP is in the allowed IP list on Azure SQL Database portal. You could try to use SMSS to connect to the Azure SQL Database from the gateway machine, which can prove the connectivity working or not.
    Wednesday, April 6, 2016 4:58 AM