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

Power BI and Data Security – On-premises Data Gateway

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.

Power BI Gateway

The On-premises Data Gateway (a.k.a. Enterprise Gateway)

First, I will not be discussing the personal gateway in this post. If you have chosen to use the personal gateway, you have limited functionality and should consider using the on-premises data gateway for corporate use.

The on-premises data gateway (referred to as gateway throughout this post) “acts as a bridge, providing quick and secure data transfer between on-premises data and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services.” (ref) Much of what is discussed here will apply to all of the services referenced above, but our primary concern is related to Power BI. Please refer to references at the end of this post for details about data sources supported within the gateway.

The gateway enables Power BI to use on-premises data for data refresh and direct access with Direct Query and Live Connections (SSAS multidimensional and tabular models). The gateway is used to manage connectivity and data transfer between on-premises data and Power BI with data compression and transport encryption capabilities as part of the solution. Our focus here is related to the most common questions related to the gateway’s use with Power BI. We will discuss security related to the gateway and then to how the data is secure when using the gateway.

Security on the Gateway

When the gateway is installed, the default service account NT Service\PBIEgwService is created as a Windows service logon credential. This credential has “log on as a service” permissions. The first item to note: this credential is NOT used to access data sources. This service account has localized permissions to the server or PC it is installed on. It has no permissions to on-premises data sources or cloud services that use it.

In some situations, this can create issues with proxy servers. If you run into this situation, you can change the account to a domain account. Refer to the proxy configuration documentation to make that change. The recommendation is to change this to a managed service account in Active Directory to avoid resetting passwords which will disable the gateway and likely cause user satisfaction issues.

Data Sources in the Gateway

While the gateway does not have access to services or data sources, it does have the capability to decrypt the connection information used by Power BI to connect to on-premises services. When you add data source to the gateway you created, the credentials are encrypted using the key from the gateway.

Power BI Gateway Data Sources

Each gateway can manage multiple data sources. (NOTE: Best practices about location and performance of the gateway are not in scope of this post.) In my example, the gateway is providing access to a folder which contains receipt files. This will allow my Power BI solution to refresh data from the source. I can add a SQL Server connection as well if it is in the same network or context. The key here is that the gateway is an entry point for your on-premises data and is not limited to a single data source.

Credentials stored with the gateway cannot be decrypted in the cloud. The credentials are only decrypted by the gateway. When considering maintenance and configuration it is important to know that this is one of the key purposes of the gateway. Without a gateway, Power BI cannot access data in your on-premises solution. (Gateways are also required for Azure IaaS solutions. However, Azure SQL Database and Azure SQL DW do not require gateways as they are PaaS solutions and managed differently within Azure.)

Gateway Communication

All data and information between the gateway and Power BI is encrypted. One of the primary concerns is around opening ports and the communication protocol that supports this communication.

The first important item to cover is that there are no inbound ports used by the gateway. The gateway creates an outbound connection to the Azure Service Bus using a specific set of ports including TCP 443 which is used for Power BI (complete list of ports used). It is possible to force the gateway to use HTTPS in lieu of direct TCP for all of its communication. If you require this as an organization, be aware that there may be performance issues. This setting can be changed in the gateway properties and will require a restart of the service.

gw-onprem_01
Image Source: Power BI Documentation – On-premises data gateway

 

Data and the Gateway

The second primary question in regards to the gateway is around how data is handled. When a request from Power BI is submitted for data, the Azure Service Bus holds the request with the encrypted credentials. The on-premises data gateway polls the Azure Service Bus for requests. Once the request is received by the on-premises gateway, the connection is decrypted and the query request sent to the appropriate resource. The data is then encrypted and compressed at the gateway and returned to Power BI.

No data is stored in the gateway and the data is encrypted for transit.

Users and the Gateway

One last consideration is related to who can use a gateway. In Power BI service, when you manage the gateway (see diagram above about Data Sources), you have the ability to manage access to data sources by user. This functionality also supports security groups. When implemented, only users who have access to the data source can use the data source for Power BI datasets that they are deploying. This will prevent users from publishing content that would require direct access or data refresh to sources they should not use.

When they are able to use the gateway, they will have access to refresh scheduling and other options via the dataset properties (I use the Schedule Refresh option to open the dialog).

Datasets and Gateways.png

Final Thoughts

There are a lot of considerations for enterprises who plan to implement gateways in their organizations. The key is to remember this is a bridge that allows on-premises data to be accessed by cloud services. However, the cloud services do not initiate a direct request to the on-premises data. Microsoft has done a great job allowing for a hybrid approach that enables organizations to take advantage of cloud resources while minimizing the impact to their on-premises assets.

References

On-premises data gateway, March 16, 2017

Power BI Gateway Proxy

Power BI Gateways – March 2017 Update

 

Power BI and Data Security – Sharing Data

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.

Power BI Collaboration Basics

The focus of this post is on Power BI collaboration through sharing data using a variety of options in Power BI. While Power BI Desktop is a great tool for building datasets and reports, the real goal of a good BI solution is to share the information and analysis with the correct people in the organization who will be able to make decisions based on it. The Power BI service (https://app.powerbi.com) is the best way to do this.

First, the service requires a work or school based login, it does not work with a Microsoft, Google, Yahoo, or similar accounts. This is the beginning of the walls to protect your data. In most cases you will only be able to share data within your organization. However, there are methods to share dashboards publically. We will discuss those here and show how to turn off or regulate those features within Power BI and Office 365.

Power BI is built with Azure Active Directory (AAD) and customers who have or are in the process of implementing Office 365 are in the best position to establish proper security protocols to manage access to the Power BI service.

Power BI Dashboard Sharing

Power BI sharing can only be done on the service with dashboards. It does not work with reports or datasets and cannot be shared from Power BI Desktop. Initially, I viewed this a not a great option, but the reality is it is the best way to share content in a read only mode. A shared dashboard allows the users to interact with the data and view the underlying reports as part of the solution. This could be a good option when you want to share an executive dashboard with a security group or distribution list within your organization.

Share Dashboard

Even though the dialog shows email addresses to enter, security groups and distribution lists can also be added here keeping the AAD security model intact. Shared dashboards are marked with a distinct icon:

Dashboard Share Icon

Dashboards can be shared with free Power BI users within the organization. However, they will not be able to view any dashboards which use Power BI Pro features including workgroups, direct query, live connection, and other Pro based features. It is recommended that all users within an organization have a Pro account at this time.

Managing Share Capabilities within Power BI’s Admin Portal

As one can imagine, when the share dashboard capability was released there were reasonable concerns regarding sharing content outside the organization. When using an email address outside of the domain, users get warned they are sharing content outside of the organization.

Share Dashboard - outside

This is definitely a significant security risk. We recommend that this feature be disabled. Be aware that it is enabled by default (this may change for newer subscriptions, but most existing subscriptions have this feature on). You can deactivate this option in the Admin Portal – Tenant Settings – Export and sharing Settings as shown below.

Admin Portal - Sharing Settings.png

If you have some groups who should have permission to share outside the organization you can specify which groups have those permissions. This may be the case where you have a business to business arrangement where sharing a specific dashboard will improve your ability to communicate with the targeted organization.

If you have no compelling reason to share content outside your organization, this feature should be disabled!

Power BI Workspaces

Another way to compartmentalize or secure data is using Workspaces within Power BI. Every user, including free users, have access to My Workspace which is the default location for deploying Power BI and other BI assets. However, you also have the option to create additional workspaces as deployment targets. These Group Workspaces usually have functional and security separation associated with them.

Power BI Create Group.pngHere are the key characteristics of a group:

  • Group membership is individual users. Power BI Groups do not currently support security groups or distribution lists for membership.
  • Private vs. Public
    • Private groups limit access to members of groups.
    • Public groups work like a folder within Power BI and can be used to separate content but are not security restricted.
  • You can set the group to “read only” by setting the “Members can only view Power BI content” option.
    • This option disables editing on deployed reports and dashboards by members
    • Admin users within the group can edit reports and dashboards
    • The dataset area is not visible to members, only admins, which prevents creating new reports

With the current limitation around group membership (as of April 2017), I recommend using groups primarily as folders. As this situation improves, they will have more value as security groups as well. However, with the inability to manage these groups with AAD security groups, management will likely be prohibitive. It is likely that users will create groups to provide limited visibility with sharing, but this will create Office 365 groups to manage into the future.

Organizational Content Packs

Another method of sharing content is with organization content packs. Content packs allow the targeted users or groups to pick up the pack and use it in their workspace as needed. They can create copies of the content to use in their own dashboards and to create custom reports on the data. The data access and refresh are determined by the content pack creator. This is a way to not manage workspaces but still make content available to other users. Content packs can be made available to the entire organization, security groups, or distribution lists. Once a user gets the content pack, changes made by the owner can be updated to them as they occur.

You can limit who has permissions to publish content for the entire organization in the Admin Portal – Tenant Settings under the Content Pack Settings header. Users can continue to publish content to specific groups, but will no longer have the “My entire organization” option for publishing.

Publish to Web

Only one option counts here – disable this feature if you don’t want have a reason to display data on the internet!

My recommendation is that if you have a public facing version of your dashboards that do not any security at all, create a new subscription to manage this experience. You can disable this feature in the Admin Portal – Tenant Settings as shown below. All existing Power BI tenants have this enabled by default. You should disable this feature for your primary, internal Power BI implementations.

Publish to Web Power BI

Exporting Data and Printing Dashboards and Reports

Depending on the needs of your organization, you may need to restrict settings which allow data to be exported or printed. You have the ability to disable or enable exporting data from tiles or visualizations, exporting reports as PowerPoint presentations, and printing dashboards and reports for the entire organization or specific security groups. Both of these features have been highly requested and caution should be taken when disabling them. You can adjust these setting in the Admin portal under Tenant settings.

The Missing “Read Only” User

As you can see from the options to share or create workspaces there are methods which allow you to distribute content in read only fashion. However, in order to properly apply security and other features within Power BI, all of your enterprise users should be Power BI Pro users. Power BI Pro users still have a number of permissions that can cause issues within organizations including the ability to publish and share content from their workspace. Until Microsoft establishes a “read only” user setting or subscriber, organizations will need to manage content with the options noted above and determine the risk. In most cases, the risk is no more an issue than allowing users to use Microsoft Excel or Tableau. However, know your plan and be mindful of the updates from the Power BI team which will expand our ability to manage users.

References

Create a Group in Power BI

Sharing Your Dashboard