The Microsoft On-Premise Enterprise Data Gateway is (finally) here!

Last month, Microsoft finally released the Enterprise version of it’s On-Premise Data Gateway.  The name of the product really says it all:  The Data gateway is a server that sits in your On-Premise Datacenter (or closet), and allows communication with certain Office 365 Products, and certain data sources on-prem.  If your organization has started to heavily rely on Power BI, you may be familiar with the concept, or had installed the Personal version of the product in the past.  With the Enterprise version, you can now have multiple users and Office 365 products utilizing one gateway to access these On-Prem Data Sources!

Great!  What can I connect?

Right now, the data gateway will connect Power BI, PowerApps, Flow, and Azure Logic Apps to on-prem SQL Server, Analysis Services, SAP Hana, Oracle and Teradata sources.  There is also support for refreshing other on-prem data sources, but not direct queries of those sources.

That’s what I need to connect, what do I need for the data gateway to work?

Ah, now for the fun part.  One of the main things you will need is an Azure Service Bus.  The data gateway uses queues to transfer queries between Office 365 and on-prem.  This prevents direct communication between your on-prem and Office 365 tenant (a good idea), and reduces any exposure of your on-prem data sources to the internet (also a good idea). Setting up and Managing a Service bus is outside the scope of this article; If you do not have an Azure tenant, and are unfamiliar with configuring a Service Bus, there is a lot of good information available on how to set these up on Technet, or feel free to reach out to me at to see how Centric can assist with this.

Once you have your Service Bus configured, the data gateway will need a server in your on-prem environment.  As of right now, there is no High Availability option for the data gateway, but it is on the roadmap.  Therefore, I recommend a Virtual Machine that can be easily restored from snapshot in case of any issues that may occur.

You will also need an Active Directory Service account in Office 365 that has rights to your-on-prem data sources (The exception here is if you are connecting to Analysis Services, I’ll touch on that next).  If you are not synchronizing your On-Prem Active Directory to Office 365, you will run into issues.  I HIGHLY recommend you do this first if you are not already.  The data gateway will use the single Service account to authenticate to your on-prem data sources.

However, that is not the case for Analysis services.  The data gateway will pass the credentials of the Office 365 user account directly to analysis services for authentication.  Therefore if you are not synchronizing your on-prem Active Directory with Office 365, YOU SHOULD START NOW. 🙂

Lastly you’ll need to open up a few outbound Ports from your datacenter to the Internet (Unless you setup a site-to-site VPN to Azure, I would recommend that if you have the infrastructure capabilities).  Microsoft has posted a full listing of the ports needed here.  The nice thing about this architecture, is there are NO inbound ports that are needed!

How can I get everything installed and configured?

As shown above there are a few things you will need to have put in place in your infrastructure in order to get the gateway to work.  Microsoft has published as full walkthrough on the installation and configuration of the data gateway product here.  This can get you started in configuring and connecting your specific data sources to Office 365.

Of course, if reading through all of that sounds like a pain, feel free to reach out to me at to see how Centric can assist with this design and implementation from beginning to end.


Leave a Reply