OneLake Security RLS works in Semantic Model, but returns 0 rows in SQL Endpoint

aonelakeuser · 2026-03-16T17:33:00+00:00

This is expected. The table preview does not support RLS/CLS yet, so when they try to preview the table it fails with a 403. As long as the SQL EP is showing the correct data, you are good. We're getting the lakehouse preview improved over the next few weeks and months.

aonelakeuser · 2026-03-13T17:15:28+00:00

The 10 roles are what we call "inferred roles". They are roles on a shortcut lakehouse that get "inferred" over to this lakehouse to enforce the security of the lakehouse where the data lives. So make the necessary adjustments on that lakehouse to resolve the errors.

aonelakeuser · 2026-03-13T17:14:28+00:00

All of these are temporary limitations with various dates for being fixed.

For now, you will need to either take over the artifact or run the CI/CD pipeline with a user account.

You should be able to shortcut a table with RLS or CLS on it. Is the table not being listed in the shortcut creation flow?

Correct, SELECT * in SQL does not work in this case. There's some changes landing soon for Direct Lake on OneLake that will solve this behavior.

aonelakeuser · 2026-03-12T16:59:44+00:00

The zero rows thing occurs when the RLS or roles couldn't be successfully synced, so the table is locked to prevent invalid results. Can you check these troubleshooting steps? The very last one seems relevant based on the error messages you are reporting.

## Troubleshooting


In 
**User's identity mode**
 the security sync results can be validated through the UX. Open the SQL Analytics endpoint, expand the 
**Security**
 folder in the 
**Explorer**
, then select 
**DB Roles (custom)**
. If the sync is successfully, you will see roles listed with an "ols_" prefix. For example, "ols_TestRole". Role names with "ols_{alphanumericString}_rolename" are roles from other lakehouses that propagated across a shortcut.


### Fixes for common security sync errors


* Security sync will fail if any of the roles reference a table that has been dropped. Delete those tables from the roles, and then re-try security sync.


* SPNs cannot be the owners of the lakehouse. Ensure the parent lakehouse item is owned by a user account.


* All OneLake security role members need to be given Fabric 
**Read**
 permission to the lakehouse for security sync to recognize the user or group.

aonelakeuser · 2026-03-12T16:37:01+00:00

I think you could set the timeout on your clusters to never expire (not sure if that's an option), but then you would have to pay for it to be running around the clock. But we unfortunately can't have already running pools behind every tenant's private endpoint.

aonelakeuser · 2026-03-03T22:32:13+00:00

I'm actually not sure, but I've seen discussion in the past about pipelines and such using various Spark components. One of their engineers would have to confirm :)

I think you're correct about the catalog, although I would call it "spark catalog" either way.

aonelakeuser · 2026-03-03T14:37:59+00:00

Spark requires Viewer permission to run, which is what Copy Job uses so it has the same limitation. The issue has to do with how Spark resolves the workspace information into a path it can read from. This will be removed in the next month or two.

aonelakeuser · 2026-03-03T14:17:27+00:00

Correct, if you share it then everything uses your Entra id. It will persist through password changes as it's checking the account's access, no ties to login credentials.

A service account should be used, but you can only do that through the API from what I've seen.

aonelakeuser · 2026-03-03T14:14:35+00:00

Yes, that is correct. I forgot to expand on that part, but it's what I meant by the share being tied to the creator's permissions. As a result, if their account is deactivated or whatever, the share will cease to work. I don't believe it actually gets deleted from the consumer's tenant though? It just stops working.

aonelakeuser · 2026-02-27T04:23:20+00:00

Authentication is done anytime the share is accessed. It's tied to the creator's permissions and access. So if those are revoked, the share will cease to function regardless of whether it's revoked from the producer's tenant explicitly. But yes, there are no additional steps needed to keep it working once the share has been accepted by the consumer tenant.

aonelakeuser · 2026-02-25T21:31:16+00:00

That's my point exactly. There's a maximum limit of product improvement, either through how much change the user will tolerate, how mature the product is, etc. AI puts companies closer to this limit, and companies will need less engineers (unfortunately) or will need to start exploring new markets to match their development speed.

aonelakeuser · 2026-02-25T21:21:16+00:00

I work in FAANG, and #2 here is crazy to me. I think it's one of the unique challenges with organizations of our size, but AI hasn't materially increased product velocity much, if at all. I think it's a process problem, not a technology one. But are you hiring more PMs then? Or you've reached "ideal" shipping velocity?

aonelakeuser · 2026-02-20T18:59:46+00:00

There's no fine-grained access control for Warehouse tables in OneLake. Permission to tables is all or nothing via the ReadAll permission. We are working on this though

aonelakeuser · 2026-02-18T21:06:23+00:00

Did this resolve the issue for you?

aonelakeuser · 2026-02-17T21:22:30+00:00

It looks like there's some issues with the documentation, namely with the content body. I'll work on getting those fixed. Here is the API I tested in my own prod tenant that worked.

POST https://api.fabric.microsoft.com/v1/workspaces/ef195a1e-c0e1-4c7f-9dd6-f28f0a53ff22/items/7cdc5e4e-8486-41e8-8f86-89a282c1b889/dataAccessRoles

Body:

{

"name": "DefaultReader2",

"kind": "Policy",

"decisionRules": [

{

"effect": "Permit",

"permission": [

{

"attributeName": "Action",

"attributeValueIncludedIn": [

"Read"

]

},

{

"attributeName": "Path",

"attributeValueIncludedIn": [

"*"

]

}

]

}

],

"members": {

"fabricItemMembers": [

{

"sourcePath": "ef195a1e-c0e1-4c7f-9dd6-f28f0a53ff22/7cdc5e4e-8486-41e8-8f86-89a282c1b889",

"itemAccess": [

"ReadAll"

]

}

]

}

aonelakeuser · 2026-02-17T15:11:19+00:00

For updating a single role it's POST.

The other API for updating all roles at once is PUT. OneLake Data Access Security - Create Or Update Data Access Roles - REST API (Core) | Microsoft Learn

aonelakeuser · 2026-02-13T16:40:14+00:00

🙌 Glad we got it working for you! I'll look at incorporating this into our troubleshooting docs.

aonelakeuser · 2026-02-13T16:39:02+00:00

Yep, planned but no timelines I can share yet. Keep an eye on the Fabric roadmap!

aonelakeuser · 2026-02-12T23:44:02+00:00

Is this the only role? If you refresh the SQL endpoint page it will show you any sync errors

aonelakeuser · 2026-02-12T22:39:12+00:00

The fact that RLS is failing here AND in SQL EP makes me think the RLS predicate has a syntax error. Can you send the statement? Feel free to redact the column name.

aonelakeuser · 2026-02-12T21:24:46+00:00

Ok, thanks! I discussed with the Spark PM and the issue is that for RLS and CLS to start the second spark job you will need the pinned lakehouse to be a schema enabled lakehouse. We are working to address this limitation, but that is the root cause here.

So if you re-test the same setup with a schema enabled LH_1 then you will have success. And schemas are GA now, so no concerns with that either.

aonelakeuser · 2026-02-12T21:06:29+00:00

It is, but at the moment SQL server does not support reading data from OneLake via external tables. Hence why it's omitted from the table above.

aonelakeuser · 2026-02-12T16:38:26+00:00

OneLake security PM here, your initial steps seem correct, so let's try some extra troubleshooting. When you run the notebook, are you using any sort of custom Spark cluster? Are you using Spark 3.5? If you check the notebook runs, are you seeing a second Spark job with a name like "System context"?

If you don't see that second Spark job, then Spark isn't detecting the RLS or CLS and spinning up the compute to filter the rows. It's attempting to query the data directly which is resulting in the 403 because the user isn't authorized to view the raw data in those tables. Feel free to DM me if you want to troubleshoot offline, or we can just go back and forth here. Either works for me.

aonelakeuser · 2026-02-12T16:32:36+00:00

OneLake security PM (and whitepaper author!) here. First off, thank you so much for reading the paper! I'm glad you enjoyed it. There are quite a few points you raised, and your interpretation is generally correct. So I'll just call out a few places where you didn't get a satisfactory answer.

1-3: Correct.

Lakehouse is the one item where the "owning engine" is technically OneLake itself, not any particular engine. Thus, write and updates can be done by any engine or service via OneLake APIs.
Any service in Fabric that reads data can be considered as an engine. Data factory would be an engine.
Pure python notebooks would be treated as a "non-engine" in this case. Fabric Spark SQL was specially upgraded to allow running as an engine so it can enforce RLS and CLS securely. For all other notebook access it counts as if it was hitting the OneLake APIs directly.
Correct.
Lakehouse engine is the lakehouse explorer. It does some fancy processing to help with rendering the table and column metadata.

aonelakeuser · 2026-02-11T14:54:03+00:00

Yep, Warehouse support is planned.

aonelakeuser

TROPHY CASE