Copy SAP hana table into Azure SQL database and get SQL Server can't be found

Question

I validated data factory and both source dataset and target dataset looks good and validated but as soon as I run pipeline and job hang on there and the error message as below.

Operation on target Copy data1 failed:
ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Server database: 'tcp:mysqlserver2002.database.windows.net,1433', Database: 'mySampleDatabase', User: 'system'. Check the linked service configuration is correct, and make sure the SQL Server 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

Any idea and help will be appreciated

Thanks,

Answer 1

According you error message, the error happened in Source dataset, Data Factory don't have the permission to access the Source SQL database "mysqlserver2002.database.windows.net".

Please test the connection to the Source Azure SQL database like this:

If the connection failed, please check if you have opened the Allow Azure services and resources to access this server on Azrue SQL database firewall settings:

According my experience for this error, here are the solutions:

1. Set Allow Azure services and resources to access this server to yes
2. Add the client ip(which we could get from the error message) to the database firewall.

Another way, please Add a role assignment to Data Factory, then it will permission to access the database:

Hope this helps.