How to use credential for DirectQuery chaining without giving end-users access to upstream data?

[julsr]

Hi everyone,

I'm trying to implement a secure chained dataset architecture in Power BI (Premium Per User) and running into a limitation I can't seem to solve. Here's the scenario:

Architecture

• Upstream dataset: Published in Workspace A (Premium Per User) Contains the full/raw data (many columns, sensitive structure)
• Downstream dataset: Published in Workspace B (Premium Per User) Connects to the upstream dataset via DirectQuery Applies transformations: removes sensitive columns, renames fields, adds business measures, etc. Goal: End-users should only see the curated/cleaned version from the downstream model

Security goal I want regular business users to:

• Have access to reports/dashboards built on the downstream model
• Not have any permission (Viewer, Read, Build, etc.) on the upstream dataset or Workspace A

Current limitation When using DirectQuery chaining:

• The end-user's credentials are used to query the upstream dataset
• This means users need at least Build (or Read) permission on the upstream dataset for DirectQuery to work
• If I give them Build/Read on upstream: they can connect directly to the upstream dataset (bypassing downstream) and see all original columns/tables (including the ones I intentionally hid/renamed in downstream)

Question Is there any way to:

• Use a Service Principal (or any fixed identity) for DirectQuery queries from downstream to upstream?
• Allow downstream to return data to end-users who have zero permissions on the upstream dataset?
• Or any other secure pattern that lets me hide upstream columns while keeping DirectQuery / live data?

Thanks in advance for any ideas, links to documentation, or alternative architectures!

[v-veshwara-msft · Accepted solution]

Hi @julsr ,
Thanks for reaching out to Microsoft Fabric Community.

The behavior you are seeing is expected.

With DirectQuery chaining in Power BI, queries from a downstream semantic model are always executed using the identity of the report viewer. Because of this, every user consuming the downstream model must have at least Read permission on the upstream dataset.

Reference:

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

It is not possible to use a service principal, managed identity, or fixed credential for DirectQuery queries between semantic models, and a downstream model cannot return data to users who have no permissions on the upstream model. Chained semantic models are not designed to act as a security boundary or proxy.

Additionally, hiding or renaming columns in the downstream model does not secure them in this architecture, since users with permission on the upstream dataset can still access it directly.

Reference: Working with a composite model based on a semantic model 

To securely limit column visibility while keeping data live, security must be enforced below the semantic model layer, such as at the data source itself (for example using database level row or column level security), or by materializing a curated data layer and exposing only that curated data through a single semantic model. Chained semantic models are not designed to propagate security rules.
Reference: Working with a composite model based on a semantic model 

Hope this helps. Please reach out for further assistance.
Thank you.

[v-veshwara-msft]

Hi @julsr ,

Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.

Thank you.

[julsr]

Hi! Yes, it did, thank you! The solution was to convert my chained semantic model into an isolated one. Instead of having one model pulling from the database into Power BI, I now have two separate pulls. This way, we ensure better data protection and availability.