cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
malagari Member
Member

Azure Analysis Services, Azure AD, and RLS

Hey everyone,

 

I'm hoping to get some answers and clarification around RLS in an Analysis Services model.  So far, all of the documentation I've found talks about what to do when running SSAS on-prem - mapping the RLS role to users within the domain.

 

How does this work when using Azure Analysis Services?

 

I have an Analysis Services model that I've built and deployed using Visual Studio.  I went ahead and created roles based on the USERNAME() function, hoping to capture the Power BI Pro username and filtering on that.  Unfortunately, in both Power BI Desktop and the Power BI Web Service, I'm not seeing the RLS filters apply based on the currently logged-in user.

 

I had originally built out my reports by querying the SQL databases directly, and was able to handle all of the RLS within Power BI Desktop.  This made use of the USERNAME() function, and everything went as expected.  When I moved to the Analysis Services (Live) model, I figured I'd be able to do the same thing.

 

So - is it possible to have RLS filter based on the Azure AD username (same as Office 365 username) in my case?

 

Thanks!

Dan Malagari
Consultant at Headspring
1 ACCEPTED SOLUTION

Accepted Solutions
malagari Member
Member

Re: Azure Analysis Services, Azure AD, and RLS

Alright, after a bit of trial and error, I was finally able to get this working.  I figured it'd be worth sharing for those that are interested in a similar setup.

 

When you initially create an Azure Analysis Services instance, you're required to enter an administrator from your Azure AD.  When viewing the data in Power BI Desktop or Web, RLS does NOT apply to the account that was designated as the AAS admin.  To circumvent this, I went ahead and created an alternate user in my Azure AD for testing out RLS, and everything worked.

Additionally, the tooling in Visual Studio doesn't fully support Azure AD & Azure AS yet.  You'll need to use a combination of Visual Studio and SSMS 2016 to create the roles, and then associate it with an Azure AD user/group.

 

The Process

 

  1. Build your data model in Visual Studio.
  2. Create your roles (and their DAX expressions) in Visual Studio.
  3. Process & Deploy the data model to the Azure Analysis Services instance.
  4. Open up SSMS and connect to the Azure Analysis Services server.
  5. Open up the Roles associated to the data model, and add the associated Azure AD users/groups to the role.
          Note: You need to use the latest version of SSMS (and SSDT) to work with Azure Analysis Services.  This lets you add an                  Azure AD user or group to a specific role.
  6. Publish the reports to Power BI.  It's easier to publish to a Group Workspace so you can log in with the non-admin account and test the RLS. 
Dan Malagari
Consultant at Headspring
4 REPLIES 4
malagari Member
Member

Re: Azure Analysis Services, Azure AD, and RLS

Alright, after a bit of trial and error, I was finally able to get this working.  I figured it'd be worth sharing for those that are interested in a similar setup.

 

When you initially create an Azure Analysis Services instance, you're required to enter an administrator from your Azure AD.  When viewing the data in Power BI Desktop or Web, RLS does NOT apply to the account that was designated as the AAS admin.  To circumvent this, I went ahead and created an alternate user in my Azure AD for testing out RLS, and everything worked.

Additionally, the tooling in Visual Studio doesn't fully support Azure AD & Azure AS yet.  You'll need to use a combination of Visual Studio and SSMS 2016 to create the roles, and then associate it with an Azure AD user/group.

 

The Process

 

  1. Build your data model in Visual Studio.
  2. Create your roles (and their DAX expressions) in Visual Studio.
  3. Process & Deploy the data model to the Azure Analysis Services instance.
  4. Open up SSMS and connect to the Azure Analysis Services server.
  5. Open up the Roles associated to the data model, and add the associated Azure AD users/groups to the role.
          Note: You need to use the latest version of SSMS (and SSDT) to work with Azure Analysis Services.  This lets you add an                  Azure AD user or group to a specific role.
  6. Publish the reports to Power BI.  It's easier to publish to a Group Workspace so you can log in with the non-admin account and test the RLS. 
Dan Malagari
Consultant at Headspring

Re: Azure Analysis Services, Azure AD, and RLS

Thanks @malagari for the post.. i am also suppose to apply RLS while using Azure AD and Azure Analysis service and this post will surely help me..

I have some more questions if you can help me to clear that.

How did you create Role in Power BI Desktop to capture Azure AD user name and do you have those user name in any of the Power BI table (I mean are you storing those username in some where in Analysis service and then loading into Power BI or we can directly compare Power BI service username and the username from Azure AD) 

I hope my question make sense.. In case you are not able to understand the context of my question then please let me know..

 

Thanks for your help !!!

malagari Member
Member

Re: Azure Analysis Services, Azure AD, and RLS

I have the roles created in the Tabular Model that is deployed to Azure Analysis Services (I am using a live connection, not import mode).  These roles use the USERNAME() DAX function to capture the current user's login, and filter down the tables accordingly (I have a user table with matching email addresses in my data model).  Since our Azure AD is tied to our Office 365 directory, these are the same.

 

I then simply have to add the users to the role on the Analysis Services server, publish the .PBIX to the Power BI service, and then the report will automatically filter based on the current user context.

To make things easier on whoever is administering these roles moving forward, you can create a group in Office 365 thatis associated with the role.  Then, whenever you need to add a user to the role, you can simply add them to the group in the Office 365 Admin Portal.

 

I hope this helps.

Dan Malagari
Consultant at Headspring
anushab New Member
New Member

Re: Azure Analysis Services, Azure AD, and RLS

Hi,

 

I have tried the same steps as above to implement row level security in azure analysis services and Power BI. But did not work.

 

However, it was working on tabular model. That means data is getting filtered when logged in as different users.

 

But the same data doesnot get filtered in power BI.

 

Anusha

BI Developer

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 379 members 4,349 guests
Please welcome our newest community members: