Share via

Azure SQL Database not accepting insert from On Prem Database through Azure Datafactory

Keren Osler 60 Reputation points
2024-04-02T09:28:34.0733333+00:00

I have an on prem database that I am accessing through a self hosted integrated run time.
I am able to preview the data in azure data factory.
I am trying to copy the data from the on prem database to an azure sql database. When I do this, I am getting permission errors on the sink database (Azure sql db). But if I change only the source (keeping the exact same sink that I am getting permission errors on) to another database it works. So I suspect the error is on the on prem source database even though it specifically says sink. The table does not currently exist in the source but I have auto create options on. When the source is not the on prem db the table gets successfully created in the sink db. I initially thought it might be a size problem so I restricted the copy to be 1 row of data and the table is 5 columns long but I still get the exact same error.

To add to this weirdness - I can push the data from the source to blob storage. So it seems that the on prem source db connection is working just specifically not to the database. Do you have any idea what could be the cause of this?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
Accepted answer
  1. ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee
    2024-04-02T16:56:26.4033333+00:00

    Hi Keren Osler,

    Thank you for posting query in Microsoft Q&A Platform.
    When we use selfhosted IR in copy activity either for source or sink, in that case ADF automatically, considers using Selfhosted IR to copy data to sink also. In your case, selfhosted IR is the one which is trying to copy data to Azure SQL as well. And on Azure SQL side some permission issues it might faced.

    Consider creating a linked service with selfhosted IR for your sink as well and use the dataset accordingly in sink tab of copy activity.

    Hope this helps. Please let me know how it goes. Thank you.

    Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 20,176 Reputation points
    2024-04-02T11:20:20.8833333+00:00

    In the copy activity, if either the source or the sink utilizes an Integration Runtime (IR), it's essential that both the source and sink are accessible from the server where the IR is hosted.

    For troubleshooting, you should log into the server that hosts the IR and verify if you can access the Azure Database from that server. If access is denied, ensure to whitelist the server's IP address and adjust the necessary port settings accordingly.

    Check old threads :

    https://learn.microsoft.com/en-us/answers/questions/858230/adf-copy-cannot-find-server-but-linked-service-and

    https://stackoverflow.com/questions/72515213/unable-to-insert-data-into-azure-sql-database-from-on-premises-sql-database-in-a