Forum Discussion
Power BI + Azure SQL Server row-based security
Memorable MemberSSAS 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.
Advocate IVWe 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
well, there are three things that I have to say about this:
- 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
- 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
- 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.
- mstefancik
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.
- ashishrj
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.
