ISSUE+SOLUTION::published report Fabric datasource not accessible by admin user who created all

[Element115]

@Greg_Deckler (thought you might not be amused by the following disaster ;-))

Power BI Desktop (PBID) Version: 2.126.927.0 64-bit (February 2024)

Fabric Trial mode

user has PPU license and is the workspace admin with all rights

ISSUE:

0__enable Fabric trial mode (FTM)

1__create a workspace (WS)

2__create a lakehouse (LH)

3__create dataflows gen 2 (DFg2) and populate the LH

4__create a report with PBID using DirectQuery mode to connect to the lakehouse SQL analytics endpoint

5__publish the report to the PBI Service

6__with the same user, ie the master user who has all rights, open the published report

7__the following error message appears:

8__click 'Show details' and this dialog appears:

Note that the dataset owner is the user that created everything and is also the admin for the workspace but not for the PBI Service. So right there something is not as it should since the creator of the workspace, the lakehouse, the multiple dataflows, and the report, should have access to all these artifacts since he created them in the first place! The dataset owner name in the screenshot is that user, so in other words the system is asking the user to update the creds he should already hold! If I am the only one confused by this, then just shoot me.

9__Then, a long winding trip through the online Microsoft documentation ensued and none of the instructions matched exactly the UI paths currently in Fabric. At least there are so many word mismatches, the user is left guessing. Eventually, the long and short of it is this:

10__go back to the workspace list view and locate the semantic model; click on the ... menu and select Settings:

11__you are presented with this page and dialog:

12__when I tried all authentication methods, except the Service principal (as I have no idea what it is), none worked; of course, the first one to try is Basic authentication as that normally should just pick up the already existing creds for the admin user since said user is already logged in the Service/Fabric, but no.

SOLUTION:

Somehow, by some miracle, you need to figure out that in all the 1,000s of pages of Microsoft documentation, this is the page that contains the hidden key you need to unlock the datasource:

https://learn.microsoft.com/en-us/power-bi/enterprise/directlake-fixed-identity

And in step 3, for Authentication method, you need to select OAuth 2.0 and then the admin user regains access to the datasource he himself created in the first place!!!  Also, why it is specifically stated not to select SSO via Azure AD for DirectQuery queries, when the whole model is in DirectQuery mode to the lakehouse is beyond me.  

This total mess definitely qualifies as the most epic WTF moment of the year so far.  I am seriously not amused. 

[v-xuxinyi-msft · Accepted solution]

Hi @Element115 

Can you elaborate on your situation? For example, what is the data source you are using, would help me to more accurately reproduce the problem you are experiencing. Thank you for your time and efforts in advance.

Best Regards,
Yulia Xu

[Element115]

case #2402280040011813

Apparently this is the reason:

•	The Back-End cluster determines how authenticated clients interact with the Power BI service. The Back-End cluster manages visualizations, user dashboards, semantic models, reports, data storage, data connections, data refresh, and other aspects of interacting with the Power BI service. The Gateway Role acts as a gateway between user requests and the Power BI service. Users don't interact directly with any roles other than the Gateway Role. Azure API Management eventually handles the Gateway Role.

•	Below is the document for the reference Power bi Security.

Power BI Security - Power BI | Microsoft Learn

Also, OAuth2 is the authentication method to use because, according to the SE: 

If you are using on-prem datasource you will get option for windows and basic.
If you are using cloud will be Oauth2.

Because you are using lakehouse table and is since not an On-Prem this not authenticating you to use basic or windows.

In other words, even though Basic and Windows authentication methods wil be available they won't work because of the type of data source, and in this case, since the data source is in the cloud, only OAuth2 is the way to go.

Perhaps then the other authentication methods shouldn't be displayed since they won't work anyway. Causes unwarranted confusion in the UX.

[Element115]

Nomenclature: 

DFg2 = dataflow gen2

PBID = Power BI Desktop

PBIS = Power BI Service

Here is the ETL chain--it is the same user creating everything from A to Z, and this user is the master user with admin and all rights on all the workspaces in the PBIS:

on-prem SQL Server-->1-DFg2-->2-DFg2-->LH-->PBID-->PBIS

after publishing to PBIS, and when opening the report in Fabric/Service, that's when the issue occurs.