Direct Lake on SQL Endpoint + OneLake Security: A Field Guide to Delegated vs. User Identity Mode (Part 1)

How a single "You don't have permission to view the content of Direct Lake table" error sent me down a rabbit hole — and everything I learned about securing cross-lakehouse shortcuts, native tables, and SPN-owned semantic models in Microsoft Fabric.

TL;DR

• In Direct Lake on SQL Endpoint, owning the model with a service principal does not make it the data reader when SSO is on — the viewer's identity is what gets checked.
• Delegated identity mode = table access is SQL-governed (GRANT SELECT, SQL-RLS/CLS). OneLake security roles are ignored.
• User identity mode (the new default) = table access is governed by OneLake security roles; SQL table grants are blocked.
• With mixed sources (a shortcut table + native tables in the same lakehouse), delegated mode uses one SQL surface, while user identity mode uses two authorization surfaces.
• The redesigned SQL-Endpoint sync relaxes the strict one-to-one Object-ID rule and improves nested-group/SPN support — but verify on your tenant, because Microsoft Learn still documents the strict behavior.

The setup

The architecture is a common hub-and-spoke / producer–consumer pattern:

Producer Lakehouse (physical Delta table: gold.daily_taxi_summary)
        │  internal OneLake shortcut
        ▼
Consumer Lakehouse + SQL Analytics Endpoint
        │  Direct Lake on SQL EP
        ▼
Semantic Model  (owner = Service Principal, SSO on)
        │  Build
        ▼
Power BI App  →  Report Viewer

Two Entra security groups are involved:

Group Plane Purpose

The error that started it all

Error fetching data for this visual
You don't have permission to view the content of Direct Lake table.
Underlying Error: QueryUserError

The trap: the semantic model was owned by a service principal, so surely the SPN reads the data? No.

Key principle #1 — model ownership ≠ data access

With SSO on, Direct Lake reads OneLake using the report viewer's identity — not the model owner. The SPN owns and refreshes the model, but at query time it's the signed-in user whose permissions are evaluated. Owning the model does nothing for the read path.

So the error meant the viewer lacked data-plane access — not the SPN.

Delegated identity mode

Governing rule: table access is SQL-governed (GRANT/REVOKE, SQL-RLS/CLS/DDM). The shortcut hop to the Producer uses the item owner's identity (the Consumer Lakehouse owner). OneLake security roles are ignored on the SQL EP path.

Permission matrix (delegated)

Level Principal Grant / Why

Key principle #2 — SELECT can be inherited via a group

In my case the viewer succeeded without an explicit grant on SD-Fabric-ConsumerAnalyst-Viewer, because the viewer was also a member of SD-DataAccess_Master_DataSA-RO, which held GRANT SELECT. SQL honors Entra group grants, so the SELECT was inherited through group membership.

[[ IMAGE: delegated-mode.png — Delegated mode: table access is SQL-governed. The viewer inherits GRANT SELECT through the SD-DataAccess_Master_DataSA-RO group; OneLake roles are ignored. ]]

SQL grant / rollback

-- On the Consumer LH SQL analytics endpoint
GRANT SELECT ON [gold].[daily_taxi_summary] TO [SD-DataAccess_Master_DataSA-RO];

-- Rollback
REVOKE SELECT ON [gold].[daily_taxi_summary] FROM [SD-DataAccess_Master_DataSA-RO];

⚠️Grant on the Consumer EP, never the Producer. If you run it on the Producer by mistake, REVOKE it on that same (Producer) endpoint.

Then Microsoft changed the default

From the New OneLake security improvements for Microsoft Fabric announcement:

"We've completely redesigned how SQL Analytics Endpoint integrates with OneLake security. The new design has lower latency, works better with nested security groups, and solves existing limitations with service principals. As part of this change, the default mode for SQL Analytics Endpoint's is now User's identity mode."

This reframes everything.

Contd in part 2...

GitHub Link: fabric-field-notes/direct-lake-on-sqlep-onelake-security/direct-lake-on-sqlep-onelake-security.md at main · ajit503/fabric-field-notes

Written by Ajit Kumar Singh — Fabric Admin & Architect. Based on hands-on troubleshooting of Direct Lake on SQL Endpoint with OneLake security across producer/consumer lakehouses.

Have a different experience with the new User-identity default? I'd love to hear how the rollout is behaving on your tenant.