cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stefkus
Frequent Visitor

Power BI + Azure SQL Server row-based security

Is it possible with Power BI and Azure SQL Server database to use row-based security? I know it's possible with a SSAS model.

 

We have a SQL Server database running on Azure, and we would like to connect Power BI based on a database username in combination with Azure Active Directory.

 

Our goal:

1 database with data for 10 customers (already have this in Azure SQL database), we want each customer to have a dashboard in Power BI with their own data.

 

Do we need to create 10 reports and dashboards? Are there other options? Is this only possible with SSAS?

 

I hope someone can shed some light on this matter. All ideas are welcome! Thanks in advance!

 

I hope

7 REPLIES 7
andre
Memorable Member
Memorable Member

SSAS is the only way to implement row level security for shared content.  All other datasets when shared run in the context of the person who created them and not the person who is viewing them.

We have got the same scenario. If it is possible only with SSAS, what else would you suggest, how to achieve everybody sees its data? We need to find the solution although it should be the part of Power BI to succeed.

andre
Memorable Member
Memorable Member

@mstefancik,

well, there are three things that I have to say about this:

 

  1. Power BI should have built-in rich security capability that permeates groups, datasets, reports dashboards and dataset attributes and calculations - unfortunately, it is not currently part of the service and we have not heard any comments from Microsoft on whether security will be built into Power BI
  2. I suggested here to make USERNAME() work in the service but it's not a good, robust solution, although it would allow for some options to secure data in the model by user.  Again, no comments from Microsoft on that
  3. Unfortunately, your only option is to promote your Power Pivot models to Analysis Services.  Even more unfortunate, your Desktop models cannot be promoted to Analysis Services in SQL 2014 because they are using the SQL 16 features and are not backwards compatible.
ashishrj
Power Participant
Power Participant

Hi @andre I am trying to implement SSAS tabular row level security for SSAS in SQL Server 2014 but could not succeed. I was able to successfully implement the same using SSAS in SQL Server 2012. As you mentioned "Even more unfortunate, your Desktop models cannot be promoted to Analysis Services in SQL 2014 because they are using the SQL 16 features and are not backwards compatible". So how will I make row level security works for SQL 2014? Any reference link for your statement? or any alternative solution ? I am stuck with the same on a project and needs to apply row level security asap. Any help would be appreciated. Thanks in advance.

@andre

 

That is crazy, how do they want to compete to the other visualization tools?

Once it is supposed for enterprise usage, security is on the first place for every single comapny, or at least it should be.

 

Anyway, we have to find way how to achieve it.

As I am new to MS technology (formerly worked with Sybase) I want to ask to clear it out.

 

Customer has DWH and makes reports to excell using BI tool to excel.

We want him to use Power BI to have dynamic, real time dashboards accessible by broader range of employees (not only 3 managers) and to have them in any time.

 

DWH is base on SQL server, so if we tried to connect directly to sql server, how can it be ensured that everybody will see only data he has got permission for. (Active directory, MS sql login passed to BI??)

This is something we do not want to as there is complex logic behind.

 

So we want to connect to excel workbook created and just visualize data.

 

Do you have any usable suggestion? ONce again , i am new to MS, despite my question.

 

Thx

andre
Memorable Member
Memorable Member

The only way to do it is to create an Analysis Services Tabular model, build desired security logic in it and then and expose it to Power BI.

 

On the negative side, it's obviously extra work, may require additional hardware/licensing/support.

 

On the positive side, Analysis Services tabular will give you a semantic model, performance and security in one spot and it also can be accessed from other tools, not just Power BI.

Greg_Deckler
Super User
Super User

My understanding is the the DAX USERNAME function only functions with SSAS and that therefore SSAS is the only data source that supports row-based security currently.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors