Error details local_sql_db_2_azure_sql_db Error code SqlFailedToConnect Failure typeUser configuration issue DetailsErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '', Database: 'db', User: 'admin'. 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=Login failed for user 'admin'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=18456,Class=14,ErrorCode=-2146232060,State=1,Errors=[{Class=14,Number=18456,State=1,Message=Login failed for user 'admin'.,},],' Activity ID45f7cb05-ccbe-41ba-a0bd-bf27a8e581bb Added Firewall rule too

Based on your question, your source seems to be an onpremises database. So you must be using a self hosted IR. Can you confirm whether you are able to access the Azure SQL database from the server on which the SHIR is hosted? Because whenever a SHIR is being used either in source or sink of a copy activity, the SHIR acts as a bridge between the source and the sink; so both the source and the sink must be accessible from within the server hosting the SHIR for the copy activity to be a success

ADF Copy Data from on-premises SQL server to Azure SQL server

Accepted answer

Nandan Hegde 30,716 Reputation points MVP

2024-06-13T11:08:03.1333333+00:00

Based on your question, your source seems to be an onpremises database.

So you must be using a self hosted IR.

Can you confirm whether you are able to access the Azure SQL database from the server on which the SHIR is hosted?

Because whenever a SHIR is being used either in source or sink of a copy activity, the SHIR acts as a bridge between the source and the sink; so both the source and the sink must be accessible from within the server hosting the SHIR for the copy activity to be a success
Please sign in to rate this answer.
Manoj Kumar 20 Reputation points

2024-06-13T11:11:14.1533333+00:00

Yes I can access infect I am able to run a pipeline in which using SHIR I can copy on-premises sql server table to Azure datalake storage

Manoj Kumar 20 Reputation points

2024-06-13T11:16:00.23+00:00

It feels like SHIR unable to connect Azure SQL database, while running Pipeline, but I can connect with SHIR on premises window

Nandan Hegde 30,716 Reputation points MVP

2024-06-13T12:44:06.3+00:00

In the screenshot I see the username as SQLadmin whereas in the error message it is admin.
Can you confirm below 2 aspects:

you are able to access both the onprem database and Azure SQL database from the SHIR server

The credentials that you have inputted to access Azure SQL database linked service is correct?

Manoj Kumar 20 Reputation points

2024-06-13T12:46:13.0366667+00:00

yaa both are valid users I tried with sqladmin to issue persist

Manoj Kumar 20 Reputation points

2024-06-13T12:47:48.9666667+00:00

Operation on target local_sql_db_2_azure_sql_db failed: ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '', Database: 'db', User: 'sqladmin'. 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=Login failed for user 'sqladmin'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=18456,Class=14,ErrorCode=-2146232060,State=1,Errors=[{Class=14,Number=18456,State=1,Message=Login failed for user 'sqladmin'.,},],'

Nandan Hegde 30,716 Reputation points MVP

2024-06-13T13:35:54.96+00:00

Can you provide the screenshot of your Azure SQL database linked service?
There is a version component in that linked service and can you confirm which one is your mapped to ?

If recommended, can you switch to legacy and test out your copy activity

Manoj Kumar 20 Reputation points

2024-06-13T13:48:57.39+00:00

Thanks a lot, I am just curious to know why it didn't work with Recommended :)
Sign in to comment

Share via

ADF Copy Data from on-premises SQL server to Azure SQL server

0 additional answers