Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
malagari
Responsive Resident
Responsive Resident

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
malagari
Responsive Resident
Responsive Resident

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

View solution in original post

6 REPLIES 6
Deepanshi_Ranka
Regular Visitor

I have a similar scenario but with my data in a SQL table which I have included in model. How to approach this?

 

malagari
Responsive Resident
Responsive Resident

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

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

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

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

 

Can anyone please post a solution to this. We are also facing the same issue as @anushab 

 

Any inputs are appreciated.

 

Regards,

Ankita

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors