Azure data factory problem to write data from a sql Server on prem to Azure SQL Database

1
votes

I am trying to retrieve data from a table located in a sql server database on an on-premise server. I would like to inject this data into an Azure SQL Database. I set up the runtime integration. By using data factory, i am able to retrieve data from a table on my on-premises server and have it copied to another table on the same on-premises server. I also manage to copy the data from one azure database table to another azure database table. On the other hand, not from my on premise table to azure database. I have the following message :

Operation on target Copy data' failed: connect to SQL Database: Database: 'XXXXX', User: adminXXX'. Check linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to ... erreur liée au réseau ou spécifique à l'instance s'est produite lors de rétablissement dune connexion à SQL Server, Le serveur est introuvable Ou n'est pas accessible, Vérifiez que le de l'instance est correct et que SQL Server est configuré pour autoriser les ....

.Net SqlClient Data Provider, SQLErrorNumber=10060, Class= 20, ErrorCode=-2146232060,State=0

I can't find an explanation, the firewall seems to be well configured. An idea?

1
azureazure-data-factoryazure-data-factory-pipeline
Hey @didou000, Did it answer your query? – Nandan
Hey @Nandan thanks a lot it's working now. – Didou000

1 Answers

2
votes

Since your source is an On prem SQL server, you would be using an Integration run time. So in ADF in case if an IR is used either source or sink, it is required that the IR should be able to access the other part as well.

Based on the error message , it is clear that the Azure SQL database is not accessible from the IR. So you would have to open the Port TCP 1433 in the IR VM .

How to test: Login into the IR VM and try accessing the Azure SQL database either via SSMS or PowerBI or portal