Learn how the On-Premises Data Gateway lets Office 365 and Azure services access your on-premise data.
Office 365 and Microsoft Azure are enabling organisations to rapidly build new solutions to business problems in the cloud. But, all too often, the data you really want to work with isn’t accessible from the cloud; it’s locked away in on-premises databases, existing SharePoint sites, or spreadsheets in network file shares.
Wouldn’t it be great if there was some way of exposing this “stranded” data to the cloud? Well, there is!
Introducing the On-Premises Data Gateway
The On-Premises Data Gateway provides a simple, centralised way to connect cloud services such as Power BI, PowerApps, and Microsoft Flow to many different kinds of on-premises data. You only need one gateway for all of these services, and you can use it to connect to as many data sources as you like
Installation
Installation is straightforward, with almost no configuration needed. Having downloaded and run the installer, you will be prompted for a user account to use as the owner of the gateway, a name for the gateway, and a recovery key. The recovery key is needed if you ever need to restore the gateway, migrate it to a new machine, or to take over an existing gateway, so keep it safe.
The gateway should be installed on a machine that is always on – and naturally it needs to be able to connect to the data source(s) you want to use.
Connecting to On-Premise Data
The On-Premises Data Gateway can be used to connect to many different kinds of data, including Analysis Services Tabular or Multidimensional models, SQL Server databases, SharePoint lists, and Excel files. Exactly how you create a connection depends on what service you’re connecting from. While I won’t go into the precise details of creating a connection here, one important factor that I will mention is authentication.
The authentication details for each connection must be supplied at the time the connection is created. All queries and actions against the data source use these credentials. This has a couple of implications. Firstly, users can only create connections to data sources for which they already have valid credentials. Secondly, they can only access data or carry out actions which those credentials enable – so, for example, if a user only has Read access to an Excel file, they can create a connection which can read data for the file, but not one that can update it.
PowerBI data sources can only be used by PowerBI. However, connections can be shared and re-used between PowerApps and Microsoft Flow.
Security Considerations
The data source credentials are stored in the cloud using asymmetric encryption. They remain encrypted during transmission, and are only decrypted at the gateway when the data source is accessed, and the key is stored on the gateway server. That means the credentials remain secure at all times.
It’s important to remember that all access to the data source uses the same credentials. If a user creates a data source (say for a PowerBI report) that accesses sensitive data, that data source (and the report) may expose that data to other users.
When you create a connection in PowerBI, you can specify which users are allowed to publish reports using that data source. You can also specify a privacy level for the connection, letting you share it with specific individuals, an entire organisation, or even make it public.
Within PowerApps and Flow, you have the option to share the gateway with specific individuals or your entire organisation. Additionally, you can specify which data source types can be used:
Some connection types, such as SQL Server, also allow sharing to be controlled at the connection level. Others, such as File Sytem connections, are still in preview and do provide this capability yet.
Finally, it’s also important to remember that since all users access the data source using the same credentials, the data can’t be filtered by user (unless you build in additional mechanisms to do this) and any audit records will record all actions against the same account.
Licensing
To use the On-Premises Data Gateway in PowerBI requires a PowerBI Pro license, as does interacting with any report or dashboard that makes use of the gateway. Use of the On-Premises Data Gateway in PowerApps or Flow is included in all PowerApps Plans (except Office Business and Office Enterprise E1 SKUs).
Read more about and download the On-Premises Data Gateway on Microsoft’s website.