cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
toddpbi Regular Visitor
Regular Visitor

Row Level Security

Hello there.

 

Currently I am trying to implement row level security on my data model. My company is not allowing the usage of Power BI service, which rules out any chances of me being able to use Cloud Services offered by Microsoft.

 

Is there any other alternatives to be able to implement RLS into my model? I have heard about using dynamic row level security in SSAS Tabular Models, but the end result still is publishing the model to the Power BI cloud. I only have access to an SSAS Tabular Model and an onPremise PowerBI Report Server.

 

Any guidance on this matter would be of great help to me.

Thanks,

12 REPLIES 12
Anonymous
Not applicable

Re: Row Level Security


Hi @toddpbi,

We have used SSAS Tabular RLS with Power BI RS On-Prem. Works well, I'm not sure why you think you would still need to publish to the cloud, I might be missing part of your requirement?

Regards
Rob

 


 

Ctijsseling Regular Visitor
Regular Visitor

Re: Row Level Security

@Anonymous Sorry to ask but have you really been able to use RLS SSAS with Power BI Report Server? How did you do that? How did you get the role been recognized in Power BI?

Anonymous
Not applicable

Re: Row Level Security


Hi @Ctijsseling,

 

Yes honest we have, it works well. 🙂

 

There are a few pre-requisites

 

You have to be using windows active directory domain user security, and your PBI users need to be authenticated logged in users.

 

We are using the Oct 2017 PBI Desktop and PBIRS on-prem, on a server on the domain. Not sure if it works with versions earlier than this.

 

We have SQL Server 2016 SSAS Tabular supporting our data model. And we use the SSAS Tab DM as the data source for the PBI report. Not a DM in PowerBI. (Direct Query like connection)

 

The roles are created and managed in SSAS Tabular, PBI is just the consumer.

 

We do have the SQLserver, SSAS Tab service and PBIRS all running on the same server. I think there maybe some extra setup needed if you distribute these elements.

 

There are some online resources for this, try Guy In A Cube for example.

 

Hope that helps

Rob

 

 

 


 

Ctijsseling Regular Visitor
Regular Visitor

Re: Row Level Security

Thank you @Anonymous. I have tried many times these steps and for several weeks now, I am still not able to get Power BI to filter the row according to my windows login. The puzzeling thing is that the Tabular model works well in excel or even SSRS and get it to recognize role A using the *Roles* keyword in the connection string. I'm using the following connection string: Data Source=MyServerName\TABULARSERVER;Initial Catalog=TestRLS;Roles=A; 

 

But such filtering doesn't work in Power BI. 

 

In your SSAS Tabular, if you created several roles - say "role A", "role B" and "role C" - how do you get power bi to filter the report using say "role A"? 

toddpbi Regular Visitor
Regular Visitor

Re: Row Level Security

Thanks for your quick response.

 

How do you share your reports to the end consumers after prepping the data model?

How do the end consumers view these dashboards?

Regards,

Ben

Anonymous
Not applicable

Re: Row Level Security

Hi @Ctijsseling. I have been working with Rob on the role level security in our organisation.

 

In our model we have an employees table which is linked to the main data. This has their Active Directory user name stored in a column (say Role A).

 

In Roles in SSAS Tabular we have then written the following DAX and add appropriate measures.

 

=ADUserName = USERNAME() || [RoleA] = USERNAME() || [RoleB] = USERNAME() || [RoleC] = USERNAME()

 

The article that we refered to when setting our role level security is as follows:

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

 

Also are you connecting to Analysis Services using a Live Connection in Power BI?

 

I hope this helps.

 

James

Anonymous
Not applicable

Re: Row Level Security

Hi @Ctijsseling. I work closely with Rob and we have developed the role level security together for our organisation.

 

The original article we refered to when setting it up is as follows:

 

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

 

With your Power BI model is it set up as a Live Connection to the SSAS tabular model? It has to be live and can only contain tables from the model itself.

 

Also I found that if you have SA access to the database then you cannot view it as a particular user and will see everything.

 

I hope this helps.

 

Anonymous
Not applicable

Re: Row Level Security

Hi @todd. @Anonymous currently share our reports using Power Bi Report Server on prem.

 

We are currently not in a position to use the cloud yet.

 

Does that help?

Ctijsseling Regular Visitor
Regular Visitor

Re: Row Level Security

Hi @AnonymousJames,

Thank you for your message.

 

My employee table is called z_Users and their Active Directory login is in column called "Login" and is linked to another table called "Time Recorded".

 

I have set up a role in SSAS Tabular (Model/Role Manager/) called: "This is a securityRole". I wrote the following DAX (Model/Row Filters/Dax Filter): =z_Users[Login]=USERNAME(). 

 

I've created a Power BI report using a live connection to the SSAS Tabular 2016 described above

( Home/Get Data/Analysis Service/"Connect Live"/Model) using the appropriate Server and Database names. I then save the report on PBRS. 

 

Now if I run the above mentioned report, I still get all the rows (not just the one I should be able to see). If I modify the connection string in PBRS (Data Sources/Connection String) and add the Roles argument (Data Source=ServerName\InstanceName;Initial Catalog=RowLevelSecuritySSASTabular;Roles=ThisIsASecurityRole), my report doesn't get filtered. 

 

But the strange things is that if now, I create a very basic SSRS report, on the same reporting server, in same folder location, with exactly the same connection string, the report DO get filtered as expected. I have also the same correct behavior when I connect to the cube using Excel and the same connection string. So it is something relating to Power BI and not my SSAS Tabular cube.

 

My query is how do you get your Roles to be recognized into Power BI? Did you have to modify your connection string to get it recognized? 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 152 members 1,642 guests
Please welcome our newest community members: