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.
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.
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 firstname.lastname@example.org, which is my local domain for SSAS Tabular server.
Now with this user name mapping, email@example.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 firstname.lastname@example.org 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.
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.