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.
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!
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.
well, there are three things that I have to say about this:
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.
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.
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.
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.
Click here to read more about the June 2022 updates!
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!
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.