Power BI + Azure SQL Server row-based security

[Stefkus]

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

[andre]

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.

[mstefancik]

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]

@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]

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.

[mstefancik]

@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]

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]

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.