Mar 5, 2020
When using cloud-based services like Office 365 and the Power Platform, it can be challenging to integrate with your on-premises resources. All of a sudden, your on-premises databases, APIs, file shares, and even your existing on-premises SharePoint infrastructure become impossible to reach. At least, not without making some giant holes in your firewall.
At our recent Toronto Citizen Developer User Group meeting, my good friend Luis Duran demonstrated how to use the on-premises data gateway to access a custom web API running on his workstation from Power Automate.
He had rehearsed the demo earlier that day from our offices. Still, he had changed many environmental variables by moving his demo to our meetup venue. Luis ran a web API from his workstation, over a different network (the guest wifi at the Microsoft office), using a new IP address.
Let’s say that if his demo didn’t work, no one would have blamed him. Heck, I tried to run a web API project using a static IP address on my workstation earlier in the day, and I had issues getting it to work.
But the demo worked!
"Any sufficiently advanced technology is indistinguishable from magic."Arthur C. Clarke
As Arthur C. Clarke once said: “Any sufficiently advanced technology is indistinguishable from magic.”. In today’s blog post, I’ll explain how to use the on-premises data gateway to access your local resources from the Power Platform.
Maybe I can prove that the Data Gateway isn’t magic?
You have services in the cloud, like Power BI, Power Apps, Power Automated, Azure Analysis Services, or Azure Logic Apps.
Your data is not in the cloud. It sits on your company’s network behind firewalls.
The on-premises data gateway works as a sort of bridge that lets your cloud-based services work with your on-premises data.
But it does so securely without requiring you to punch any holes in your firewalls.
And it is fast!
Do you use some chat app at work? Maybe you use Microsoft Teams or Twitter?
When someone sends you a chat, they’re not connecting directly to your workstation over some obscure port.
When your friend sends you a chat message, they send it to a gateway service that is usually in the cloud.
The gateway service then tells your app that you received a new chat message and displays it for you.
The chat gateway knows how to reach you because when you signed-in with your chat app, the app contacted the gateway and said, “If you need to send me notifications, let me know! I’m right here”.
It often uses the standard SSL ports (443) to keep prying eyes from intercepting your chat messages from the app to the gateway.
The on-premises gateway mostly works the same way. Except that instead of a chat app you install on your workstation, it is a Windows service you install anywhere on your network (e.g., your workstation or a server).
Instead of chat messages, it receives requests to retrieve data from available resources on that same network. It can also receive requests to update data.
When it launches, your on-premises gateway lets the cloud-based gateway service that it is ready. “Hey, let me know if you need any data. I’ll be here!”.
When your Power App, Power Automate, or any of the other services needs data, it sends an encrypted request with credentials to the cloud gateway service.
The gateway service then sends that encrypted request to your on-premises gateway.
And your on-premises gateway decrypts the request, extracts the credentials, and connects to your data source with those credentials. It sends the query to the data source and, when it gets a response, sends the encrypted results back through the gateway service, which sends it to cloud service that requested the data in the first place.
Microsoft has a pretty diagram that explains the concept. I encourage you to read more about how it works on their site:
Image: Microsoft
The good news is: you don’t need to punch any holes in your firewalls or anything like that.
However, you shouldn’t install the gateway on any rinky-dink computer. You need at least a 64-bit version of Windows 8 or Windows Server 2012 R2.
The machine should always be on and always connected to the Internet. Please don’t install it on a laptop that goes to sleep or puts the network adapter to sleep to save power.
Microsoft recommends an 8-core CPU with at least 8 GB of memory and a solid-state drive (SSD).
Try using a physical network connection over a wireless network.
The person installing the gateway on the device must also be the administrator of the gateway. You’ll need to have enough privileges on the computer you’ll be using to be able to install the application.
You can only install one type of gateway per computer (yeah, there are more than one types of gateway — we’ll explain later)
If you’re planning on using Windows credentials in your data sources, your gateway should go on a domain-joined machine.
And please don’t install the gateway on a domain controller.
At the risk of repeating myself: you’ll want to install gateways on computers that are always on and connected to the Internet.
The steps below are the same installation steps provided by Microsoft; you should check their instructions to see if they have updated.
If all goes well, you should find your gateway in the Power Platform site. For example, in Power Apps, you would go to Data > Gateways in your default environment.
You’re done! Now you’re ready to use the on-premises data gateway!
To use your gateway, just create a connection for one of the gateway-enabled connectors.
Right now, it works with the following connectors:
For example, to create a connection in Power Apps, follow these steps:
Some other connectors may prompt you to select at the start of your connection. For example, if you create a SharePoint connection, the first thing you’ll see is this option:
And if you select Connect using an on-premises data gateway, you’ll get prompted to enter more details:
If you scroll to the bottom of the configuration dialog, it’ll prompt you to select a gateway:
When some connectors only work with on-premises data, you’ll have to select a gateway right away. For example, when creating a connection to your on-premises File System, you’ll get the following dialog:
And if you fill all the configuration details, the last option is to select a gateway:
You can also use an on-premises data gateway with custom connectors.
For example, let’s say you have an API that is only available within your on-premises network, but you want to call it from within Power Apps or Power Automate. You would follow these steps:
You’ll be prompted to select a gateway only when you create a connection with your new custom connector.
Your gateway connections will appear in your list of connections, just like cloud-based connections. You can use them as any other regular connections within your apps.
It may not always be obvious that you can connect to an on-premises resource. For example, let’s say you want to create a new Flow with Power Automate that launches when a new file is created on your on-premises SharePoint server. You would follow these steps:
Note that you can also have multiple SharePoint actions in your Flow, and each one can use a different connection. It could come in handy if you wanted to move files from SharePoint on-premises to your SharePoint online instance, for example.
The Power Platform allows you to define environments to separate your production data from your sandbox, dev, and test data.
Unfortunately, as of this writing, the on-premises data gateways only work with the default environment on your tenant.
If you need to create a Power App or a Flow that use the gateway, they must also be in the default environment.
It means that your apps and Flows must also be in the same region as your default environment.
It is a serious limitation, and the Power Platform team is aware of this.
They’re super busy adding new features all the time, so you can’t blame them if it hasn’t been a priority for them.
I’m sure that one day they’ll get around to adding support for non-default environments, but until then, feel free to provide them with feedback.
They may seem magical, but on-premises data gateways allow you to access your on-premises data from the cloud in a secure way, and it is very fast and easy to use.
You should definitely consider establishing some form of governance before you use on-premises data gateways, but that’s a topic for another day.
I hope this helps?
Thanks to Luis Duran for doing all the hard work for our user group presentation. I simply recapped what he said in this post.