Power BI Security
A common topic I have been discussing recently with customers is the security around Power BI. Basically, how to prevent users seeing data they shouldn’t. So I’ll discuss the various “layers” of security.
The Power BI service architecture is based on two clusters – the Web Front End (WFE) cluster and the Back-End cluster. The WFE cluster manages the initial connection and authentication to the Power BI service, and once authenticated, the Back-End handles all subsequent user interactions. Power BI uses Azure Active Directory (AAD) to store and manage user identities (in Azure Blob), and manages the storage of data and metadata (in Azure SQL Database), both using encryption at rest (where you can bring your own key).
Power BI also leverages the Azure Traffic Manager (ATM) for directing traffic to the nearest WFE, based on the DNS record of the client, for the authentication process and to download static content and files. Power BI uses the Azure Content Delivery Network (CDN) to efficiently distribute the necessary static content and files to users based on geographical locale (see Power BI Security).
User Authentication: Power BI uses AAD to authenticate users who sign in to the Power BI service, and in turn, uses the Power BI login credentials whenever a user attempts to access any resources that require authentication. Users sign in to the Power BI service using the email address used to establish their Power BI account.
Power BI workspaces and apps: You can publish content from your Power BI desktop into Power BI workspaces, which is a collection of dashboards, reports, workbooks, datasets, and dataflows. You can then add security groups, distribution lists, Office 365 groups, or individuals to the workspaces and assign users their roles and privileges as either viewer, contributor, member or admin. You have the option to bundle that collection into a neat package called an app and distribute/publish it to your whole organization, or to specific people or groups. Only dashboards, reports, and workbooks are part of the bundle, and you choose which ones you want to publish via the “INCLUDED IN APP” option. You can also allow app users to connect to the app’s underlying datasets by giving them Build permission. They’ll see these datasets when they’re searching for shared datasets. You typically start the process of creating an app within workspaces, where you can collaborate on Power BI content with your colleagues, and then publish the finished apps to a large group of people in your organization. Apps make it easier to manage permissions on these collections. Think of workspaces as staging areas and containers for the content that will make up a Power BI app
Row-Level Security: With Row-level security (RLS) you are given the ability to publish a single report to your users but expose the data differently to each person. So instead of creating multiple copies of the same report in order to limit the data, you can just create one report that will only show the data the logged in user is allowed to see. This is done with filters, which restrict data access at the row level, and you define filters within roles. For example, creating a role called “United States” that filters the data in a table where the Region = “United States”. You then add members (user, security group, or distribution list) who can only see data for the United States to the “United States” role (the assignment of members can only be done within the Power BI Service). If a user should not have access to a report, then just don’t include that person in any of the roles for that report, so they would always see a blank report.
Auditing: Knowing who is taking what action on which item in your Power BI tenant can be critical in helping your organization fulfill its requirements, like meeting regulatory compliance and records management. With Power BI, you have two options to track user activity: The Power BI activity log and the unified Office 365 audit log (differences listed here). These logs both contain a complete copy of the Power BI auditing data so you can view exhaustive logs of all Power BI activities. The audit logs only hold up to 90 days of data, so you may want to store the data and report off it via Power BI (see Creating a Power BI Report from the Audit Logs stored in Azure Blob Storage)
Data Sources – Import: When data is imported in Power BI Desktop, Power BI connects to the data source using the current user’s credentials from Power BI Desktop, or the credentials defined as part of configuring scheduled refresh from the Power BI service. In publishing and sharing such a report, be careful to only share with users allowed to see the same data, or to define row-level security as part of the dataset.
Data Sources – DirectQuery: Ideally, because DirectQuery always queries the underlying data source, this configuration would allow any security in that underlying source to be applied. However, after it’s published to the Power BI service, Power BI always connects to the underlying source using the same fixed credentials as would be used for import. Note that immediately after publishing a DirectQuery report, it’s necessary to configure the credentials of the user that will be used. Until you configure the credentials, opening the report on the Power BI service would result in an error. Once the user credentials are provided, then those credentials will be used by whichever user who opens the report. In this way, it’s exactly like imported data. Every user sees the same data, unless row-level security has been defined as part of the report. The same attention must be paid to sharing the report, if there are any security rules defined in the underlying source. Until Power BI allows for the identity of the report consumer to pass through to the underlying source, DirectQuery offers no advantages for data source security. See About using DirectQuery in Power BI
More info:
What Permissions are Managed by Power BI Content Owners? [VIDEO]
Power BI Security Best Practices
Good work
Nice post! A question on the following sentence:
“You can delineate access privileges to these apps: a Recipient only views the reports, Report Consumers are able to interact with but not edit the data, and the App Author can make edits.”
Where can I read more about this?
Oops, that sentence should not of been in there. I have removed it, as you can only specify who has read access to the app.
Hi
I have two questions:-
1) Is it possible show different users different fields n the downloaded data (so PII could be shown to one person but not another)?
2) Can you grab the PowerBI audit log and push it into an SQL database/table?
For Q2, we do this for our reports (for auditing purposes) but, know I know it exists in PowerBI, we’d need to do the same for that as well.
TIA
Richard
Hi Richard,
1) You can use Dynamic Data Masking that is available in SQL Server/database to obfuscate PII data for certain people
2) You can download the activities by using a REST API or PowerShell cmdlet and import them into SQL Server
Pingback:Row Level Security & Beyond in Business Intelligence Applications: A Practical Overview – Olivier Travers
If a “service account” is involved how can RLS hierarchies be implemented with PBI in the service and Azure SQL Server and Azure Active Directory in the mix?
Second question can Azure SQL Server security be used for RLS hierarchies in any scenario with PBI in the service?
Third question with PBI in the Service, Azure SQL Server, Azure Active Directory and a “Service Account” in the mix, what is the best way to implement RLS hierarchies, either in PBI or directly in SQL Server?
Thank your very much for any advice!!
Hi James,
Nice write up.
I am facing some challenges which i cannot have any answers.
I have a tabular model which hold properties and claims details.
I have added RLS on the contracts which limit individuals from seeing all data.
Using power bi to connect to the tabular cube and published to PBI services. I have given access to guest users outside my organization tenant, however they still see all data.
Any help on this as i cannot find any proper solution online. there is no way i can create users table and link it to the two tables i have in the model.
RLS works for internal users but not for external users.