Power BI and Data Security – App Workspaces and Power BI Apps

Power BI Security LogoShortly after I published the Power BI Security Sharing Data post in April, Power BI Premium, Power BI Apps, and Power BI App Workspaces were released. These changes impacted that post in many ways. As part of the follow up, I also did an updated webcast with Pragmatic Works. This is a follow up post with some of the changes called out.

We are in the process of restructuring our organization around Apps and App Workspaces. Here are some of the highlights and changes related to sharing data using these new features.

Information Architecture and the Importance of Planning

My company, Pragmatic Works, uses a number of collaborative features in Office 365 including Planner, Teams, SharePoint, and Power BI. With this level of usage, a number of Office 365 groups get created. As we begin the process of updating our reporting structure, we will be using the App Workspace model to manage content creation and the Power App model to deploy content to users.

Before we began, we had to understand who the content creators are and who the consumers would be. App Workspaces are currently managed as Office 365 groups. We have a lot groups that match to our consumers, however, they really don’t work for content creators. Why? As we began the research, consumers exist in the current groups and are excellent targets. App Workspaces already exist for these users and groups due to our use with Teams and SharePoint. But due to the current limitations within Power BI and Office 365 with regards to group management, we need to create new App Workspaces, which also create new Office 365 groups to manage content creators. Typically these groups will be small and easy to manage. By limiting the users in these workspaces, we are also able to keep the additional clutter that is required.

In our process, we treated the end result as the guidance for the required workspaces. Each workspace creates and app that we want to target a specific set of consumers. By starting there, we created the list of workspaces we need to create. Because the apps and workspaces have a 1:1 relationship with each other, the apps (collection of Power BI content with the same permissions) are the determining factor for whether a workspace will be required. Our goal was to have the appropriate level of security while still minimizing management of the additional workspaces.

App Workspaces

We created the App Workspaces based on our Information Architecture Plan. The workspaces were created with two admins and set with members who would be content creators. Part of our exercise was to understand the impact of changing roles in Office 365 and related products such as Teams. What we learned is that Admin and Owner roles are shared throughout and managed by the Office 365 group. If you make a user an Owner in Teams they become an Admin in the matching Power BI Workspace. THIS IS IMPORTANT! While creating additional workspaces for report creation adds complexity by creating Office 365 groups, we have different security and content management rules for Power BI groups.

The Admins have the ability to add users to the group. Members do not. Also a Team, for instance, may have 100s of members who are essentially consumers. We are using the same role, Member, to assign to content creators. Consumers will use the Power BI Apps to view and consume the data made available. Because of this distinction, we created new Power BI App Workspaces.

When creating Members in Power BI Workspaces, you have the option to make those members View Only. However, doing so means all content creators will need to be Workspace Admins. This may work well for your organization, but remember Admins have elevated permissions as they are also Owners in Office 365 groups.

Preventing App Workspace Creation

Currently the only way to prevent App Workspace creation in a Power BI subscription is to disable the ability to create Office 365 groups or limit that capability to a small group of people. (NOTE: This affects all Office 365 applications which use Groups to segment the app such as Teams.) This is done using PowerShell. You can find details here including what applications are affected by this change.

Power BI Apps

In order to use Power BI Apps, all users need to have a Power BI Pro license or the apps need to be deployed to Power BI Premium. Whether you choose to use Pro or Premium should be evaluated for your organization. With current retail pricing, around 500 consumers is the “break even point” when only considering licensing. I will be discussing non-license related reasons to choose Premium in a later post.

When publishing or updating an app as noted in the images below. You have the ability to assign permissions to the app. Unlike Workspaces, you are able to assign distribution lists, individuals, and security groups to an App. This allows you to manage consumers using Active Directory (AAS). PBI Workspace and App

PBI App Permissions

Once Apps are deployed, a link can be sent to the users or they can find it in their available apps from Microsoft Appsource and their organizations deployed apps. They should only see apps they have been given permissions to. Once they get the app and open it, they have read only access but full Power BI interactive capabilities.

Power BI Apps will honor Role Level Security (RLS). However, unlike content packs, users cannot modify or change any content in the app including dashboards. When using apps, you are essentially creating the entire experience for the user and it cannot be “personalized” with their own dashboard or other updates. For enterprise reporting scenarios, this makes sense. For self-service or configurable solutions, you should still consider Content Packs.

To finish our story, we will be deploying Apps based on reporting groups within our Active Directory structure. This will allow us to control access to reporting through standard processes. At the moment, a group of us will manage the Workspace groups to prevent unwanted exposure to data and to manage report “creep”. In some ways, this is contrary to the original purpose of Power BI as a self service product. We are not limiting our teams capability to do their own report creation, app deployment, or self service analytics. We are making a point that the Enterprise Reporting will be managed which most organizations need on some level. If you have a great report you want to include, the Power BI Desktop allows us portability. The only not portable portion is the Dashboard itself. Hopefully we will be able to transfer that between workspaces in the future.

Power BI and Data Security – Free User’s Cannot Share, Read Only in Premium

Power BI Security LogoAs part of the Power BI Premium release, Microsoft changed how the “free users” in Power BI work within the platform. There are two key changes that affect the data security within your organization.

Power BI Free Users Cannot Share

One of the key areas of concern around the free user accounts was the fact that a corporate user can deploy content to the Power BI service (online). This would allow users to unintentionally (or intentionally) share data with others who would normally not have access to that data. When Microsoft released Power BI Premium, this capability was removed. While Power BI Free Users have access to all of the core capabilities of the product, they are not permitted to share or participate in the collaboration in workspaces. Essentially they only have access to My Workspace.

Power BI Free User Workspace 1

If they try to create an App Workspace, they get prompted to upgrade.

Power BI Free User Workspace 2 - Dialog

Free Users Are Read Only in Power BI Premium

When a customer chooses to use Power BI Premium, they can take advantage of “unlimited”, free, read only users. I called out the fact that Power BI did not support free users in a previous post about sharing content. Now with Power BI Apps and Premium, free users are turned into Read Only users. This is a huge win for the Power BI user community. This currently only works with Premium, so if security and managing content creation are key to success within your organization, you should be reviewing Power BI Premium.

I will have a follow up post on how Power BI Apps and App Workspaces impact data security in Power BI soon. If you want to have a look at creating and using Apps and App Workspaces check out this post on the Power BI site.

 

Thoughts about the Microsoft Data Amp Announcements

Microsoft conducted a live event called Microsoft Data Amp to announce a number of key features and releases for SQL Server on premises and data platforms in Azure (such as Azure SQL DB and Azure Data Lake). Some of these include features that I have been waiting to see. Here are some of announcements that I am excited about.

Microsoft Data Platforms 
Intelligent – Trusted – Flexible
On-premises & Cloud

SQL Server 2017

Yes. Microsoft has officially announced that SQL Server vNext is SQL Server 2017. The marquee feature being released in SQL Server running on Linux. But this also shows Microsoft is increasing its innovation efforts with SQL Server with an even shorter time between releases.

CTP 2 of SQL Server 2017 has been released today and includes an number of analytics features such as support for graph processing and graph queries. It will be the first commercial database with built in support for AI and deep learning database applications using R and Python scripts. Check out all the database engine improvements.

Azure SQL Database

Microsoft is bringing even more symmetry between the on-premises product and the PaaS product. The goal is to support upgrades or migrations to Azure SQL DB with minimal effort and no changes. Here are some of the features that are coming to Azure SQL DB soon:

  • Support for SQL Agent
  • 3-part names
  • DBMail
  • CDC
  • Service Broker
  • Cross-Database and Cross-Instance querying
  • CLR & R Services
  • SQL Profiler
  • Native backup-restore
  • Log shipping
  • Transactional Replication

These features will definitely bring more parity to the platforms. A number of these features are key for some of my clients to move to Azure SQL DB.

Migration Project for Azure SQL DB

Whether you have SQL Server, Oracle, or MySQL, you should be able to migrate your database to Azure SQL DB in “five simple steps”. While a great tool, I am interested in exploring this more with Oracle in particular. You can create a project in Azure that let’s you choose the source database and platform and target a Azure SQL DB then move the schema and load the database. While I am skeptical on the full capability of this solution, I look forward to exploring it more.

Azure Analysis Services is GA

The last topic I am going to bring up is Azure Analysis Services. This service is now GA which brings a great service to the PaaS space in Azure. Check out the capabilities here.

Final Thoughts

Microsoft announced much more than I highlight here including tighter AI integration into the data engine, R Server 9.1, and planet scale Document DB. Check out the Microsoft Data Amp site for more videos on what’s coming to Microsoft’s data platforms.

 

Power BI and Data Security – Row Level Security (RLS)

Power BI Security LogoAs Power BI becomes more prevalent in data analytics and visualization within the enterprise, data security becomes a significant concern. Power BI at its best is deployed to the Power BI service hosted on Microsoft’s Azure platform. Every enterprise should understand the level of security available with their data. Companies who have made the leap to cloud technologies such as AWS, Microsoft Azure, Salesforce, and Microsoft Office 365 should have an understanding of the data compliance and security capabilities of those solutions. However, companies who want to take advantage of Power BI but have just started their cloud journey or are cloud adverse need to know the nuances of Power BI and security.

I have been involved with data and cloud security questions a lot of the past few years. With Power BI’s rise in significance, I have had to answer more specific questions about the service. In order to provide proper guidance and not have a reference for myself, I am putting together a short series of posts on various data security items in Power BI. The topics included enterprise gateway, privacy levels, data classification, and compliance. The focus of these articles are related to using the Power BI service as this is the cloud implementation of Power BI. The desktop has setting which impact deployment of assets, but is not the focus of this series.

The Power BI service is updated frequently. These articles were created based on the Power BI implementation in early April 2017. You may find improvements and changes that impact your experience that are based on newer releases. Feel free to add comments to highlight changes.

Row Level Security in Power BI

Row level security is the ability to filter content based on a users role. There are two primary ways to implement row level security in Power BI – through Power BI or using SSAS. Power BI has the ability in the desktop to create roles based on DAX filters which affect what users see in the various assets in Power BI.

In order for this to work, you will need to deploy to a Workspace where users only have read permissions. If the members of the group associated to the Workspace have edit permissions, row level security in Power BI will be ignored.

Power BI Manage Roles.png

Both DirectQuery and data loaded into the model support RLS is the manner described above.

LiveConnection

SQL Server Analysis Services implements RLS on its own. SSAS requires the enterprise gateway to implement LiveConnection and RLS. RLS is supported by using EffectiveUserName on the connection from Power BI to the on-premises SSAS instance. (Refer to documentation on setting up live connections to SSAS.) This method works for both multidimensional and tabular models.

References

BI SSAS Connector Deep Dive (older content but good information)

Power BI SSAS Tabular Data

Power BI Admin for RLS

Power BI Row Level Security

Row-Level Security for Cloud models and DirectQuery

Tabular Model Row Level Security White Paper

 

 

Power BI and Data Security – Data Classification and Privacy Levels

Power BI Security Logo

As Power BI becomes more prevalent in data analytics and visualization within the enterprise, data security becomes a significant concern. Power BI at its best is deployed to the Power BI service hosted on Microsoft’s Azure platform. Every enterprise should understand the level of security available with their data. Companies who have made the leap to cloud technologies such as AWS, Microsoft Azure, Salesforce, and Microsoft Office 365 should have an understanding of the data compliance and security capabilities of those solutions. However, companies who want to take advantage of Power BI but have just started their cloud journey or are cloud adverse need to know the nuances of Power BI and security.

I have been involved with data and cloud security questions a lot of the past few years. With Power BI’s rise in significance, I have had to answer more specific questions about the service. In order to provide proper guidance and not have a reference for myself, I am putting together a short series of posts on various data security items in Power BI. The topics included enterprise gateway, privacy levels, data classification, and compliance. The focus of these articles are related to using the Power BI service as this is the cloud implementation of Power BI. The desktop has setting which impact deployment of assets, but is not the focus of this series.

The Power BI service is updated frequently. These articles were created based on the Power BI implementation in early April 2017. You may find improvements and changes that impact your experience that are based on newer releases. Feel free to add comments to highlight changes.

The following items are part of the series because they imply additional levels of data security. In order to help alleviate confusion on the implementation and use of data classification and privacy levels I have included them in the conversation.

Power BI Data Classification

Data classification is a method available in Power BI which allows users to tag dashboards to alert consumers of the data to sensitivity in the data. Data classifications are enabled and configured at the tenant level. Once established, a visible tag will be present on dashboards.

PBI Data Classification

Data classification is not a data security implementation. Data classification is only a tag for dashboards and can only be applied on the service not on Power BI Desktop. If you plan to implement this feature you need to have matching policies and practices to support its use.

Power BI Privacy Levels

Power BI Privacy Levels “specify an isolation level that defines the degree that one data source will be isolated from other data sources”. After working through some testing scenarios and trying to discover the real impact to data security, I was unable to effectively show how this might have any bearing on data security in Power BI. During one test was I shown a warning about using data from a website with data I had marked Organizational and Private. In all cases, I was able to merge the data in the query and in the relationships with no warning or filtering. All of the documentation makes the same statement and most bloggers are restating what is found in the Power BI documentation as were not helpful. My takeaway after reviewing this for a significant amount of time is to not consider these settings when evaluating data security in Power BI. I welcome comments or additional references which actually demonstrate how this isolation actually works in practice. In most cases, we are using organizational data within our Power BI solutions and will not be impacted by this setting and my find improved performance when disabling it.

Here is the only instance where I was prompted about privacy levels while working with this. After marking it “public” I proceeded to merge the data with a private connection. You may have a different experience that what I have and I would welcome comments to further the discussion on this topic.

Privacy Level Setting Dialog.PNG

References

Dashboard Data Classification

Power BI Desktop Privacy Levels

Power Query (Excel) Privacy Level Settings

Power BI Community Response on Privacy Levels

March 2016 Power BI Desktop Update – Search for Privacy Level

Thoughts on data, business analytics, and the SQL Server community

%d bloggers like this: