Skip to main content

Power BI

Under Review

Security - Ability to maintain source security for reports published on BI Sites

Vote (1832) Share
Ramu Kodemala's profile image

Ramu Kodemala on 03 Mar 2015 07:53:46

The general requirement is that visualizations (Power View, SSRS etc...) must not circumvent existing policies, or introduce yet another set of security policies on top of those already implemented at the source.

* For example, a visualization of sales data needs to reflect the policy that account managers can only read sales data for their region.
* For performance reasons, this is enforced at the source by injecting predicates into the query based on the end users identity. If identities for end users are not passed down the process chain into the data layer, it leaves us little option but to publish individual reports for every region, which results in an explosion of complexity and numbers of reports, or move the whole model to BISM and manage the policy in yet another place (namely the BISM model).

Impact
blocking migration to SPO/BI Sites. At least 412 Site Collections with more than 600 Power Views. Impacting Adoption or migration for majority of BPUs - e.g. Finance, LCA, HR, etc

Administrator on 16 Aug 2020 02:15:30

Hey all! We've continued to make progress here, so I wanted to update this thread with our current capabilities for maintaining security on dashboards/reports. As always, all of this information can be found in our Row-Level Security (RLS)documentation: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/ > If you have set up RLS in Analysis Services, Power BI will send the signed-in user's credentials to Analysis Services, and respect the RLS rules set up on the on-premises model. > Separately, you can set up RLS in Power BI for data sources that you import or connect to via DirectQuery. This process starts in PBI Desktop, where you define roles, and write DAX to constrain what data these roles can see. As part of this process, can you use the UserPrincipalName () DAX function to get the current signed in user's UPN (e.g. joe@contoso.com). Then, once you publish to service, you can assign users to these roles. Does the above meet your requirements? Please let us know via comments or e-mail. Those of you who requested that the identity of the signed in Power BI user be pass through to Azure SQL, SQL DB, DWH, etc.: we hear you - that is under consideration. Thanks, -Sirui

Comments (144)
Ramu Kodemala's profile image Profile Picture

krishna sanga on 26 Aug 2021 12:47:50

RE: Security - Ability to maintain source security for reports published on BI Sites

Importantly when the RLS Security is enabled and the workbook has been downloaded to PBI Desktop by an end user they should not be able to edit the security configuration setup.

Ramu Kodemala's profile image Profile Picture

Swapnil on 16 Aug 2020 04:07:20

RE: Security - Ability to maintain source security for reports published on BI Sites

We are using an Odata feed to CRM Online and to mimic the entire security model already customized in CRM is not very practical.

Ramu Kodemala's profile image Profile Picture

Power BI Ideas Admin on 16 Aug 2020 03:58:27

RE: Security - Ability to maintain source security for reports published on BI Sites

Agreed - we are also looking at RLS and through desktop, definately slows things down. There needs to be better ways to link back to DB.

Ramu Kodemala's profile image Profile Picture

Connie Corredor on 16 Aug 2020 03:57:49

RE: Security - Ability to maintain source security for reports published on BI Sites

We really need the ability to connect to an SQL database with AD authentication for a live gateway connection when publishing to the web. PowerBI has this feature on the desktop, it should work on the web as well.

Ramu Kodemala's profile image Profile Picture

PlymouthRock on 16 Aug 2020 03:55:13

RE: Security - Ability to maintain source security for reports published on BI Sites

We've implemented RLS using DAX. The queries run 10 times slower. It appears that all of the data is returned to the client and then DAX filters it. To get the performance we need, we have to have the identity passed through to SQL Azure RLS via session_context.

Ramu Kodemala's profile image Profile Picture

PlymouthRock on 16 Aug 2020 03:55:08

RE: Security - Ability to maintain source security for reports published on BI Sites

Hi Sirui,

Any update on identity pass through to Azure SQL. We know it is under consideration, but we need to know if it will be implemented.

Thanks,

Tim

Ramu Kodemala's profile image Profile Picture

Neil Palmer on 16 Aug 2020 03:54:49

RE: Security - Ability to maintain source security for reports published on BI Sites

I'd love to see this expanded to respect the SQL security using pass through auth and Azure Active Directory. When using services like SQL Azure, the security model can then be built in the database - agnostic of the reporting tool on top.

Ramu Kodemala's profile image Profile Picture

Jonathan on 16 Aug 2020 03:54:45

RE: Security - Ability to maintain source security for reports published on BI Sites

If I build a single report to which is applied some Row-level security, does the end-user need to buy some « application »/ « plugin » to visualize only the data he is allowed to see ?

If not, how can we pass along his identity to the back end ?

Ramu Kodemala's profile image Profile Picture

PlymouthRock on 16 Aug 2020 03:54:32

RE: Security - Ability to maintain source security for reports published on BI Sites

This is also a deal breaker for us. We currently are working on a Multi-Tennant enterprise app using RLS in SQL Azure. All of our security is defined with Security Predicates in the database and we are adding the User ID in session context in our web application.

We have started testing PowerBI embedded with Direct Query. With the ability to set User data in the Access Token, it shouldn't be that difficult to have PowerBI set session context after it connects to the database.

It should be configurable to allow the developer to set any session context variable they choose.

This would keep security where it belongs in the SQL database.

Ramu Kodemala's profile image Profile Picture

Pieter on 16 Aug 2020 03:53:17

RE: Security - Ability to maintain source security for reports published on BI Sites

It should be possible to use the identity of the logged on user in both Power BI as well as in SQL DB/DWH etc.