SSAS 2016 Tabular On Premise with Row-Level Security and Active Directory
This is a series of articles related to RLS (Row Level Security), started with http://hectorv.com/bringing-security-where-data-lives
We are going to implement RLS (Row-Level Security) in SSAS (SQL Server Analysis Services) Tabular mode.
In our use case: Adventure Works Internet Sales, the goal is to restrict access to all customer data to specific users. The user GHowell, is a sales agent based on United States. This user should only be able to retrieve customers from United States when query the SSAS database using Excel, Power BI desktop or any other BI tool.
To implement RLS, let’s download a Tabular Project sample “Adventure Works Internet Sales Tabular Sample Solution”, then we’ll add RLS to this project.
Go to:
https://msftdbprodsamples.codeplex.com/releases/view/125550
Download Adventure Works Internet Sales Tabular Sample Solution from the link as shown below.
Open the project in Visual Studio and deploy it in your SSAS instance. We should apply RSL security to the Customer table. The customer table information from customers from different countries and “Adventure Works” has sales agents in different countries that should only have access to the corresponding customer information.
Notice the relationship between the Customer and Geography tables. The Row Level Security is disabled. Our plan is to apply RLS to the Geography table and the RLS will apply automatically to the Customer table, since there is a relationship between these two tables.
We are going to create a Role which have the security rules and membership.
- We create the Role: “Internet Sales United Kingdom”
- The Role screen show two tabs: Rows Filters and Members. In the Row Filter tab, we can see the list of tables. We select the Geography table and add the DAX filter.
=Geography[Country Region Code] = “GB”
Now, we add the membership in the Members tab. We add the active directory group that contains the “United Kingdom” sales agents.
Let’s test it!
In order to do our security test, we’ll use Excel as a BI client tool. In Excel go to Data – From Other Sources and select “From Analysis Services”.
Then, you have to enter your SSAS instance. After this a data connection in your Excel file is created.
You can create a Pivot table like the one shown below. It will show all users since probably your user is an administrator will access to all information.
Now, In order to test a specific user, we need to change the connection. In Excel, go to Data, Connections and select the SSAS connection that you created before. Go to the definition tab as shown below.
You are going to add an Effective user name to the connection string to simulate a query executed as another user. This will be allow only if you have administrator rights in the SSAS instance.
Try adding this at the end of the Connection String:
;EffectiveUserName=DOMAIN\GWilliams;
GWilliams is an active directory user that belongs to the “Internet Sales United Kingdom” AD group.
Click OK and you’ll see the data filtered only United Kingdom information as expected.
If you profile SSAS, you’ll be able to see that the Effective User name is sent with the query, then SSAS can check the AD membership and apply the corresponding Filter Rules defined in the SSAS Roles. Notice that Excel generate MDX queries instead of DAX queries.
In this article we learn how to implement RLS in SSAS tabular mode, and how to test it. In a following article, I’ll show you how to use this Tabular RLS security from Power BI with live connection.
