Data Management Gateway

  • Article

Microsoft Data Management Gateway connects on-premises data sources to cloud services for consumption. With Microsoft cloud services, such as Power BI for Office 365 and Azure Data Factory you get benefits including fast deployment, low maintenance cost, and flexible billing model while keeping your enterprise data on-premises. With Data Management Gateway, you can connect on-premises data to cloud services in a secure and managed way, to respond more quickly to changing business needs with a flexible, hybrid cloud platform. You can benefit from Microsoft cloud services while you keep your business running with the on-premises data.

Overview

Data Management Gateway is software that connects on-premises data sources to cloud services for consumption. With Data Management Gateway, you can:

  • Connect to on-premises data for Hybrid data access – You can connect on-premises data to cloud services to benefit from cloud services while keeping the business running with on-premises data.

  • Define a secure data proxy – You can define which on-premises data sources are exposed with Data Management Gateway so that Data Management Gateway authenticates the data request from cloud services and safeguards the on-premises data sources.

  • Manage your gateway for complete governance – You are provided with full monitoring and logging of all the activities inside the Data Management Gateway for management and governance.

Data Management Gateway has a full range of on-premises data connection capabilities.

  • Non-intrusive to corporate firewall – Data Management Gateway just works after installation, without having to open up a firewall connection or requiring intrusive changes to your corporate network infrastructure.

  • Encrypt credentials with your certificate – Credentials used to connect to data sources are encrypted with a certificate fully owned by a user. Without the certificate, no one can decrypt the credentials into plain text, including Microsoft.

  • Move data efficiently – Data is compressed and transferred in parallel, resilient to intermittent network issues with auto retry logic.

The following sections provide you a quick overview of how the Data Management Gateway is used by Azure Data Factory and Power BI for Office 365 services.

Azure Data Factory

Azure Data Factory is a managed service that you can use to produce trusted information from raw data in cloud or on-premises data sources. It allows developers to build data-driven workflows (pipelines) that join, aggregate and transform data sourced from their local, cloud-based and internet services, and set up complex data processing logic with little programming. The Azure Data Factory service enables the easy monitoring and management of these pipelines by providing rich visual experience offered through Azure Management Portal. See Introduction to Azure Data Factory for detailed overview of the Data Factory service.

To enable Data Factory pipelines in an Azure data factory to work with an on-premises data source, you need to add the on-premises data source as a linked service to the data factory by using either Azure Management Portal or Azure PowerShell. To be able to add an on-premises data source as a linked service to a data factory, you first need to download and install Microsoft Data Management Gateway on an on-premises computer and configure linked service for the on-premises data source to use the gateway either by using Azure Management Portal or Azure PowerShell cmdlets. See Enable your pipelines to work with on-premises data article for details.

Power BI for Office 365

The Power BI for Office 365 Admin Center allows IT administrators to register on-premises data sources with the portal, enable OData feed for the data sources and select tables/views to be included in the feed, and/or allow Excel workbooks stored in SharePoint Online to be refreshed with data from on-premises data sources. See Introduction to Power BI for Office 365 Admin Center for details.

The two scenarios that the Admin Center supports are:

  1. Enable OData Feed: In this scenario, an IT Administrator exposes data from an on-premises data source as an OData feed so that it can be consumed by users such as data stewards and information workers from Power Query for Excel. To expose data from an on-premises data source as an OData feed, the administrator registers the on-premises data source with the Power BI Admin Center, enable OData feed for the data source, select tables/views to be included in the feed, and grant users the access to the feed. See Create a data source and enable OData feed topic for details.

  2. Enable Cloud Access: In this scenario, an information worker creates an Excel workbook with a Power Pivot model that uses an on-premises data source of SQL Server or Oracle, tests it, and posts it to the SharePoint Online to share it with others. When the information worker tries to refresh data in the workbook on SharePoint Online, it fails to connect to the on-premises data source. The information worker then contacts an IT Administrator with a link to/copy of the Excel workbook (or) the connection string for the data source. The administrator extracts the connection string from the Excel workbook if needed, uses the connection string to register the on-premises data source with the Admin Center, enable cloud access for the data source, and grant access to appropriate users so that they can refresh the Excel workbook with the latest data from on-premises data sources. See Enable cloud access for a data sourcetopic for details.

You must have at least one gateway installed in your corporate environment and register it with the Power BI for Office 365 Admin Center portal before creating data sources in the portal. The Power BI Admin Center lets you create multiple gateways, and download and install gateways on multiple on-premises computers. See topics in the Data Management Gateway section of Power BI for Office 365 Admin Center Help.