Context?

Power BI makes it easy to connect to data and start building reports. One thing that is often not thought of until late in the process is how the reports will be refreshed, and more importantly, what security context will be used for the refresh.

When using the Power BI desktop application to get data the user is prompted to enter credentials for the data source. The user can select Windows Authentication, Database credentials, Microsoft Account, and others depending on the source.

This selection is critically important when developing reports when source systems implement row-level security.

Illustration

I have configured row-level security in SQL Server on the AdventureWorksDW database to filter the FactResellerSales table to specific regions. This is the security predicate function.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

create function [RLS].[tvf_SecurityPredicateSalesTerritory] (@SalesTerritoryKey int)
returns table with schemabinding as return

select
1 as [fn_SecurityPredicateSalesTerritory_result]
where 1=1
and exists 
(
    select 1 as Result
    from dbo.DimSalesTerritory as st
    where 1=1
    and 
        (
            (
               is_srvrolemember('sysadmin') = 1 
            or is_rolemember('db_owner') = 1
            )
        or 
            (
            st.SalesTerritoryKey = @SalesTerritoryKey
            and 
                (
                   (st.SalesTerritoryCountry = 'United States' and is_member('Role_UnitedStates') = 1)
                or (st.SalesTerritoryCountry = 'Canada' and is_member('Role_Canada') = 1)
                or (st.SalesTerritoryGroup = 'Europe' and is_member('Role_Europe') = 1)
                )
            )
        )
);

The result of the function is that users in the server role sysadmin or the database role db_owner have full access to all sales territories. Otherwise, users must be in the role for United States, Canada, or Europe (or some combination thereof), each of these will limit the data to the respective territory.

In this example, the developer is a member of the United States role and may only see data for the US. A simple report is created which shows the data that is expected.

However, when this report is deployed to the Power BI Service it uses a service account to connect. This service account is used by many reports and has access to all of the data in the source database. The result is that data for every sales territory is displayed.

When using Import mode, Power BI does not enforce source-system security policies. It is subject to those policies when refreshing data, but it only extracts the data once, not once for each user viewing a report.

Does DirectQuery Help?

So far I have been discussing reports which use Import mode, where Power BI will first extract all of the data for each table, (as defined in Power Query) and store that data within the Power BI file or in the service. Power BI can also operate in DirectQuery mode where it generates queries against the source system when a user views the report. This mode does not extract and save data, it only displays the aggregated results of queries against the source.

When using DirectQuery It is possible to pass the report user’s context to the source system if Kerberos SSO is enabled. This setup is beyond the scope of this post, but more information can be found here.

Keep in mind that not all data source connectors support DirectQuery and that an on-premises data gateway is required. Additionally, the features available within Power BI itself are limited when using DirectQuery. DirectQuery is the only option if you need to let the source system enforce row-level security. Otherwise you will need to re-create the rules within the Power BI model and service.

Note, ODBC does not support DirectQuery, but it is possible to enable it by creating a custom connector. More information can be found here.