Synapse Link for Dataverse can't be accessed in pipelines

Jérôme Po 1

Hi,

we setup the link between dataverse and synapse, sync our table.
fine, working.
I can run SQL statements in Azure Synapse.
but as soon as I try to connect a pipeline activity, like a simple copy, I can't.
I have this error:
External table '???' is not accessible because content of directory cannot be listed.
( 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 )

I tried everything possible, like direct access to the CSV files or creating external table in a serverless pool to the same files as used by the data lake, no change.

so how I can connect a pipeline to the data lake and execute the activities I want?

Do I have to grant sepcific accesses in dataverse to a service account???

there is absolutely no documentation on this problem anywhere.

thanks.

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-07-21T14:05:31.533+00:00

Hi @Jérôme Po ,

Thank you for posting query in Microsoft Q&A Platform.

Usually above mentioned(In the questions) error will come when you try to use a SQL Login to read external table that is referencing the DataVerse table.

Please Note, 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.

So make sure to use the Azure AD passthrough authentication to avoid this error. Click here to know more about this error in documentation.

Hope this helps. Please let us know how it goes.
Jérôme Po 1 Reputation point

2022-07-21T14:36:08.757+00:00

Thanks,

I found this article about the pass-trough constraint...
but no way there is a description on what to do to solve this problem.

Is it just activating the pass through option in the AAD?
or some additional config required?

the problem appear toi be related to the System Assigned Managed Identity which is the one I use to connect to the data in pipelines.
do we have to use another type of connection for the pipelines?

nowhere I can see any demonstration of how to use pipelines against dataverse linked stuff, only direct SQL access from a user.
I'm disappointed by all these restrictions and issues.
ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-07-22T08:45:30.397+00:00

Hi @Jérôme Po ,

If my assumption is correct. You mentioned as you tried to connect pipeline activity like Copy activity to Dataverse. That means you might have created a dataset/linked service for Dataverse. There you might have used authentication mechanism like Managed identity or any other. And since all these authentication mechanisms are not supported to read external table that is referencing Dataverse table, you are ending up with this issue.

AAD pass though authentication means only user IDs which has access can be used to query or fetch data. I guess linked service may not have option of using AAD pass through authentication. Hence that route of implementation may not work here. We may need to depend of Synapse link only.

If all my above understanding is correct, then feel free to raise product feedback at below link. Product team closely monitors feedbacks and consider them for future implementations.
https://feedback.azure.com/d365community/forum/9b9ba8e4-0825-ec11-b6e6-000d3a4f07b8
Jérôme Po 1 Reputation point

2022-07-22T12:32:53.03+00:00

your comment is exactly my case.
is there a way to create a connection on my credential (instead of the system user managed or integrated sql login)?

this may solve the problem(?)
ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-08-01T09:44:42.543+00:00

I think thats not possible at this moment. Please feel free to submit feedback on feedbacks portal and up-vote. PG monitor feedbacks there and consider for future releases.
https://feedback.azure.com/d365community/forum/9b9ba8e4-0825-ec11-b6e6-000d3a4f07b8