Yes, You Can Use Your On-Premises Data with Office 365

office logo v3

Say what? What! <edited out the back and forth dialog in my head between Samuel L. Jackson and some poor kid in an inappropriate-for-work movie that is playing in my head>



For a lot of people, hybrid just means Active Directory and works in both the cloud and on-premises. Then all of the other IT functionality is purely online or purely on-premises. Exchange is hosted in O365. They have SharePoint in both but neither talks to each other. There are a whole host of other solutions and calling them hybrid can be a stretch. I think of it more like, they have two data centers that just happen to have the same username and password.

Well, it doesn’t have to be that way. One of the smartest pieces of technology Microsoft has created in years flies under the radar but it can get you out of this “here or there” mentality. The software is called the On-Premises Data Gateway.

The On-Premises Data Gateway

The Data Gateway (that is what we are going to call it to get down on wordiness) allows you to connect to your on-premises data to several tools in the Microsoft Cloud. PowerApps, Power BI, Microsoft Flow, Azure Logic Apps, Azure Analysis services, and probably a few others all natively support the Data Gateway. This allows you to truly bridge the gap and build hybrid environments. It is also the answer to how do you deal with the fact that amazing tools like PowerApps, Power BI, and Flow will never come on-premises. No problem, you can bring on-premises to them.

Now you might be thinking, why is it you haven’t heard of this product before? Why can’t Shane tell us exactly what platforms it works with and where to get more info. Turns out, in my opinion, this is the only flaw with the Data Gateway. It’s greatest feature, is its biggest weakness.

The Best Feature

You only need one Data Gateway installed on-premises and it works with all of the tools I listed above. What simple form of genius is this? I am shocked that this single tool was built and got all of these other teams to play nice with it. Better news? Installing it takes about 5 minutes and there is almost nothing to configure. So why is this amazing news also a weakness?

The reason you haven’t heard of the tool and that the details are hard to come by is because the Data Gateway doesn’t have a standalone website. All of the documentation for the product is hidden on the various websites of the products it works with. This can be good if you only care about it from one point of view but I am a harlot when it comes to this tech. I want all up documentation. Sad face.

If you want more info or to install the product, then you need to go to the various product sites to do so. The nice thing is this gives you product-specific context. Here are the sites that I know of:

Are you overwhelmed by that? I was. So many Gateways but remember you only need one. So after you setup the Gateway for PowerApps, you are done. If next week you decide you want to also use on-premises data with Power BI, no problem. You do NOT have to install another gateway.

Speaking of installs. Because I love you, I have also gotten into this chaos. I have made two different videos. One is from the PowerApps point of view and one is from the Power BI angle. I found it makes a lot more sense to install, configure, and build something using the gateway if you do it product specific. Enjoy.


There is no license required to install the Data Gateway. Instead, the various products are where the licensing, if any, are handled. For example, with PowerApps the Data Gateway is available to almost everyone. The only exception is being users licensed as Office Business, Office Enterprise E1, and Office 365 Enterprise F1. Once again, the downside is I can’t point you to one place where that is called out. So make sure as you make plans to use this great tool, you dig on the licensing. This is especially true if you are in a mixed licensing environment. You will most likely be fine but I want you to look before you leap. Random fact? Power BI even has a personal Gateway mode. So cool.


I have bad news for your security and firewall teams. We don’t need them. Hooray. The very non-nerdy explanation for the way this works is the Data Gateway calls out to Azure Service Bus looking for work. Now if you have super outbound security and proxy servers (like we did in the late 90’s), then you may have to make some changes. But if I were you, I would not over think it, just install the Data Gateway and give it a try. If it cannot find the Azure Service Bus (the internet), it will let you know. Then you can try to configure around it.

There Is So Much Fun to be Had Here. What Are You Waiting For?

Imagine a Power BI dashboard showing on-premise and online data in the same report. Imagine workflows that take SharePoint content from on-premises and publish it to the cloud. The sky is the limit. You just need to deploy one instance of the Data Gateway and then you can go crazy. Leave me comments below and tell me what doors this opens for you. I love success stories.