ADLS Gen 2 with Power bi

Question

ADLS Gen 2 with Power bi

Anusha S Raj 21

I own a shared storage account. An end-user is trying to access a delta table present in ADLS from Powerbi. But facing few issues.
I see there are only two possible ways to connect to ADLS Gen2 from PBi.
1.One is Access Keys. But, i do not want to share it with any other members as this can provide them access to our entire storage.
2.The other is organizational Account in which the user id that they are using to login should also have read access to the entire storage.

Please correct me if I am wrong. In both cases, they will receive the read access to the entire storage account which is not at all feasible. Also is there any other way I can just provide the end-user read access to that one table and they should only be able to connect to only this table from pbi ?

I was as well exploring the azure databricks connector that is available to see if i can access the delta tables present in adls. But seems like that also has a limitation. I am getting the below error.

DataSource.Error: ODBC: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'Error getting columns: Invalid configuration value detected for fs.azure.account.key'.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-05-27T18:06:59.467+00:00

Hello @Anusha S Raj ,

Just checking in to see if my previous response was helpful. And, if you have any further query do let us know.
Anusha S Raj 21 Reputation points

2022-05-30T07:21:47.187+00:00

Hi @KranthiPakala-MSFT ,

Thanks for your response. All the answers were helpful in solving my doubts.

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-05-27T18:06:59.467+00:00

Hello @Anusha S Raj ,

Just checking in to see if my previous response was helpful. And, if you have any further query do let us know.
Anusha S Raj 21 Reputation points

2022-05-30T07:21:47.187+00:00

Hi @KranthiPakala-MSFT ,

Thanks for your response. All the answers were helpful in solving my doubts.

Answer 1

Hello @Anusha S Raj ,

Welcome to the Microsoft Q&A platform and thanks for posting your query.

Have you got a chance to look at this article: Azure Databricks - Access Azure Data Lake Storage Gen2 using OAuth 2.0 with an Azure service principal
The above article explains how to connect to ADLS gen2 from Azure Databricks using Service principal and when you use service principal authentication, you can associate that security principal with an access level for files and directories. Each association is captured as an entry in an access control list (ACL). Each file and directory in your storage account has an access control list. When a security principal attempts an operation on a file or directory, An ACL check determines whether that security principal (user, group, service principal, or managed identity) has the correct permission level to perform the operation.

I would recommend looking at this document which has detailed info about the ACL's in ADLS Gen2 and help avoid other authorization mechanisms like Shared Keys, SAS or RBACs.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-05-26T23:21:34.06+00:00

Continuation to above comment......

As the official doc says the allowed authentication methods as Organizational sign in or Access Key, your approach with ADB seems possible to achieve your requirement.
Basically, you can import the ADLS Gen2 data as tables (only the one you would want to give access to your end user) into ADB using service principal auth as mentioned in the doc I referenced in above comment and then you can use ADB connector in PowerBi to establish connection to that particular cluster in which your ADLS gen2 data tables exist and import that data into powerBI as described in this video by a community volunteer: How to import data from Azure Databricks to Power BI Desktop

Also please be aware of these limitation with Power Query Online & ADLS Gen2:

Hope this clarifies. Do let us know if you have further query.

Share via

ADLS Gen 2 with Power bi

1 answer

Your answer