Back to List

Three Steps to Implement AD Security for an On-Premises Data Gateway

Eric Saltzmann Eric Saltzmann  |  
Apr 28, 2017
 
The idea to implement AD groups was first realized when trying to dynamically populate O365 group membership, that can be done with PowerShell. The benefit of that knowledge lead me to thinking how I could reverse the process and extract the list of members in an AD group. A short discussion with Andrew Schwalbe - a Sr. Systems Administrator here at Skyline - lead me to discover the importance of mail enabled security groups to leverage certain properties for authentication.

The Problem
Due to the nature of self-service, many organizations struggle with maintaining the current list of O365 groups or individual users that have access to data sources through the on-premises gateway. The problem being every time a new O365 group is created it would have to be added to the gateway users list for a data source. However, these struggles can be mitigated by implementing the same AD groups used for authentication on your data warehouse or analysis services cube in the on-premises gateway.

The Solution
According to the Microsoft Feature summary AD groups have been enabled since November of 2015, but as you can see from the ideas link there remains confusion on how to properly implement AD groups. This will be your guide to implementing AD groups in the Power BI on-premises data gateway.


1.Establishing Reporting Groups

To fully leverage the AD groups and minimize maintenance, you should grant permissions to each data source (SSAS Tabular Cubes) with a single reporting group with reader permissions for the model. This single reporting group will be specific to the business domain the tabular model was implanted to serve. Then populate that business domain’s reporting group with the current AD groups used for role based authentication. That way when associates are creating new 0365 groups for organizing collaboration their authentication in the Power BI service is seamless without requiring IT involvement and reports can be authored as quickly as groups are created.
*Server admins will also need to be assigned into a business domain group as well.


2.Assigning Mail Enabled Security Groups

The solution to utilizing AD groups on the enterprise gateway can be found by leveraging the mail enabled property of the security group. Per Microsoft, “A mail-enabled security group can be used to distribute messages as well as to grant access permissions to resources in Active Directory.”  The process to enable email is straight forward and only requires an email be assigned to the group. 
 
saltzmann_1.jpg
Using mail-enabled AD groups, allows the enterprise gateway to access the Members property in the Azure Active Directory group for the user. Notice how I said Azure Active Directory, that will be covered in the next step. That way the user will always authenticate as a member of the allowed Azure Active Directory group listed, no matter which O365 group they are using. This needs to be done for all security groups and recursively for each AD group that is a member of the current group for permissions to pass through.


3.Sync to Azure Active Directory to reflect on premises AD

The final step to get the on premises AD group into the Power BI on-premises data gateway is to sync the local AD in to Azure Active Directory.  A prerequisite to this process will be to install the latest version of DirSync known as Azure AD Connect. In the tool you must choose which organizational units are going to by synced to Azure AD. The organizational unit where the security group resides is chosen to sync.
saltzmann_2_jpg_1.png
*Sub Groups must exist in the same organizational unit as the reporting security groups or the sync won’t contain those members.
 
You can confirm in Azure AD that the group sync has taken place and that the type is a “Mail-enabled security group”
saltzmann_3.png


Assigning users to the Power BI on-premises data gateway

Once the sync is complete, you will now be able to assign the single business domain reporting role to the on-premises data source as you would with any O365 group or individual user.
saltzmann_4.png
AzureBusiness IntelligenceMicrosoftOffice 365

 

Love our Blogs?

Sign up to get notified of new Skyline posts.

 

Comments
Blog post currently doesn't have any comments.
 Security code

Related Content


Blog Article
Microsoft Build 2017 Day 3 Recap
Brandon MartinezBrandon Martinez  |  
May 13, 2017
Day three of Microsoft Build 2017 did not have a keynote, but that doesn’t mean it ends without content to fill the day. Today will be focused on sessions, “The Hub”, and catching some of the Channel 9 Live production happening right here at the conference. Here’s a...
Blog Article
Microsoft Build 2017 Day 2 Recap
Brandon MartinezBrandon Martinez  |  
May 12, 2017
Another beautiful day in Seattle, and another amazing day at Build 2017! Today started off with another fantastic keynote followed by a wide array of awesome sessions. Here’s my recap of today’s events. Keynote Today’s keynote was heavily focused at improvements and...
Blog Article
Microsoft Build 2017 Day 1 Recap
Brandon MartinezBrandon Martinez  |  
May 11, 2017
I’m very excited to be here in Seattle, Washington to attend the Microsoft Build 2017 conference! It’s an honor to be amongst the geekiest of the geeks in the Microsoft community and to be able to absorb a ton of knowledge over the next few days. I’ll be posting daily recaps of...
Blog Article
Three Steps to Implement AD Security for an On-Premises Data Gateway
Eric SaltzmannEric Saltzmann  |  
Apr 28, 2017
The idea to implement AD groups was first realized when trying to dynamically populate O365 group membership, that can be done with PowerShell. The benefit of that knowledge lead me to thinking how I could reverse the process and extract the list of members in an AD group. A short discussion with...
Blog Article
More Adventures with Fuzzy Tools: Six Reasons Why Fuzzy Lookup is Better than a Normal Lookup
Jared KuehnJared Kuehn  |  
Mar 22, 2017
A few months ago, I wrote about a migration I was working on where I incorporated a Fuzzy Lookup into a data matching process between two environments (see ‘Fuzzy Lookup in SSIS: A Simple Approach to Resolving Complex Data Quality Problems’). Time has passed, that project has...