External datasource error - Azure synapse

Question

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

https://learn.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.

Answer 1

Hello @Bryan Wang ,

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.

• Visit full guide on Azure Active Directory access control for storage for more information.
• Visit Control storage account access for serverless SQL pool in Azure Synapse Analytics

Alternative to Storage Blob Data Contributor role:

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.

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 or upvote 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