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,
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:
BIscovererHi 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.