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,
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?
Helper IHi 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)