Row Level Security with SSAS Tabular Live Connection in Power BI



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.


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, which is my local domain for SSAS Tabular server.


Now with this user name mapping, 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;


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 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.









Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.


  • Good demo Reza!

    Can the Tabular model in SSAS when connected from Power BI Desktop takes effect based on the RLS defined?

    • 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.

      • 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?


  • 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

  • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *