question

ctsb avatar image
0 Votes"
ctsb asked PRADEEPCHEEKATLA-MSFT commented

External datasource error - Azure synapse

I am following this article to create external datasource and external tables to read data from Azure data lake storage Gen2.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/tutorial-logical-data-warehouse

Error message is generic is not helping to troubleshoot the access issue

File '/2019/01/data.parquet' cannot be opened because it does not exist or it is used by another process.

My user account and the app identity created by the Synapse have 'Storage Blob Data Contributor' access to the storage account and the storage account also have public access.

azure-synapse-analyticsazure-data-lake-storage
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @BryanWang-3107,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.

0 Votes 0 ·

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
2 Votes"
PRADEEPCHEEKATLA-MSFT answered

Hello @BryanWang-3107,

Welcome to the Microsoft Q&A platform.

If your query fails with the error saying, File cannot be opened because it does not exist or it is used by another process' and you're sure both file exist and it's not used by another process it means serverless SQL pool can't access the file. This problem usually happens because your Azure Active Directory identity doesn't have rights to access the file. By default, serverless SQL pool is trying to access the file using your Azure Active Directory identity.

To resolve this issue, you need to have proper rights to access the file. Easiest way is to grant yourself 'Storage Blob Data Contributor' role on the storage account you're trying to query.

Instead of granting Storage Blob Data Contributor, you can also grant more granular permissions on a subset of files.

All users that need access to some data in this container also need to have the EXECUTE permission on all parent folders up to the root (the container). Learn more about how to set ACLs in Azure Data Lake Storage Gen2.

194128-synapse-filenotfound.gif

Reference: Query fails because file cannot be opened

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.