The On-premises data gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that is not in the cloud) and specific Microsoft Office 365 cloud services (Power BI, Microsoft Flow, Logic Apps, and PowerApps)
You can use a single gateway with different services at the same time. If you are using Power BI as well as PowerApps, a single gateway can be used for both. It is dependent on the account you sign in with.
Pre-requisites to Install on-premises Data Gateway
License Requirement
- Microsoft Provides Data Gateway server as a free product to encourage organization use Microsoft Cloud Services
Minimum Requirements:
- .NET 4.6 Framework
- 64-bit version of Windows 7 / Windows Server 2008 R2 (or later)
Recommended:
- 8 Core CPU (2 processor , 4 core each)
- 8 GB Memory
- 64-bit version of Windows 2012 R2 (or later)
- Drive space C:\ -100 GB
Related Considerations:
- The gateway cannot be installed on a domain controller
- If you are planning to use Windows authentication, make sure you install the gateway on a computer that is a member of the same Active Directory environment as the data source(s).
- You shouldn’t install a gateway on a computer, such a laptop, that may be turned off, asleep, or not connected to the Internet because the gateway can’t run under any of those circumstances. In addition, gateway performance might suffer over a wireless network.
- Analysis Services is not required to use the gateway. You can use the gateway to connect to an Analysis Services data source.
Servers DNS
It has inbuilt clustering feature for high availability, we need to two VMs to implement that.
- domain.com
- domain.com
Sign in account
Users sign in with either a work or school account. This account is our organization account. If you signed up for an Office 365 offering and didn’t supply your actual work email, it may look like nancy@domain.onmicrosoft.com. Your account is stored within a tenant in Azure Active Directory (AAD). In most cases, AAD account’s UPN will match the email address.
Example: datagateway@domain.com
Whitelisting of IP address and Ports
It is recommended that you whitelist the IP addresses, for your data region, in your firewall. You can download the Microsoft Azure Datacenter IP list, which is updated weekly. The gateway will communicate with Azure Service Bus using the IP address along with the fully qualified domain name (FQDN).
Microsoft Azure Datacenter IP list:
Ports:
The gateway creates an outbound connection to Azure Service Bus. It communicates on outbound ports: TCP 443 (default), 5671, 5672, 9350 through 9354. The gateway does not require inbound ports.
Here is a listing of the fully qualified domain names used by the gateway.
| Domain names | Outbound ports | Description |
| *.download.microsoft.com | 80 | HTTP used to download the installer. |
| *.powerbi.com | 443 | HTTPS |
| *.analysis.windows.net | 443 | HTTPS |
| *.login.windows.net | 443 | HTTPS |
| *.servicebus.windows.net | 5671-5672 | Advanced Message Queuing Protocol (AMQP) |
| *.servicebus.windows.net | 443, 9350-9354 | Listeners on Service Bus Relay over TCP (requires 443 for Access Control token acquisition) |
| *.frontend.clouddatahub.net | 443 | HTTPS |
| *.core.windows.net | 443 | HTTPS |
| login.microsoftonline.com | 443 | HTTPS |
| *.msftncsi.com | 443 | Used to test internet connectivity if the gateway is unreachable by the Power BI service. |
| *.microsoftonline-p.com | 443 | Used for authentication depending on configuration. |
Limitations of Analysis Services Live Connections
You can use a live connection against tabular or multidimensional instances.
| Server version | Required SKU |
| 2012 SP1 CU4 or later | Business Intelligence and Enterprise SKU |
| 2014 | Business Intelligence and Enterprise SKU |
| 2016 | Standard SKU or higher |
- Cell level Formatting and translation features are not supported.
- Actions and Named Sets are not exposed to Power BI, but you can still connect to multidimensional cubes that also contain Actions or Named sets and create visuals and reports.
Installation of on-premises Data Gateway Server
- Download the Data gateway server application for free
- Run the package to start the installation
- Click next to proceed.
- Choose the enterprise mode for full deployment.
- Click next to proceed installing the binaries.
- A customary warning to ensure that you have chosen the right network placement of the server for optimal performance. Please check the pre-requisite section for details.
- Chose the installation path and click Install to proceed.
- Sign in with an office 365 account to register the gateway service with Azure service bus.
- Choose register if you are installing this as the first gateway server for the organization. You can also choose migrate option if you are installing this as a second server for any of the use case supporting second server provided in the pop up.
- Set your recovery key in this step. This key will be needed if you want to scale up or recover a failed data gateway server to connect to existing data gateway Azure Service Bus.
- Once this step is completed, the configuration steps will automatically establish secured connectivity between the server and the Azure Service Bus.
- Upon successful configuration, you can explore additional options to troubleshoot/monitor the server.
- Service Settings
- Diagnostic – enable if you want additional troubleshooting log to be made available
- Network – enforce https mode for secured connectivity to Azure Service Bus
- Connectors – to set advanced configuration if you are using existing or different Azure Service Bus connector, imported into this server.
Test Connectivity to DataGateway Server from PowerBI
You can log in to Power BI for Office 365 and add Gateway Cluster. It should automatically show up the new Data Gateway Server name installed.
We will continue in the Part II to learn how we can make use of Data Gateway server to fetch data from on-premise SQL Server. Stay tuned till then. Do not forget to provide your feedback in the comment section.
