I am really excited to show you in this blog post how to use Active Directory (AD) Security groups to make Dynamic Row Level Security (DRLS) easy and simple.

In the past when using DRLS there had to be a list maintained of all the users, along with what Row Level Security they required. As can be seen with the image below, in which this is the first 6 lines of a possible 200.

This was an administrative nightmare.

Now using AD Security Groups, you no longer need to maintain a long list of users.

All that you will need to do is to put in the AD Security group with the required permissions and Power BI will do the REST! This means a small and simple security file with the permissions and AD Security group.

The users who require access will have to go through the formal channels with regards to being added to AD Security Groups. This has the following awesome advantages.

  • The report developer will only have to implement DRLS Once.
  • Users who require access will have to possibly log a call or chat to the IT Department to be added into the required AD Security Group.
  • One or possibly multiple users will be responsible for the Security File. Which can possibly be located a SharePoint List, SharePoint Online Document Library, One Drive file etc.
  • Administration of whom is in the AD Security Group is looked after by the IT Department. If people move roles, leave or need changes this could possibly be done as part of an IT process.
  • Very often there is already AD Security Groups in place for different departments. Which means that there is already existing AD Security Groups that can be leveraged.

How awesome is that?

I personally think it is pretty awesome, clean and simple, where everyone within an organization is responsible for their area where they work!

I am not going to go into the details of how DRLS works, there are a whole host of blog posts explaining this. The two that I have used in the past are from Radacad.com and Kasper on BI which go into great details and have working copies.

Ok, that is enough waffling, let me show you how I do it.

NOTE: This blog post is quite long, but it is well worth the read. I go into a fair bit of detail to explain how it all pieces together with a lot of images.

Example

As always it is easiest to follow with an Example.

I have got a Security File, which has all the attributes as to which Country the AD Security Groups belong to.

Below is a bit more detail on the Security File and AD Security Group.

  • I have got a Security File table which has got the following attributes shown below.
    • As you can see above it has got a Country column, and an AD Group Column
    • From the above users who are in the SYD Office AD Security Group will only see data from Australia
    • And the above users who are in the USA Office AD Security Group will only see data from United States
  • Next, I created 2 AD Security Groups in Office 365
    • In the groups above, I added the required users.
      • In my working example I have a user which will be called “Pro“
    • NOTE: In a typical organization, there will already be AD Security groups with users added.

Connecting to Active Directory

In the steps below, I am going to give an overview of how to connect to Active Directory. As well as the final table which will have the Users & Groups to which they belong to, along with the all-important User Principal Name.

  • I first created a Parameter called Domain Name
  • I went to Get Data and then selected Active Directory
  • I then put in my Active Directory by selecting the Parameter
  • This is where it can get a bit tricky navigating AD, so what I did was to find the Users details and kept the following details shown below in my Users table.
    • The reason for the Users table is to get the User Principal Name which is required for Office 365
  • Next, I had to get the Group details, which once again was a bit tricky navigating AD, and once again this was the output I needed into my table.
    • As I saw above, it is a pity that the Group AD Container does not have the User Principal Name
  • I then merged the two tables above, to get the Output I needed, which is shown below. And this includes the User Principal Name
    • I did it by selecting the following:
    • And this was the desired output

Using the Query Editor to automate the bursting of User Principal Names

This is where the true power and awesomeness of the Query Editor is at its best. In the steps below, I am going to show how by using the Query Editor I can burst all the data to get all the User Principal Names

  • I go to my Security File Table.
  • Next, I click on Merge Queries and selected the following as shown below.
  • This then resulted in the following table
    • As you can see above it is showing that it is a table. But it still only currently 2 rows.
  • I clicked on the Expand Button and selected the User Principal Name.
    • NOTE: I also made sure that the Use original column name as prefix did not have a tick.
    • Once again this is because all that I require for the DRLS is the User Principal Name
  • And the result was now the Query Editor bursting all the rows for me
    • As you can see above I now have 6 rows.
    • NOTE: If you had a lot of groups with a lot of countries it would burst this all for you.
    • HOW AWESOME is that?
    • No need to remember users, and who is coming and who is going.
  • The final step is to load this into the data model by clicking Close and Apply.

Creating the Relationships between the tables.

Next, I show how to create or ensure that the required relationships are in place for DRLS to work.

  • This is what my relationships looked like after the data had loaded.
  • There was one key relationship missing which is from the Security File table to the Country Table.
    • NOTE: This is the relationship that enables the DRLS to work.
  • I created it as shown below.
    • The key thing to take a note of here, is that the Cross filter direction is set to Both
    • And that I have enabled the Apply security filter in both directions is enabled.
    • This ensures that when the DRLS is in place it will work as expected.

Configuration of the DRLS Role

The next few steps are fairly simple. In this next step I am going to configure one role that will be used for all users.

  • I am now back in Power BI Desktop, and I click on the Modelling
    Tab, and then click on Manage Roles
  • I then find the AD User Details table and put in the following DAX Syntax
    • 'AD User Details'[User Principal Name] = UserprincipalName()
  • And then I click Save.
  • That is all that is needed for the DRLS for all your users.
  • Once again, so simple yet so powerful. I personally LOVE IT!
  • I then clicked Save and uploaded the PBIX file to the Power BI Service.

Configuration of the Security for DRLS in the Power BI Service

The next step is where I then needed to configure the security for DRLS in the Power BI Service.

The reason for this, is so that the Power BI Service can correctly assign the correct security when a user logs in.

  • I logged into the Power BI Service, and went to the App Workspace where I had uploaded the PBIX file.
    • NOTE: It is always suggested to use App Workspaces to ensure that the organization
      owns the data and files and not an individual user.
  • I then went to Datasets, and clicked on the breadcrumb (or 3 dots …) and selected Security
  • I was then presented with the following screen as shown below.
  • Now once again, making my life very simple all that I needed to do, was to add in the same groups from my Security file. (Syd Office & USA Office)
    • I then clicked Add
  • And then Save.
  • I know that this has been a rather long blog post, for something that is inherently so simple. But I felt like it needed all the steps so that someone could easily follow it.
  • Now when a specific user logs in they will only see their view of the data.
  • The data should ideally be shared via an App from the App Workspace.

DRLS in Action

As you can see below, when the user Pro logs in, based on the DRLS above, they should only see data from USA, and that is what is shown below.

Conclusion

As I have demonstrated, it is a lot easier and simple to leverage AD Security Groups and Power BI Query Editor to do all the heavy lifting for DRLS.

As well as this allows the Security File and AD Security Groups to be managed
outside of Power BI and the data modelling experience.

Here is the link to the file AD Steps – DRLS.pbix, in which you will still need to plug in your data, but it does have the required Active Directory Steps in place.

And as always if there are any questions or comments please leave them in the section below.

Appendix: Adding in the AD User Details

Due to the way Active Directory Works, and having sensitive data, if you want to do this after download the file above, please replace the following M syntax in your file for the table called AD User Details

let
 Source = ActiveDirectory.Domains(#"Domain Name"),
 my.domain.com = Source{[Domain=#"Domain Name"]}[#"Object Categories"],
 user1 = my.domain.com{[Category="user"]}[Objects],
 #"Removed Other Columns" = Table.Buffer(Table.SelectColumns(user1,{"displayName", "user"})),
 #"Expanded user" = Table.ExpandRecordColumn(#"Removed Other Columns", "user", {"userPrincipalName"}, {"user.userPrincipalName"}),
 #"Filtered Rows" = Table.SelectRows(#"Expanded user", each not Text.Contains([displayName], "@")),
 #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "displayName", "displayName - Copy"),
 #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column","displayName - Copy",Splitter.SplitTextByPositions({0, 1}, false),{"displayName - Copy.1", "displayName - Copy.2"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"displayName - Copy.1", type text}, {"displayName - Copy.2", type text}}),
 #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"displayName - Copy.1", Text.Upper}}),
 #"Split Column by Delimiter" = Table.SplitColumn(#"Uppercased Text","displayName - Copy.2",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"displayName - Copy.2.1", "displayName - Copy.2.2"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"displayName - Copy.2.1", type text}, {"displayName - Copy.2.2", type text}}),
 #"Split Column by Position1" = Table.SplitColumn(#"Changed Type1","displayName - Copy.2.2",Splitter.SplitTextByPositions({0, 1}, false),{"displayName - Copy.2.2.1", "displayName - Copy.2.2.2"}),
 #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"displayName - Copy.2.2.1", type text}, {"displayName - Copy.2.2.2", type text}}),
 #"Uppercased Text1" = Table.TransformColumns(#"Changed Type2",{{"displayName - Copy.2.2.1", Text.Upper}}),
 #"Removed Columns" = Table.RemoveColumns(#"Uppercased Text1",{"displayName - Copy.2.1", "displayName - Copy.2.2.2"}),
 #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"U",Replacer.ReplaceValue,{"displayName - Copy.1"}),
 #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"U",Replacer.ReplaceValue,{"displayName - Copy.2.2.1"}),
 #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"displayName - Copy.1", "First Name Letter"}, {"displayName - Copy.2.2.1", "Surname Letter"}, {"user.userPrincipalName", "User Principal name"}, {"displayName", "Display Name"}})
in
 #"Renamed Columns"

 

Appendix: Adding in the AD User & Groups

Due to the way Active Directory Works, and having sensitive data, if you want to do this after download the file above, please replace the following M syntax in your file for the table called AD User & Groups

let
 Source = ActiveDirectory.Domains(#"Domain Name"),
 my.domain.com = Source{[Domain=#"Domain Name"]}[#"Object Categories"],
 person1 = my.domain.com{[Category="person"]}[Objects],
 #"Expanded top1" = Table.ExpandRecordColumn(person1, "top", {"memberOf"}, {"top.memberOf"}),
 #"Expanded top.memberOf2" = Table.ExpandListColumn(#"Expanded top1", "top.memberOf"),
 #"Expanded top.memberOf3" = Table.ExpandRecordColumn(#"Expanded top.memberOf2", "top.memberOf", {"displayName", "name"}, {"top.memberOf.displayName", "top.memberOf.name"}),
 #"Filtered Rows" = Table.SelectRows(#"Expanded top.memberOf3", each ([displayName] <> null)),
 #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"person", "distinguishedName"}),
 #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"displayName", "Display Name"}, {"top.memberOf.displayName", "Group Email Address"}, {"top.memberOf.name", "Group Name"}}),
 #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Display Name"},#"AD - User Details",{"Display Name"},"NewColumn",JoinKind.LeftOuter),
 #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"User Principal name", "First Name Letter", "Surname Letter"}, {"User Principal name", "First Name Letter", "Surname Letter"})
in
 #"Expanded NewColumn"
#"Expanded NewColumn"