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

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

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 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.
2,654 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.
4,328 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,456 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. BhargavaGunnam-MSFT 25,876 Reputation points Microsoft Employee
    2023-01-20T19:49:15.5033333+00:00

    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.