Forum Discussion
Helper IOneLake Security RLS works in Semantic Model, but returns 0 rows in SQL Endpoint
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.
Super UserHi 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?
Helper IHi 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.