OneLake Security on the SQL Analytics Endpoint

[fresantos]

OneLake Security centralizes fine-grained data access for Microsoft Fabric data items and enforces it consistently across engines.
Currently in Preview and opt-in per item, it lets you define roles over tables or folders and optionally add Row-Level Security (RLS) and Column-Level Security (CLS) policies. These definitions govern what users can see across Fabric experiences.

When you first opt in, a DefaultReader role preserves prior read access. You can remove or adjust this role to transition toward a least-privilege model.

OneLake Security in the Context of SQL Analytics

At its core, OneLake Security is an item-scoped RBAC and policy layer.
You can:

• Create data-access roles
• Attach tables or folders
• Add policies (RLS, CLS, object-level permissions)

Membership can be:

• Explicit – users or groups within the workspace (for example, those assigned the Viewer role)
• Inherited – via shared items such as a Lakehouse or shortcut

Once published, these rules apply to data access from all engines, including the SQL Analytics Endpoint. (See Microsoft Learn.)

Understanding OneLake Security for SQL Endpoint

The SQL Analytics Endpoint is a read-only surface over Lakehouse data.
When OneLake Security is enabled on a Lakehouse, the endpoint enforces those same Lakehouse policies in one of two access modes.

Write operations continue to be governed by workspace roles within the Lakehouse experience.” (See Microsoft Learn.)

Since there are two access modes, let’s look at how each determines security enforcement:

• User identity mode (central governance in OneLake)
  The signed-in user’s identity is passed through to OneLake; table reads are governed entirely by OneLake roles and policies (SQL GRANT/REVOKE on tables is ignored). You still can use SQL permissions for non-table objects (views, procs, functions). This gives 'define once, enforce everywhere' behavior across Power BI, notebooks, Lakehouse, and SQL.
• Delegated identity mode (SQL-centric control at the endpoint)
  The endpoint reads OneLake using the item/workspace owner identity; SQL enforces access (roles, GRANT/REVOKE, SQL-defined RLS/CLS, DDM) for the querying user. Use this when you need classic DBA patterns or utilize SQL specific security functions. Ensure the owner has read access in OneLake; otherwise, queries will fail.

Differences Between the Modes

• Tables:
  • User identity: controlled by OneLake roles; SQL GRANT/REVOKE is not allowed on tables.
  • Delegated identity: controlled by SQL; full GRANT/REVOKE.
• Views / Stored Procedures / Functions: Both modes allow SQL permissions (e.g., GRANT SELECT on views, GRANT EXECUTE on procs/functions).
• RLS/CLS/DDM:
  • User identity: RLS/CLS are defined in OneLake role UI; DDM isn’t part of OneLake Security.
  • Delegated identity: RLS/CLS/DDM are defined in SQL (security policies, column grants, masked columns).
• Workspace roles: In User identity, Admin/Member/Contributor bypass OneLake policies; Viewer or read-only sharing is required for enforcement.

Shortcuts: Remote Data Ownership and Practical Considerations

Shortcuts surface data from another Lakehouse (or external source) into your item. The security policy is enforced at the source; when SQL queries shortcut tables, the system honors the originating OneLake roles/RLS/CLS. Security sync also validates shortcuts, so remote policies aren’t bypassed.

Design tips:

• Document role mappings across items (consumer ↔ source) so troubleshooting is straightforward.
• Expect validation when shortcut targets change (rename/move); allow brief propagation time.

Configuration: Enabling and Setting OneLake Security for SQL Endpoint

1. Opt-in to OneLake Security on the Lakehouse (Manage OneLake security). Review the DefaultReader behavior; remove it or adjust to move to least-privilege. Create roles → select tables/folders → add explicit/virtual members → optionally set RLS/CLS.
2. In the SQL Analytics Endpoint → Security, choose OneLake access mode:
   • User identity (enforces OneLake roles only).
   • Delegated identity (enforces SQL permissions + OneLake security for the owner)
     Confirm the change in the pop-up.

Important switching behavior: Switching to User identity ignores SQL table permissions and can delete existing SQL roles on tables; switching to Delegated stops applying OneLake roles/policies to table reads for all users except the owner. Plan cutovers and back-ups accordingly.

How It Works Under the Hood: Security Sync and (Meta)Data Validation

When the endpoint runs in User identity mode, a background security sync service keeps SQL aligned with OneLake: it detects role changes, user assignments, and table/policy updates; it translates OneLake RLS/CLS into SQL-compatible constructs and validates that shortcut targets remain consistent, so the source’s policies are honored. Expect non-instant propagation (measure in minutes, not hours) and explicit errors if policies reference dropped/renamed columns—fix in OneLake and re-publish.

In Delegated identity mode, SQL is authoritative for the end user access. The endpoint authenticates to OneLake as owner, and the SQL permission model (roles, grants, RLS/CLS/DDM) determines access. Any mismatch between owner access to the Lakehouse and SQL grants will surface as query failures until corrected.

Bringing It All Together

OneLake Security brings a single, authoritative policy surface to Fabric and lets you decide how the SQL Analytics Endpoint enforces it:

• Choose User identity when you want to define once in OneLake, enforce everywhere (and ensure workspace users are Viewer/read-only so policies apply).
• Choose Delegated identity when you need classic SQL control (roles, grants, RLS/CLS/DDM) or to align with existing DBA tooling.

By unifying governance across SQL and Lakehouse, Fabric helps teams move faster while staying secure.

We’re actively working on additional features, so stay tuned for more updates!

Resources

To learn more, refer to the OneLake Security for SQL analytics endpoints (Preview), OneLake security overview documentation, and The next evolution of OneLake security (Preview) blog post.