You can define Row Level Security in Power BI itself, However sometimes you do use SQL Server Analysis Services Tabular as the source for Power BI through Live or DirectQuery connection. SSAS Tabular allows you to create same type of Row Level Security. So it would be much better to use Row Level Security defined in Tabular from Power BI, rather than duplicating that in Power BI. In this post I’ll show you an end-to-end solution which contains elements below;
- Power BI Live Connect to SSAS Tabular through Enterprise Gateway
- Row Level Security Configuration in SSAS Tabular
- Viewing the result filtered by RLS in Power BI
Note that this method is different from defining Row Level Security in Power BI Desktop. In fact we won’t use that method. If you like to learn more about that method read this blog post. If you are interested to learn more about Power BI, read Power BI online book; from Rookie to Rock Star.
Prerequisite
For this demo I will be using my demo machine which has SSAS Tabular and Enterprise Gateway. In a separate post I’ll explain how to create such demo VM. You also need to have SSAS Tabular database example AW Internet Sales Tabular Model 2014 from here.
Install and Configure Enterprise Gateway
Purpose of gateways in Power BI is to create connection from Power BI cloud service to on-premises data sources. There are two types of gateways; Enterprise, and Personal. Naming of these gateways are a bit misleading. Personal doesn’t mean you have to install gateway on your laptop only, and Enterprise doesn’t mean only for organizations. There are more differences which I will talk in different post. In general Enterprise is built for more live connections specially to Analysis Services, and Personal is more for importing data from many data sources. Let’s leave details of that for now. For this example we will be using Enterprise Gateway because we want to connect to SSAS Tabular on-premises through a Live/DirectQuery connection.
You can download latest version of Enterprise Gateway from PowerBI.Microsoft.Com website under downloads
Then from gateways download page, choose Enterprise gateway, and download it.
Installation of Enterprise Gateway is just following a wizard, so continue that till end of the wizard where it asks for Power BI login. Sign in to your Power BI account;
After sign in, configure the Power BI Gateway with a name, and key, and then you should be good to go. If you see green checkbox and Connected means your configuration was successful.
As you can see after successful configuration, you can close the gateway (don’t worry it is running behind the scene as a service), and add a data source to this gateway in Power BI Service.
Or alternatively you can directly go to Power BI website, after login go to setting menu option and choose Manage Gateways
In Gateway management window, you can see all gateways you have set up. My gateway here is named BIRADACAD, and I can see that it is successfully connected.
Create Data Source
Now Let’s create Data Sources. You might think that one gateway is enough for connecting to all data sources in a domain. That is absolutely right, however you still need to add a data source under that gateway per each source. each source can be a SQL Server database, Analysis Services database and etc. For this example we are building a data source for SQL Server Analysis Tabular on premises. Before going through this step; I have installed AW Internet Sales Tabular Model 2014 on my SSAS Tabular, and want to connect to it. If you don’t have this database, follow instruction in prerequisite section.
For creating a data source, click on Add Data Source in manage gateways window (you have to select the right gateway first)
Then enter details for the data source. I named this data source as AW Internet Sales Tabular Model 2014, I enter my server name, and database name. then I use Windows authentication with my domain user <domain>\username and the password. You should see a successful message after clicking on Apply. The domain name that I use is BIRADACAD (my SSAS Tabular domain), and the user is PBIgateway, which is a user of BIRADACAD domain (username: BIRADACAD\PBIgateway), and is an administrator for SSAS Tabular (explained in next few paragraphs).
Note that the user account that you are using here should meet these conditions:
- It should be a Domain User
- the domain user should be an administrator in SSAS Tabular.
You can set administrator for SSAS Tabular with right clicking on SSAS Tabular instance in SSMS, and in Properties window,
in the Security setting add the user to administrators list.
Effective User Name
Gateway account used for accessing Power BI cloud service to on-premises SSAS Tabular. However this account by itself isn’t enough for the data retrieval. gateway then passes the EffectiveUserName from Power BI to on-premises SSAS Tabular, and result of query will be returned based on the access of EffectiveUserName account to SSAS Tabular database and model.
By default EffectiveUserName is the username of logged in user to Power BI, or in other words EffectiveUserName is the Power BI account. This means your Power BI account should have enough access to SSAS Tabular database to fetch required data. If Power BI account is the account from same domain as SSAS Tabular then there is no problem, and security configuration can be set in SSAS Tabular (explained later in this post). However if domains are different, then you have to do UPN mapping.
UPN Mapping
Your SSAS Tabular is part of a domain (it should be actually, because that’s how Live connection works), and that domain might be the domain that your Power BI user account is. If you are using same domain user for Power BI account then you can skip this step. If you have separate Power BI user account than the domain account for SSAS Tabular, then you have to set the UPN Mapping.
UPN Mapping in simple definition will map Power BI accounts to your local on-premises SSAS Tabular domain accounts. Because in my example I don’t use same domain account for my Power BI account, so I set up UPN as below.
Then in Mapping pane, I create a new mapping that map my Power BI user account to reza@biradacad.com, which is my local domain for SSAS Tabular server.
Now with this user name mapping, reza@biradacad.com will be passed as EffectiveUserName to the SSAS Tabular. If you want to learn more about UPN mapping, you can watch Adam Saxton’s great video about it.
Configure Row Level Security in SSAS Tabular
In SSAS Tabular opened in SSMS, expand AW Internet Sales Tabular Model 2014, and create a New Role
We want to create a role for users who don’t have access to Bike sales across the database. so let’s name the role as No Bike Sales Manager, this role has Read access to the database.
In the membership section you can add users to this role. Users should be domain users that you get through EffectiveUserName from Power BI (if Power BI accounts aren’t domain users, then create UPN mapping for them as explained above in UPN mapping section). I add user BIRADACAD\Reza here. (Note that I’ve created a UPN mapping for this user. so each time Power BI user associated with this logs in, this domain account will be passed through EffectiveUserName to SSAS Tabular).
Now let’s define Row Filters with a basic filter on Product Category as below;
='Product Category'[Product Category Name]<>'Bikes'
Now that we defined row level security in SSAS Tabular, let’s build a simple report to test it.
Create Sample Report
Our sample report here would only show a Pie chart of Product Categories Sales. So create a Live connection to SSAS Tabular from Power BI Desktop.
Then choose the model
After creating the connection you should see the Live Connection: Connected status in Power BI Desktop down right hand side corner.
Create a simple Pie chart with Product Category Name (from Product Category table) as Legend, and Sales Amount (from Internet Sales table) as Values.
Save the Power BI file, for example as SSAS Tab Live Ent DB.pbix, and publish it. Remember that you shouldn’t set up Row Level Security in Power BI itself. Because we will be using RLS configuration from Live Tabular connection.
Test the Result
In Power BI website or service, when you login, and refresh the SSAS Tab Live Ent DB report. you won’t see any sales from Bikes. you will only see sales of Clothing and Accessories.
How doesn’t it work?
What happened here is that my Power BI account mapped to reza@biradacad.com domain user, and this domain user passed through enterprise gateway as the EffectiveUserName to the SSAS Tabular on-premises. SSAS Tabular identified that this user has only one role which is No Bike Sales Manager, and this role has a restricted view of sales for everything but Bike. So the data returned from SSAS Tabular to Power BI report doesn’t contain Bike’s sales.
Summary
SSAS Tabular as a Live connection source for Power BI used in many enterprise solutions for Power BI. There are different reasons for that, for example;
- Some organization already have SSAS Tabular models ready, and they are using that for their on-premises reporting and data analysis. So they want to use the same source of truth.
- Scale of data is larger than it fits into Power BI model. (Read step beyond 10GB limitation for Power BI as an example).
- and many other reasons.
In this post you’ve seen how Row Level Security defined in SSAS Tabular will be passed through EffectiveUserName to Power BI accounts. This method will authorize users to only view part of the data that they are authorized to see.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Good demo Reza!
Can the Tabular model in SSAS when connected from Power BI Desktop takes effect based on the RLS defined?
Raj
Hi Raj,
Do you mean using Power BI Desktop as your visualization point?
when you create the live query connection then you need to use your windows account for it. and you will see only part of it that you have access to it.
It means RLS is built-in with Live Query connection in Power BI.
Cheers
Reza
Hi Reza,
Thanks for the reply. I mean if RLS has been implemented for my username/alias in SSAS and when connected to the model via GET DATA in Power BI Desktop to visualize the data, do I see the data I have the access or I see all the data in Power BI Desktop?
Thanks!
Raj
Yes, with SSAS Live Query connection, you will only see part of data that you are authorized for it, based on Roles defined in SSAS.
Cheers
Reza
Hi Reza. Great article. One question … if for a role, no filter has been specified for object A, does that mean that any individual identified in that role can see all data in object A OR does it mean they cannot see any data in object A? If that latter, have you ever used a filter that is never true (e.g. 1=0) to effectively prevent users of a role from seeing any data in an object?
Hi Mike,
Yes, a role with no filter means has access to everything.
a role with a filter that is always false (e.g. 1=0) means nothing. However, if the user has access to this role as well as another role, then user will see the union of both roles
Cheers
Reza
Hi Reza
Nice post !!
I have SSAS tabular model and I want to implement dynamic row level security on my tabular model. After that I want users to browse my tabular model using Power BI but here when they browse , they can access only data they are allowed to access in tabular model. So will this work o dynamic security on SSAS Tabular model.
Hi
Dynamic Row Level security with SSAS Live connection works exactly the same as Power BI. The only difference is that you write your security role code in the SSAS tabular rather than Power BI. and users will be able to see the data related to them based on that. if their Power BI accounts and local domain accounts are different, then you will need to work on a UPN mapping table, like the one mentioned in this post.
Cheers
Reza
I am trying to create a PowerBI dashboard where my data resides in SQL Server Database. If I use direct query, it is too slow. Thus, I tried to create a SSAS tabular model. This solved the problem of speed. However, everyone can see everything in tabular model. Is there a way to pass SQL server database security to SSAS without defining security within the SSAS. I am trying to see if security can be maintained via SQL Server database so that we do not have to maintain security in both the places. Any thoughts?
If you want SSAS to rely on the security in SQL Server backend, then you have to use SSAS as DirectQuery, which will again result in slowing down. your best option is to define the security again in SSAS. However, instead of static row level security in SSAS. You can do it dynamically using the username option. I have written about dynamic row level security in my other blog post. that blog post uses Import Data approach, but the same dynamic row level security configuration can be defined in SSAS Tabular and then you can have Power BI live connection to it. When you use the dynamic row-level security, your tables in the data model are responsible for keeping the access levels and all the configurations. I have also written about other scenarios of dynamic row level security in other blog posts like this.
Cheers
Reza
Hey Reza,
Can we implement this RLS from SSAS tabular model in power BI desktop, while doing a import connection ( NOT Live)?
Thanks,
Sindhu
If you are importing from SSAS, then you have to implement RLS inside the Power BI Desktop itself. I wrote about it here.
Thank you for the quick response Reza.
But the problem for me is, I was not able to import the tables into power BI because data is huge ( ERROR – there is not enough memory to complete this operation).
So I am planning to create role, by filtering last 2 years of data. But as you said, if I am importing the role I created in SSAS tabular will not work.
Can you please help me the best way to solve the issue?
Thanks,
Sindhu
Hi Sindhura
Is your data huge because you select everything you want in one shot in the Navigator window of Power Query when you connect to SSAS?
Remember that selecting everything from all dimensions, will create a cross join type output, and it would be huge. Not recommended that way.
If you query data separately for each dimension, then it would be small.
Hi Reza Rad,
I have a scenario, wherein I have implemented row level security in SSAS tabular model and am able to test it in Sql server management studio by browsing the cube using different users. But, I want to formulate an automated test strategy where we can do row level security for different users in Power BI itself. Any ideas on this would be helpful. Thank you.
Do you want to implement RLS in Power BI now instead of SSAS Tabular?
or you are asking about how to test user access in Power BI?
or both?
You can implement RLS in Power BI like this. you can also test it in the Power BI Desktop or website based on the same thing.
Cheers
Reza
Hi Reza,
We have a multidimensional cube and we tried to implemented the RLS using the roles however it is not working.We mapped our external user to the account in the domain through UPN mapping and that domain account is part of one Role.But the external user is able to see everything.Do you think what is the possible cause for this.I believe the domain account added in the roles has admin access on the VM where the cube installed.Would you please advise
Hi Jadish,
The connection to the multi-dimensional cube is LIVE connection I assume? otherwise it will work based on a saved credential.
If it is live connection, and still user see everything, then the user is part of a role that has access to everything. I suggest checking the logic of the role and seeing where the user access is
Cheers
Reza
Hi Reza,
Yes it was Live Connection to Multidimensional Cube.The issue was that the user was having Local Admin access on the VM where cube was deployed and there was some issues in the roles those were created as well.It was fixed.Thanks
Glad the issue is fixed 🙂
Hi Reze , Thanks for the article , But what if I use SSAS MD instead of Tabular model ?
Does the same approach work properly ?
We have RLS configuration on the SSAS Multi-dimensional too. the filters are written a bit differently. MDX mainly, but possible.
Hi Reza
Your articles are really helpful.
Have you manged to pass trhough access to the Source.
Lets say the source is a SQL Server (AD group), can access be controlled from the SOurce and not SSAS Tabular?
that would work if you have DirectQuery connection to SQL Server database and Kerberos setup
Cheers
Reza
I have a SSAS tabular model with RLS implemented. Data is filtered for all the users except me. I am the creator of the report and model. Do you know why is RLS not working when I am the user of the report?
The creator (publisher) of the report is report owner, with Edit rights on the report. RLS would only work for view rights
Cheers
Reza
Fantastic article.
I wanted to ask how to solve the RLS problem. I have an embedded report in the application using the PBI Embedded service. It is connected live to the model in SSAS Tabular where we have roles level security.
We would like to use the “Username ()” formula to display the restricted data to the right people. How to do it How do you transfer “Username” to SSAS Tabular?
The live connection Power BI report used through embedded should use different approach for RLS.
because the EffectiveUserName will be passed through the connection all the way to the SSAS tabular, and the result of that comes up in the Power BI, you need to get the username of the people logged in to the system somehow through your custom application. this can be their network username, and then pass it through the REST API
Cheers
Reza
Hi Reza
I have a question about how to handle the same person in different roles with different security restrictions. I have a large model and a very large and complex organization. We have very strict security demands coming from management and this is giving us sleepless nights sometimes. So here is my question:
Is it possible, in the same cube, to have person X having different security restrictions in different roles at the same time. E.g. When Person X connects to Report1 he should be able to see data for the entire organization, but when he connects to Report2 he should only see data for his department and when he connects to Report3 only data for his Cost Center. This is not an easy task as the Role manager always defaults to the role where he sees the most. I tried so many different approaches and now I need some new inspiration. Is there some way to activate one Role over the other or is this issue not possible to solve? What does your experience say?
Thanks in advance.
Lasse Jørgensen
Hi Lasse
Very unique requirement 😉
The best way that comes to my mind at the moment is to have different data sources than only one to serve them all. having three SSAS data sources. one for the entire data (probably this would be high-level data anyways), one for department-level access with the RLS configuration, and one for the cost-centre-level access.
The level of data stored in each model might also change because one is very detailed and requires detailed analysis and one is very high-level. so it might make sense to have multiple models.
Cheers
Reza
Hi Reza,
I don’t see Row filters option in my SSAS. I have a table with users and departments for which they should have access.
I have department dim which is connected to all facts.
I would like to use users = userprinciplename() in the row filters and I see below option in screen
1. General
2. Securables
3. Extended properties (where we can put name and value)
What version of SSAS are you using? is it Tabular or Multi-Dimensional? can you share a screenshot of what you see when you go to properties of your SSAS database?
Cheers
Reza
Hi Reza,
My data source is ASS live connection (Azure analysis services) I want to use RSL for specific power bi report not the whole the data source.
For example a user might be restricted to view other cities in report A, but need to view all the cities in report b. Is there a way to do this?
If you are using live connection, then RLS comes from the AAS. You can’t have it at the report level
However, the composite model on live connection (which is coming later this year) might help with your implementation, so that can bring some RLS logic into the Power BI model.
Cheers
Reza
Hey Reza nice article
Pls i have a situation, I’m currently working on a PowerBi dashboard which uses a SSAS tabular model as Source and its on live connection.
How would i be able to view the RLS created on the dashboard the “view as Roles” is not enabled in the powerBi desktop.
Hi Nonso
The “View as role” option is only available in Power BI, when you implement the RLS inside Power BI. In the SSAS Live connection scenario, the RLS is defined in SSAS. You can’t View as role with that. However, you can try login in with another user, or you can change the connection string (which is not a straightforward process in the Power BI Desktop)
Cheers
Reza
Hi,Reza,
I Have a situation. I have implemented RLS in SSAS and connected successfully with power bi paginated report , I am using power bi report server but in power bi report server the RLS is not working ,any other configuration needed on power bi report server, please help.
can you please explain what is the configuration for the report and dataset on the Power BI report server? are they using DirectQuery? Live or Import data connection? and where is the RLS defined etc?
There is no limitation as such that RLS won’t work on the report server, I think the configuration might be the issue.
Cheers
Reza