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

Question

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?

Answer 1

@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:

• 

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.

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.

Answer 2

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