Share via

Access Azure Synapse Link Data Through PowerBI

Malshini Nissanka 0 Reputation points
2024-04-05T06:36:52.2933333+00:00

We are using Synapse Link to export D365 FnO tables to Azure Datalake. When we are creating the Synapse Link by configuring Synapse workspace, Spark pool and Storage, a LakeDatabase get created in the synapse workspace with the tables. Then I have created a view in a synapse serveless sql pool database using few of the above FnO tables.

There's a requirement to create a live PowerBI dashboard with this view in the serverless SQL pool. I want to grant access to one of the business users so that he can connect this view to PowerBI. I created an SQL user in the serverless SQL pool and granted read access. However, with that user ID, we're getting the below error. I even tried with the SQL admin user and through SSMS and got the same error:

"Cannot find the CREDENTIAL 'https://.dfs.core.windows.net/dataverse/deltalake/dimensionfocusbalance_partitioned', because it does not exist or you do not have permission."

I could connect to the view through PowerBI with my AAD user ID without any issues.

I don't want to give the business user access to the Synapse workspace or the entire data lake storage. I want to make sure he can only access this view.

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,365 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,456 questions
Microsoft Dataverse Training
Microsoft Dataverse Training
Microsoft Dataverse: A Microsoft service that enables secure storage and management of data used by business apps. Previously known as Common Data Service.Training: Instruction to develop new skills.
9 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Harishga 4,160 Reputation points Microsoft Vendor
    2024-04-08T05:21:21.3133333+00:00

    Hi @Malshini Nissanka

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    It seems that the error message "Cannot find the CREDENTIAL 'https://.dfs.core.windows.net/dataverse/deltalake/dimensionfocusbalance_partitioned', because it does not exist or you do not have permission" is related to the credentials required to access the Delta Lake storage where the data for the view is stored.

    When you connected to the view through PowerBI with your AAD user ID, it worked because your user account has the necessary permissions to access the Delta Lake storage. When the business user tried to connect using the SQL user account that you created, they did not have the necessary permissions to access the Delta Lake storage, which resulted in the error message. 

    To grant the business user access to the view without giving them access to the entire data lake storage or the Synapse workspace, you can create a shared access signature (SAS) token with read access to the specific Delta Lake folder that contains the data required for the view. You can then provide this SAS token to the business user to access the data.

    Once you have generated the SAS token, you can provide it to the business user to access the data required for the view. The user can then connect to the view in the Synapse serverless SQL pool database using PowerBI and the SAS token.

    Reference
    https://learn.microsoft.com/en-us/azure/storage/blobs/assign-azure-role-data-access?tabs=portal

    I hope this information helps you. Let me know if you have any further questions or concerns.

    0 comments