DataSourceError from PowerBI when trying to access Synapse Serverless View on ADLS2

Question

DataSourceError from PowerBI when trying to access Synapse Serverless View on ADLS2

Hobday, Colin 45

We have an issue with a particular user connecting to our Serverless SQL pool via PowerBI.

I created an Azure Synapse workspace, which contains the built in serverless SQL pool which has some Views set up to pull data from some Delta tables.  As the creator of the DB I can connect to the serverless pool without issue and can model the data from the views in powerBI. However when my colleague attempts to do the same, she can see the names of the views/tables but gets the following error:

DataSource.Error: Microsoft SQL: Content of directory on path '
https://-------.dfs.core.windows.net/engineering/-----/gold/g_estimate_details/_delta_log/*.*'
cannot be listed.
Could not use view or function 'gold.g_estimate_details_el' because of binding errors.
Details:
    DataSourceKind=SQL
    DataSourcePath=----------synapse-ondemand.sql.azuresynapse.net;aegaeon
    Message=Content of directory on path '
https://---------.dfs.core.windows.net/engineering/--------/gold/g_estimate_details/_delta_log/*.*'
cannot be listed.
Could not use view or function 'gold.g_estimate_details_el' because of binding errors.
    ErrorCode=-2146232060
    Number=13807
    Class=16

I have granted references on a database scoped credential to this user.  The credential is a shared access signature with read permission on the ADLS2 container.  

I have also tried to use Storage Explorer to grant and propogate ACLs to the subfolders, but it doesn't work.

Accepted answer

1 additional answer

Your answer

Answer 1

@Hobday, Colin - Thanks for the question and using MS Q&A paltform.
It seems like your colleague is facing an issue while accessing the Serverless SQL pool via PowerBI. The error message suggests that the content of the directory on the path cannot be listed. This could be due to a binding error.

To resolve this issue, you can try the following steps:

Check if the user has the necessary permissions to access the ADLS2 container. You mentioned that you have granted references on a database scoped credential to this user. Please ensure that the credential has the necessary permissions to access the ADLS2 container.
Check if the user has the necessary permissions to list the contents of the directory on the path. You can try granting the user the necessary permissions to list the contents of the directory on the path.
Check if the user has the necessary permissions to use the view or function 'gold.g_estimate_details_el'. You can try granting the user the necessary permissions to use the view or function.
Check if the user has the necessary permissions to propagate ACLs to the subfolders. You can try granting the user the necessary permissions to propagate ACLs to the subfolders.

If the issue persists, please provide more information about the environment and the steps you have taken so far to troubleshoot the issue.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Hobday, Colin 45 Reputation points

2024-08-06T10:02:36.0666667+00:00

The user was able to have access after I used managed identities. I had to drop and create the external data source with the new credential.
PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2024-08-06T10:35:46.3933333+00:00

@Hobday, Colin - Glad to know your issue has been resolved. Please do continue to use MS Q&A platform for any question related to Azure!

Answer 2

Nandan Hegde 36,146 MVP Volunteer Moderator

User's image

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002

Hobday, Colin 45 Reputation points

2024-08-01T00:17:50.83+00:00

Using Storage Blob Data Contributor role for the whole storage account is too wide an access. I want to have fine-grained access control down to table level, without giving an IAM role to the user which can be used to access the storage account directly.

Hobday, Colin 45

Using the Storage Blob Data Contributor role to the storage account is too wide of an access.  I want to control access down to the table level, such that access is only granted through Synapse.  Otherwise, the user can potentially access the storage account through other means, and there is a risk of data corruption.

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2024-08-03T04:21:55.78+00:00
@Hobday, Colin - You can use Azure Synapse Analytics managed private endpoints to control access to your Synapse workspace and the underlying storage account. With managed private endpoints, you can restrict access to your Synapse workspace and the underlying storage account to a specific virtual network and subnet. This way, you can ensure that access to your Synapse workspace and the underlying storage account is only granted through Synapse.

To control access down to the table level, you can use Synapse workspace managed identities and Azure Data Lake Storage (ADLS) access control lists (ACLs). You can create a managed identity for your Synapse workspace and grant it the necessary permissions to access the ADLS account. Then, you can use ADLS ACLs to control access to specific tables or folders within the ADLS account.

Here are the high-level steps to implement this solution:

Create a managed identity for your Synapse workspace. Grant the managed identity the necessary permissions to access the ADLS account. Create ADLS ACLs to control access to specific tables or folders within the ADLS account. Use Synapse workspace managed identities to access the ADLS account and enforce the ADLS ACLs.

For more information on how to implement this solution, please refer to the following documentation:

Synapse Managed private endpoints

Managed identity for Azure Synapse

Hope this helps. Do let us know if you any further queries.

Share via

DataSourceError from PowerBI when trying to access Synapse Serverless View on ADLS2

1 additional answer

Your answer