Copy Data from View in serverless SQL Pool into adl via Copy data pipeline

Guentner Florian 45 Reputation points
2023-11-28T10:54:37.3366667+00:00

Hello,

I created a linked service to my serverless sql pool and the test connection is working. Then I created a copy data pipeline, which is using a Azure SQL Database - data set. The data set is using the linked service. I can preview the data and it is showing the data correctly. As sink I am using an ADL Gen 2. When I try to run the pipeline, I get following error message. Why do I get an error?User's image

User's image

User's image

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,668 questions
0 comments No comments
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2023-11-28T22:05:51.22+00:00

    @Guentner Florian I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others (Opens in new window or tab)", I'll repost your solution in case you'd like to accept the answer (Opens in new window or tab).

    Error Message:

    • User's image

    Issue:

    • User created a linked service to my serverless sql pool and the test connection is working. Then they created a copy data pipeline, which is using a Azure SQL Database - data set. The data set is using the linked service. they can preview the data and it is showing the data correctly. At sink user is using an ADLS Gen 2. When they run the pipeline, they get above error message.

    Solution:

    • The problem was that user has used Azure Integrated Runtime in the source of the copy activity and in the sink they used a Self-Hosted integration runtime. When they changed the sink to the Azure Integrated runtime it worked.

    The reason for this is, when either of the source or sink linked service uses SHIR, then COpy activity is executed in SHIR for both source and sink. Hence in that case your SHIR should be able to access your source and sink. In case if either of your source or sink has firewall restrictions, then your SHIR won't be able to access unless you grant the SHIR IP address to access your source or sink explicitly.
    User's image

    Here is the related doc for above statement: Copy activity determines which IR to be used

    If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

    I hope this helps others reading this thread!

    If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Amira Bedhiafi 19,616 Reputation points
    2023-11-28T11:34:06.7533333+00:00

    If you are connecting an Azure SQL Instance using Sql server management studio while inside you company's network/firewall, first of all you will need to check if your company's firewall rules allow that or not.

    On the AZURE SQL Server, make sure your IP address is in the allowed list.

    You may need you to tick the checkbox "Allow other Windows Azure services to access this server" in Firewall Rules on your SQL Azure account (this will allow connecting to the db server from your deployed Azure application)

    Check this thread : https://stackoverflow.com/questions/5066347/sql-azure-connection-to-sql-azure-throws-exception

    1 person found this answer helpful.