Hello I synced Azure Synapse Link to Dataverse in powerapps which creates External Tables/Data Source/ File Format in my Storage Gen 2 account container. However, I ran into an issue running a simple query with one of the external tables in Synapse Workspace. -I need help identifying the error below: " External table 'name of table' is not accessible because content of directory cannot be listed. " The permissions in my storage account are: -Contributor -Storage Blob Data Owner -Storage Blob Table Data Contributor -I am also a Synapse Administrator. -Firewalls setting is set to "Enabled from all networks". -Also have a Serverless SQL pool. Thank you

Hello anonymous user , Welcome to the MS Q&A platform. If you are using the Synapse link for Dataverse to read the linked DataVerse tables, you need to use the Azure AD account to access the linked data using the serverless SQL pool. For more information, see Azure Synapse Link for Dataverse with Azure Data Lake. If you try to use a SQL login to read an external table that is referencing the DataVerse table, you will get the following error: External table 'table_name' is not accessible because content of directory cannot be listed. Dataverse external tables always use Azure AD passthrough authentication. You can't configure them to use a shared access signature key or workspace managed identity. Please see the below document explaining this error. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002#content-of-dataverse-table-cant-be-listed ------------------------------ 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

Azure Synapse Link Dataverse External Table issue

Accepted answer

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-08-26T18:11:09.8+00:00
Hello anonymous user,

Welcome to the MS Q&A platform.

If you are using the Synapse link for Dataverse to read the linked DataVerse tables, you need to use the Azure AD account to access the linked data using the serverless SQL pool. For more information, see Azure Synapse Link for Dataverse with Azure Data Lake.

If you try to use a SQL login to read an external table that is referencing the DataVerse table, you will get the following error:

External table 'table_name' is not accessible because content of directory cannot be listed.

Dataverse external tables always use Azure AD passthrough authentication. You can't configure them to use a shared access signature key or workspace managed identity.

Please see the below document explaining this error.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002#content-of-dataverse-table-cant-be-listed

------------------------------

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
Please sign in to rate this answer.
Seishu Morales 21 Reputation points

2022-08-26T19:42:07.573+00:00

Hi @Bhargava-MSFT ,

I hope all is well and thank you for the feedback. Unfortunately, even when signing with Azure AD account with MFA, I am unable to query with the same error. The person who initiated the sync from Dataverse to ASL was a global admin.

I have permissions to add additional tables and also set-up the Storage Acc Gen 2 and Synapse Works with admin permissions.

Is there any additional permissions I may need? Thank you

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-08-26T22:44:41.137+00:00

Hello anonymous user,
Are you using Synapse Studio for your query? Can you please connect to the SQL serverless end point using the SQL management studio(SSMS) and see if you see the same error message?
I just wanted you to check whether the error message is the same from both Synapse and SSMS.

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-08-27T05:14:36.69+00:00

Hello anonymous user,

Prerequisites for role access:

On the storage account, you need to have

1) Owner
2) Storage Blob Data Contributor- access roles

on the synapse workspace, you need to have

Synapse Administrator role access within the Synapse Studio.

here is the document explaining the prerequisites.

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-synapse#prerequisites

Looks like you are missing both roles on your storage account. Could you please provide both roles and see if it helps?

Seishu Morales 21 Reputation points

2022-08-29T13:17:32.087+00:00

Good Morning @Bhargava-MSFT ,
Thank you for the feedback.

With your first reply, when connecting to Synapse Studio and SSMS, I do receive the same error message when I am attempting to query. When I go to my Azure Storage Gen 2 Account, I have visibility to view the data (csv files) from the tables that's synced to Dataverse.

For the storage account roles, I will attempt to have someone alter my roles and get back to you. Another question, even though it's asked for Storage Blob Data Contributor, wouldn't Storage Blob Data Owner be a higher role?

Thank you for your time assisting me.

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-08-29T22:57:59.493+00:00

Hello anonymous user,
Thank you for the details.
Yes, Storage blob data contributor allows full access to Azure Storage blob containers and data. Initially, I thought the Storage blob data owner would be sufficient to access the table's data from the storage.
But As per the prerequisites mentioned in the document, you will need to have Owner and "Storage blob data contributor" permissions.
Suppose you can get owner and "Storage blob data contributor" access and see if it resolves the access issue. Please let me know if you still have issues with these elevated permissions.

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-synapse#prerequisites

Seishu Morales 21 Reputation points

2022-09-01T13:55:12.72+00:00

Hi @Bhargava-MSFT ,
I hope all is well. I wanted to reach out and let you know that setting permission as Storage Blob Data Contributor seems to have worked when creating queries. Seems a minor issue is that it's not pulling current data from lake database but that's for a separate ticket I can create if I can't resolve it. I really appreciate your time assisting me.

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-09-01T15:37:08.233+00:00

Hello anonymous user,
Thank you so much for the update.
Sign in to comment

Share via

Azure Synapse Link Dataverse External Table issue

0 additional answers