Making SQL sense

+44 (0)20 3051 3595 info@coeo.com
coeoClose

Making SQL sense

+44 (0)20 3051 3595 info@coeo.com

Careers

We're looking for people who share our commitment to excellence in Microsoft's data platform to join us consultants working on exciting business intelligence, analytics, and SQL Server projects on-premises and in the cloud.

Power BI Security in Ten Steps

The Coeo Blog

Are you fearful of migrating to a new data visualisation solution or hosting data in the cloud? The power of Power BI is too much to ignore and offers many security features to ease your concerns.

Padlock

Here are ten steps you should take to secure your data.

1 Azure AD Conditional Access

We will assume you are familiar with basic Power BI concepts. The one sentence summary is, you develop locally using the free Power BI desktop tool and publish to the Power BI cloud service to share content.

Azure Active Directory (AD) governs authentication to the Power BI service. Sync your AD to Azure using Azure AD connect to provide single sign-on for on-premises and cloud.

Use Azure AD Conditional Access (Azure AD premium required) to provide additional layers of security and implement policies such as:

  • Multi-factor authentication (MFA)
  • The machine must be domain joined
  • Block access from specific Operating Systems
  • Only allow access from locations marked as trusted
  • Block access from individual clients such as mobile

Use Azure AD Conditional Access to turn on MFA and block unauthorised access

2 Workspaces

You publish content from Power BI desktop into workspaces in the cloud service. Add your report author colleagues to the workspace in one of the following roles with ascending privilege levels:

  • Contributor
  • Member
  • Admin

Apply the principle-of-least-privilege as you have always done with other data. Workspace admins can delete the workspace, who needs that permission in your company?

Workspace

Don’t give report consumers any workspace permission or add to any roles, but we will come to that next.

Create workspaces and grant permissions selectively

3 Apps

You are now a workspace expert, but how do you distribute reports to our report readers mentioned above?

The publication of content is where apps come in. Each workspace is called an app workspace and provides the ability to neatly package all content into a single entity called an app. You then give access to the app to individuals, groups or your whole company.

Recipients access via the Apps menu for a fantastic read-only data visualisation experience. Report consumers can fully interact with the data by clicking on charts, slicers and filters, but cannot edit any content. The app author can update the app and edit permissions very easily after publication.

Distribute content via apps

4 Admin

Power BI Administrators have the keys to the castle to administrate your Power BI tenant. Admins have access to the Power BI admin portal to perform actions such as:

  • Control who can create workspaces
  • Specify if data can be shared externally
  • Limit the ability to export data from Power BI

Your Office 365 Global Administrator is a Power BI Administrator by default. The Office 365 Global Administrator also assigns other users as Power BI Administrators. Ensure Power BI admin rights are granted selectively to only those users requiring these elevated permission levels.

Be very selective in granting admin permissions

5 Row Level Security

Row-level security (RLS) provides the ability to publish the same report to your user base, but expose a different horizontal slice of data to each person. Think:

  • A sales manager only see sales for her region
  • An athlete only sees their performance metrics
RLS

Implement row-level security either in your Power BI data model or within Analysis Services (more later). In Power BI, hit the “Manage Roles” button on the Modelling ribbon. From there, add a DAX expression that returns a Boolean TRUE/FALSE to filter a column. Add users to roles in the Power BI service to see row-level security in action.

The great benefit is you avoid maintaining multiple versions of the same visualisation to expose different data. Publish a single report for a clean solution to match all security requirements.

Apply RLS to secure data and ease administration

6 Complementary Components

A concern of many with Power BI is the proliferation of data in numerous Power BI data models. Think many developers connecting to source data and publishing siloed data sets in an un-governed manner that is difficult to administrate and maintain.

A solution to this problem is to create a single-version-of-the-truth organisational curated data model in Azure Analysis Services used by all for reporting purposes. This approach avoids storing data in many Power BI desktop files and cloud datasets.

Rather than each report developer repeating the same extract transform and load logic, IT performs this task once. The resulting single data model is more consistent and straightforward to maintain. Use Power BI just for report rendering and connect to Azure Analysis Services via a Live Connection. Data resides once in Azure Analysis Services, avoiding data sprawl.

Apply security best practice in all tiers of this architecture. Use the principle-of-least-privilege when each component communicates with another. For example, configure Azure Analysis Services to authenticate to your data warehouse via a login that can select from particular abstraction layer views only, the login will have zero permissions on underlying tables or the ability to edit data.

Create a single version of the truth

7 Auditing

Trust, but verify. Admins and people with appropriate authority have access to the Office 365 Admin Center. This treasure trove of information logs many Power BI events allowing you to search by date range and user. Examples of events audited:

  • Created Power BI dataset
  • Deleted Power BI report
  • Exported Power BI report visual data
  • Shared Power BI dashboard

Use the Audit Logs as a crucial tool to meet your regulatory and compliance commitments.

Review the audits to ensure compliance

8 Classification

The first objective of your compliance regime is to demonstrate you know where all your sensitive data is. You also need to be clear which of your dashboards are affected by each piece of legislation relevant to your organisation.

Power BI offers the facility to classify your dashboards based on categories you define. You might create labels such as Classified, Internal, Sales or PII. Also, specify a URL to send your users to a location for further information about the security classification. 

Classification

Classification is not a security feature as such but is a necessary action you should take to document the dashboards you publish and communicate with others where sensitive data resides.

Classify your dashboards, create a URL to educate your users

9 Sharing Externally

Power BI offers the ability to share reports outside of your organisation or even publish to the public internet. If this causes you to shudder, turn these features off. Your Power BI admin (remember them from above) should open the admin portal and move a slider – problem solved.

Turn off sharing externally (unless needed)

10 Exporting Data

You have implemented all the steps above and are sleeping comfortably with a secure solution your users love. However, there is one last action to take. Power BI offers the ability to export data out of its boundaries by printing or exporting to either Excel or PowerPoint. All the Power BI security in the world will not help you if people leave your building with confidential data in spreadsheets on their laptop.

You know the drill by now, admin portal, turn off.

Turn off exporting data (unless needed)

 

Steps

 

There are ten steps and actions you should take. If you would like further help, Coeo offers Power BI training and consultancy. If that interests you, please get in touch (https://www.coeo.com/contact-us/), we would love to hear from you.

Develop your Power BI skills - sign up for a training course

Subscribe to Email Updates