***Note this post applies to Power BI for Office 365 (deprecated as of 12/31/2015). Though some concepts will translate, all details are not necessarily applicable to the new Power BI V2 service.***
This blog post is an overview of things you'll want to consider when it comes to security with Power BI.
This post is as of June 2014 - things can and will change as Power BI grows and matures.
Things to Consider with Security in Power BI
The categories of things to be aware of when you are implementing Power BI include:
- Authentication of users to access the Power BI Site
- Permission to read (view) or edit workbooks (XLSX files)
- Permission to refresh data stored in a workbook
- Permission to access data via an OData feed
- Permission to access data via shared Power Queries
- Roles for data stewards and administrators
- Service, user, and virtual account to run the Data Management Gateway
- Secure transmission of data refreshes from Data Management Gateway
- Ports for Data Management Gateway
Some of these items will be maintained by the administrator of the Power BI system, while some will be maintained by the user (author), or the owner of a document library that stores workbooks.
Authentication of Users to Access the Power BI Site
Power BI does not currently support anonymous users (although Office 365 itself does). The supported logon types are:
- A Microsoft Account. This used to be known as a Microsoft ID or a Windows Live ID. Example: user@outlook.com.
- An Organization ID. Example: user@org.onmicrosoft.com. Optionally, this ID can be validated against Active Directory if set up in Exchange Online.
Users are set up in the Office 365 Admin Center:
The Users and Groups page in the Office 365 Admin Center
Then, within each individual user's account, they get access to Power BI by being assigned a license:
The Licenses page of a user account in the Office 365 Admin Center
Permission to Read (View) or Edit Workbooks (XLSX Files)
For a user to see a Power Pivot workbook, the user needs:
- Permission to the document library where the workbook is stored. (By default, a Power BI site has one document library but you can most certainly utilize many more in order to organize content or set up security differently by area. More info is here: Organizing a Power BI Site.)
- Permission to the workbook. Preferably, you will set up files to inherit security from the document library itself, but it is possible to break the inheritance - be cautious if you do that though because it's definitely more maintenance.
Power BI workbooks are secured at the file level - either you can view the workbook or you cannot. There is currently no concept of row-level security in an XLSX workbook that contains an embedded Power Pivot model. Additional control over row-level security is usually one justification for upgrading to a SQL Server Analysis Services Tabular model.
Note that in order to specify security, you need to view the document library & files via the "old school" way of viewing the document library in SharePoint Online rather than via the Power BI Site with the thumbnail previews. More info about how to access that is here: Ways to View Content Published to a Power BI Site.
The pre-defined SharePoint security groups in Office 365 may work for you, but you may also need to set up additional groups. How to secure & organize content one of the things to look into when you're initially planning your Power BI site.
Default SharePoint Groups
The couple of pre-defined SharePoint groups I want to call out are:
- Members. Users in the Members group have permission to read *and* edit the workbook. This means not only can they edit the Power View and/or Excel reports in the workbook, but they can download the workbook and edit the Power Pivot model embedded in the workbook.
- Visitors. Users in the Visitors group have permission to only read, or view, the workbook. This means they cannot edit the reports, nor can they download the workbook, nor can they see how the Power Pivot model is defined and its calculations, etc. If a user attempts to download and open a workbook it'll show this message:
Users with Read permissions can interact with reports, but cannot open the file, edit the file, or see the underlying Power Pivot model
You can find more information about permissions in SharePoint Online here: Understanding permission levels.
Permission to Refresh Data Stored in a Workbook
To be able to successfully set up a scheduled data refresh, a user (or group) has to be explicitly granted permission to do so. This is defined within a data source in the Power BI Admin Center rather than the workbook itself. This is good - if multiple workbooks utilize the same data source (i.e., the same source database for feeding data into Power Pivot), the refresh for each of those workbooks will function with that data source being set up just once.
Users and Groups page when setting up a Data Source in the Power BI Admin Center
For this to have effect, the "Enable Cloud Access" checkbox also needs to be selected on the Data Source Usage page.
When data refresh runs, it runs under the credentials of the user defined in the "Connection Info" page of the data source in the Power BI Admin Center - this might be the original workbook author but many times it's not. This can have an implication re: the data coming back which may present a security concern if not properly managed. More details are here: Data Source Connectivity for Scheduled Data Refresh in Power BI.
Permission to Access Data Via an OData Feed
Discovering corporate data in Power BI using Power Query is a great feature of Power BI - it makes it easier for users to access data by using search instead of knowing ahead of time what the connection string is for a particular database. Permissions for which users can discover data in this manner is handled with the exact same setting as the one mentioned just above: in the User and Groups page of the data source in the Power BI Admin Center.
If you need separate groups of users to be able to set up refresh (i.e., the "Enable Cloud Access" option) vs. discover data via an OData Feed, then you'll want to set up two data separate data sources and define the "Data Source Usage" page differently for each:
Data Source Usage page when setting up a Data Source in the Power BI Admin Center
Permission to Access Data Via Shared Power Queries
I'm a big fan of sharing Power Queries - just like the OData Feeds that administrators can expose, other Power BI users can share their Power Queries with others. This allows other users to discover them using Search in Power Query, and it promotes reusability and consistency.
Permissions to find and use shared Power Queries are handled by the user who is sharing the Power Query. It's done in Excel > Power Query ribbon > Manage Queries > Shared > hover on the query and choose Edit Settings:
Properties when sharing a Power Query with other Power BI users
Roles for Data Stewards and Administrators
Roles for Data Stewards and Administrators are defined in the Power BI Admin Center:
Role Management page in the Power BI Admin Center
Ideally, you have a small group of Administrators - those are the folks allowed to define data sources and gateways and roles and such in the Power BI Admin Center. Even a member of the global Office 365 administrator role needs to be added here.
Also, ideally, you have a small subset of users who have been identified as data stewards. A data steward is allowed to certify a shared Power Query (certified meaning it's been validated and deemed trustworthy by someone who knows the data sufficiently).
Service, user, and virtual account to run the Data Management Gateway
This last section is for the administrator of the on-premises server where the source data is being accessed by Power Pivot and/or Power Query. If you need to set up scheduled data refresh, or expose data feeds via OData, that requires a Data Management Gateway to be installed on the server where the data resides (i.e., before it's imported into Power Pivot). When the Data Source Manager is installed, there are 3 things set up by the install program:
- Windows group called Data Management Gateway Users. These are the user(s) who can run the Data Source Manager application installed on the local server. The user ID of the person who is installing the DMG is added automatically. You might need to add additional people depending on how the server is managed.
- Windows service called the Data Management Gateway Host Service. This is the service that runs the DMG on the server and maintains contact with the Power BI site.
- Windows virtual account (managed local account) called NT SERVICE\DIAHostService. This is the context under which the host service runs.
Windows Service which runs the Data Management Gateway
Secure transmission of data refreshes from Data Management Gateway
When doing data refreshes in Power BI, or exposing data via an OData feed, it's typically very important to do so via HTTPS with an SSL certificate to encrypt the transmission so the data is not exposed. In a production (real) environment you'll want to use an SSL certificate purchased from a certificate authority. For testing purposes, it's ok to use a self-signed certificate. More info about doing that is here: Create a Self-Signed Certificate for Testing. A bit of additional info about the DMG and certificates is here: Troubleshooting Power BI for the IT Pro and here: Power BI for Office 365 Admin Center Help.
Ports for Data Management Gateway
The DMG on the on-premises server communicates with cloud services with outgoing ports 9350-9354. It falls back to 443/80 if the other ports are not open. No incoming ports are required from the internet because messages are received via Service Bus.
For OData feeds, however, it does require an open port of 8050 or 8051.
More information on this is available from this Channel 9 session called Deep Dive on the Data Management Gateway in Power BI.
As you can see, there's some things to be aware of to make sure your environment is secured the way you intend it to be. Handling of security is one of the reasons I'm an advocate of functional areas partnering with technical staff when implementing Power BI.
If you can think of something I missed, leave me a comment and I'll be sure to add it.
You Might Also Like...
Differences Between Power BI and SharePoint BI
Getting New Power Pivot Features & Other Office 2013 Updates