Hi there,
I'm new to Power BI (coming from a dev background) and somewhat delving in at the deep end. I've found myself in a situation where we are needing to rapidly build a proof of concept to embed Power BI into an existing extranet/web portal that we have many hundreds of users setup for in our own user/membership database, thus are looking at Embedded and the App Owns Data model.
Our raw source data is in SQL Server and we've pre-aggregated some of this data set into what I can only describe as a poor mans data warehouse.
I've been looking at the integration into our web portal which has been successful, however we need to implement row level security to ensure the logged in user can only see data that relates to them. We are pushing in the username when we generate our embed tokens.
Exploring options around RLS with SQL Server (Direct Query), it appears we can only apply row level security for AAD users, and we can't dynamically inject the username from the token into queiries. Other options seem to suggest the USERNAME(), CUSTOMDATA() and USERPRINCIPALNAME() are only available via DAX, and I assume on-prem Analysis Services or Azure Analysis Services. I can push the data into a Power BI dataset but I could quite easily exceed the 1GB limit. The further I dig for answers, the more unsure I am about direction to take.
I've got on-premise data in SQL Server and I can put it anywhere... Whats a sensible choice to be putting my data for RLS against token values? Azure Analysis Services, in Power BI Dataset? Somewhere else?
Thanks in advance!
