Trouble with Azure Synapse: pipeline cannot execute a stored procedure that works in Develop script

asked 2023-01-19T19:03:45.9066667+00:00
Mateo Estrada Bazan 71 Reputation points

Hello!

I will give some context regarding our inconvenience in Azure Synapse:

  • We created a Stored Procedure (it creates a VIEW which reads all the parquet files in a certain folder) on a Develop script, and it ran successfully.
  • We also created the VIEW manually, also successfully, in a database created in Serverless SQL Pool.
  • The container where the data is currently has Private Access Level.
  • My user has Storage blob data contributor.
  • Moving on to the Azure Synapse pipeline, we can use Copy Data to get new files inserted into the Container (ADLS Gen2)
  • When we want to run everything on Integrate (Synapse pipeline) an error pops up:
   {    "errorCode": "2402",    "message": "Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 13807. Error Message: Content of directory on path 'https://xxx.blob.core.windows.net/data/folder/*.parquet' cannot be listed.

 

If we switch the Container's Access level to public, everything works smoothly, but we want to keep it Private.
Is there anything else we should do in order to make our Synapse pipeline work correctly? Any additional permissions setup or else?
Thank you so much in advance. Regards, Mateo

Azure Active Directory
Azure Active Directory
An Azure enterprise identity service that provides single sign-on and multi-factor authentication.
12,606 questions
Azure SQL Database
Azure Storage Accounts
Azure Storage Accounts
Globally unique resources that provide access to data management services and serve as the parent namespace for the services.
1,545 questions
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.
2,605 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,268 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2023-01-20T19:49:15.5033333+00:00
    BhargavaGunnam-MSFT 7,406 Reputation points Microsoft Employee

    Hello @Mateo Estrada Bazan,

    Welcome to the MS Q&A platform.

    As per the error, the synapse pipeline is unable to access the parquet file in the Gen2 directory.

    As your storage is firewall-protected you need to create MSI on the SQL end.

    Your scenario has been documented in the below URL. Please review this article and let us know if you have any further questions.

    https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/synapse-studio-error-while-trying-to-read-data-from-storage/ba-p/1511965

    A similar issue has been discussed here

    I hope this helps.