Row Level Security SSAS - EffectiveUsername or CustomData for RLS Live Connection

[BI-Nomad]

Hello All,

I have some questions i would like the experts to answer over here.

- Is there a way to restrict access to the SSAS cube using Excel or Power BI but the Users still being able to see Data in the Reports created by the BI Department using Effective Username as Connection String Property?

- Is there a way in which CustomData can be used to provide RLS when connected to SSAS?What happens at the moment is that if the User with Readaccess is not directly an admin on the server then the Account which was used to connect to the Gateway which is in this case a Service account takes over as the EffectiveUsername and all dreams of implementing RLS are shattered as it always considers the EffectiveUsername to retrieve Data. This property is pretty useless in my used Case. Maybe others over here have other ideas. I would love to hear from people who have had success down this route. I know this way is meant more for Custom Autenthication from other sources other than power BI but still curious.

- Are there any best practices with regards to implementing RLS using PowerBI live Connection

[BI-Nomad]

Hi Jayleny,

Thanks for your Response and the clarification. This is what i feared as well regarding Custom Data but in my case with an onpremises SSAS it wont work. Have a nice day and thanks again for the prompt reply

[v-jialongy-msft · Accepted solution]

Hi @BI-Nomad 

Let's address your queries one by one.

Restricting Access to SSAS Cube Using EffectiveUsername

When connecting to SSAS cubes using Excel or Power BI, the EffectiveUsername connection string property allows you to pass the username of the end user to SSAS, enabling SSAS to apply security rules based on that user. This means that even if a service account is used to establish the connection via the gateway, you can still implement RLS by specifying the EffectiveUsername. Host Excel workbooks using Office Online Server (OOS) - Power BI Report Server - Power BI | Microsof...

Using CustomData for RLS in SSAS Connections

Regarding the use of CustomData for implementing RLS, it's important to note that CustomData isn't directly supported for this purpose in Power BI. However, for scenarios involving Azure Analysis Services (AAS) or when embedding Power BI content, CustomData can be utilized within the model to apply dynamic RLS. This involves creating roles in your SSAS model that leverage the DAX function to filter data based on the value passed into CustomData. Embed a Power BI report with an Azure Analysis Services (AAS) database - Power BI | Microsoft Learn

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.