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

Question

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

Answer 1

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.