Forum Discussion
OneLake Security RLS works in Semantic Model, but returns 0 rows in SQL Endpoint
Hi everyone,
I’m running into a wall with OneLake Security (Preview) and I’m hoping someone here has solved the issue.
I need a 'define once, enforce everywhere' security setup for the Gold lakehouse. My users should only have access to the Semantic Model and reports (via a Power BI App). They do not have workspace access (they are not Admins/Members/Contributors). I want the RLS I defined at the Lakehouse level to be the single source of truth.
The Setup:
- Lakehouse (Gold): OneLake Security is enabled.
- RLS Role: Created two roles in the Lakehouse, applied a filter to a specific table (e.g., SELECT * from Regions WHERE Region = 'North'), and assigned Entra ID groups to them.
- Semantic Model: Built on this Lakehouse. When the test user opens the report/model, it works perfectly. They only see the data they are supposed to see.
- The Problem: When I try to query that same table from the SQL Analytics Endpoint using (I am an Admin on a workspace level), the table appears, but it is completely empty (0 records).
If OneLake Security is supposed to be the 'Universal' security layer, why is the SQL Endpoint failing to see the rows that the Semantic Model (Direct Lake) sees perfectly? It feels like the SQL engine isn't correctly inheriting or syncing the OneLake security metadata.
Is this a known limitation of the Preview?
Has anyone successfully used OneLake RLS with the SQL Endpoint?
Any advice or workarounds (that don't involve duplicating the security logic in T-SQL) would be greatly appreciated!
I was able to identify the root cause. In my case, some Entra ID groups had been renamed. Although the object IDs remained the same, the SQL analytics endpoint still had stale principal entries under the old names. Running a diagnostic query against sys.database_principals helped me identify the external principals present in the SQL endpoint, which is what that catalog view returns.
After identifying the affected principal, I removed it by name from the current database and then triggered a metadata sync. The DROP USER syntax for a name with spaces must use brackets, for example DROP USER [Group Name]
After that, the sync completed successfully and I was able to query the table normally again.
9 Replies
Hi BIscoverer,
By default, SQL Endpoints will use a deleagted identity for OneLake Security, meaning it uses the permissions of the lakehouse/warehouse owner.
You can flip that over to User's identity in the Security tab when you open the SQL Endpoint:
Hi tayloramy ,
Thanks for reviewing.
I did switch to the User's identity and that's exactly where the problem comes:
When I switch back to the Delegated identity, there is no problem in querying, but it will not carry over RLS either. My goal is to have the RLS enabled on the SQL endpoint that should inherit the rules from OneLake security, however, there always seems to be an error after I attempt to sync metadata or if I query a table with RLS in question.
I understand that sync might take some time, but I have noticed the issue for already one week, so it's not a recent setup.
Hi BIscoverer,
Do you have seperate RLS configured on the SQL Endpoint outside of OneLake Security?
I see you have 12 errors while syncing, if you click details what is the error emssage you get?
Hi tayloramy ,
I have no RLS configured on the SQL endpoint.
The errors are as following:
- two exact errors for two roles I have set up: User does not have permission on the artifact. Provide user with [object ID] permission to the artifact. [RoleName1]
- This is something I would expect, because I have not provided these users access to the Lakehouse/SQL endpoint and would like to avoid doing so. The access to the end users would be only given to the semantic model built on top of this lakehouse (DirectLake on OneLake). The security propagation from OneLake Security works there fine.
- ten same errors: Please remove user {0} from role {1}. New user is created with conflicting SID. Role Name: DefaultReader. User Name: [Entra ID group of workspace admins].
- This is something I don't expect to appear. The error exists irrespectively whether I have DefaultReader as a role or if I delete it or if I rename it. I was assuming that for the workspace admins, there should be no role because they bypass the RLS and by design have access to all teh items in the workspace, including this Lakehouse.
- two exact errors for two roles I have set up: User does not have permission on the artifact. Provide user with [object ID] permission to the artifact. [RoleName1]
Hi BIscoverer,
For users to access the SQL Endpoint, the item does need to be shared with them. So if your models are using directquery to the endpoint, your users need access to that endpoint.
Though if the goal is just to have the security applied to the model, can I suggest just ignoring the SQL endpoint all together and set up DirectLake models directly on the delta tables? This should respect OneLake security.
As for that second error, I have no idea there, I've not seen that one before. Have you tried deleting your roles and recreating them?
Hi tayloramy ,
The users should not access the SQL endpoint. This is needed only for developers aka workspace admins.
Yet, that's exactly where I am experiencing 0 rows when querying the SQL endpoint being a workspace admin myself.
The semantic model respects the OneLake Security (DirectLake on OneLake), so all good there. The only issue is with the SQL endpoint. I can set it up as Delegated identity, then no RLS will be applied and admins can query the data, but I miss the point of 'set it up once and use it everywhere' motto of Microsoft when OneLake Security is supposed to be also inherited by the SQL endpoint.
I have tried to delete and re-create the roles again. Still the same issues, just now for some reason there are only 11 errors (instead of having two errors for two roles, I now have only one, although, nothing nothing has changed in terms of permissions to the item)
Hi BIscoverer,
My last stab at this is asking if you have "shared" the lakehouse/sql endpoint with your users?
https://learn.microsoft.com/en-us/fabric/onelake/security/sql-analytics-endpoint-onelake-security
The SQL Analytics Endpoint requires a one-to-one mapping between item permissions and members in a OneLake security role to sync correctly. If you grant an identity access to a OneLake security role, that same identity needs to have Fabric Read permission to the lakehouse as well. For example, if a user assigns "[email protected]" to a OneLake security role then "[email protected]" must also be assigned to that lakehouse.Hi tayloramy ,
Thanks again for the suggestion.
Yes, I already granted Fabric Read access to the same Entra ID groups that are assigned to the OneLake security roles, so the role membership and item permission mapping should be aligned. I also removed the admin group from my custom full-access role and deleted that role entirely, but I still see the same stale auto-generated roles in the SQL endpoint, the same sync errors, and the table still returns 0 rows there, while the semantic model continues to work correctly.
At this point I’ve submitted a Microsoft support ticket as this does not look like expected behavior anymore. I’ll share an update here once I learn more.
I was able to identify the root cause. In my case, some Entra ID groups had been renamed. Although the object IDs remained the same, the SQL analytics endpoint still had stale principal entries under the old names. Running a diagnostic query against sys.database_principals helped me identify the external principals present in the SQL endpoint, which is what that catalog view returns.
After identifying the affected principal, I removed it by name from the current database and then triggered a metadata sync. The DROP USER syntax for a name with spaces must use brackets, for example DROP USER [Group Name]
After that, the sync completed successfully and I was able to query the table normally again.
