Cannot acces Tabels in Dataflow Gen2 from Excel

[QooT]

Hi,

I have an Excel File with connection to Dataflow Gen2, query as simple as just load table data.
let
Quelle = PowerPlatform.Dataflows(null),
Workspaces = Quelle{[Id="Workspaces"]}[Data],
Workspace = Workspaces{[workspaceId="xxxxxxxxx"]}[Data],
Dataflow = Workspace{[dataflowId="xxxxxxxxxxx"]}[Data],
Table = Dataflow{[entity="TableName",version=""]}[Data]
in
Table

I am Admin and its working fine for me, but User gets following error 

When going step by step up to table level all is visible and accesible, but when trying to get into table data error pop ups.
User have a Memebr accees on whole Workspace.
With exactly identical Gen1 Dataflow all works as a charm.

What I am missing?

[QooT · Accepted solution]

Problem solved.

Reason was firewall rule wchich was blocking connection to SQL.

It seems that behind the scenes connection to dataflow Gen2 is turned on the fly into connection to the staging SQL database.

[v-veshwara-msft]

Hi @QooT ,

Thanks for confirming and good to know the issue is resolved.

Even though the user had the correct workspace permissions, network-level restrictions like firewall rules can still block access when trying to load table data from Dataflow Gen2 in Excel.

Thanks again for the update, this will definitely help others encountering similar behavior. Could you please mark your reply as Accepted Solution to help others with similar issues.

Also thanks to @MasonMA for your valuable inputs.

Please reach out for further assistance.

Thank you.

[MasonMA]

HI @QooT 

This might mean you would need to recheck the data source permission for SQL Server that your Dataflow Gen2 is trying to connect to. A couple of points you can look at, 

1. Gateway Configuration:

If the SQL Server connection in the Dataflow Gen2 is using an on-premises data gateway, ensure the user has access to the gateway and the data source in it.

2. SQL Server Permissions:

Check if the SQL Server is using Windows authentication or SQL authentication, and whether the user’s credentials are valid.

3. Credential Settings in Power BI Service:

Under Data source credentials in Dataflow Gen2 settings , check if the authentication method is set to organizational account and whether the user has signed in.

Hope this helps:)