You can connect your on-premises data to Azure services using a Data Gateway, which provides a secure connection between your on-premises data and the cloud.
The Data Gateway supports TCP/IP, HTTP, and HTTPS protocols, allowing you to connect to various data sources, including SQL Server, Oracle, and MySQL.
With a Data Gateway, you can access your on-premises data from Azure services, such as Azure Synapse Analytics, Azure Databricks, and Azure Logic Apps.
This secure connection enables you to integrate your on-premises data with cloud-based services, making it easier to build hybrid cloud solutions.
Use Cases and Deployment
You can use the Azure data gateway to create cloud-based Azure Logic Apps workflows that require data from on-premises software as part of their run.
These workflows can be triggered by various events, such as user input or scheduled tasks, and can perform a wide range of actions, including data processing and integration with other cloud services.
Cloud-based Azure Logic Apps workflows that require data from on-premises software as part of their run include:
- Cloud-based Azure Logic Apps workflows that require data from on-premises software as part of their run.
- Extending the capabilities of existing on-premises software by triggering Logic Apps workflows in the cloud.
By using the Azure data gateway, you can extend the capabilities of existing on-premises software by triggering Logic Apps workflows in the cloud, which can help to improve business processes and increase productivity.
Security and Authentication
Security is a top priority when it comes to connecting your on-premises data sources to Azure. Exposing your on-premises servers to the public internet is not recommended, but using an on-premises data gateway creates a secure read/write connection between your on-premises data sources and Azure.
A stored credential is used to connect from the gateway to on-premises data sources, and the gateway uses this credential to connect regardless of the user. There might be authentication exceptions for specific services, such as DirectQuery and LiveConnect for Analysis Services in Power BI.
To secure remote access to on-premises web applications, you can use Azure Active Directory's Application Proxy. This requires an Azure AD Premium P1 or P2 license, and allows users to access on-premise applications the same way they access M365 applications.
Security
Security is a top priority when it comes to protecting your valuable data and systems. Exposing your on-premises servers to the public internet is possible, but it's not the recommended approach.
Using an on-premises data gateway is a more secure option, as it creates a secure read/write connection between your on-premises data sources and Azure.
This gateway provides a safe and reliable way to access your data, reducing the risk of unauthorized access or data breaches.
By implementing an on-premises data gateway, you can ensure that your data is protected and secure, giving you peace of mind and confidence in your security setup.
Authentication to On-Premises
Authentication to on-premises data sources is a crucial aspect of security, and it's essential to understand how it works. A stored credential is used to connect from the gateway to on-premises data sources, regardless of the user.
The gateway uses the stored credential to connect, and there might be authentication exceptions for specific services, such as DirectQuery and LiveConnect for Analysis Services in Power BI. This means that the stored credential may not work for all services, and you may need to configure additional authentication settings.
To access on-premises resources, you can use either Hybrid Connections or VNet Integration in Azure Functions. This allows you to connect to your on-premises resources, such as SQL or BizTalk, from your Azure Function app.
Here are the options for accessing on-premises resources in Azure Functions:
Make sure to choose the correct hosting plan and operating system for your Azure Function app, as the consumption-based plan is not supported for on-premises integration.
Architecture and Setup
To set up a high availability environment for your data gateway, you can use data gateway clusters with multiple gateway installations. This ensures there are no single points of failure and allows for load balancing of traffic across gateways in the group.
Data encryption is handled automatically, so you don't need to worry about the security of your data as it travels through the gateway.
A high availability setup using data gateway clusters is a great way to ensure your data is always available and secure.
How it Works
Here's how the on-premises data gateway works its magic.
The gateway requires installation and setup by an admin, typically with Server Administrator permissions or special knowledge about on-premises servers.
An admin must install and set up an on-premises data gateway for developers to access on-premises data they already have authorized access to.
The gateway facilitates faster and more secure behind-the-scenes communication between the user, the gateway cloud service, and the on-premises data source.
All traffic originates as secured outbound traffic from the gateway agent, using only outbound connections that work with firewalls.
The gateway sends data from on-premises sources on encrypted channels through Service Bus messaging, which creates a channel between the gateway and the calling service without storing any data.
The data that travels through the gateway is always encrypted.
Here's a step-by-step overview of what happens when you interact with an element connected to an on-premises data source:
- The cloud service creates a query, along with the encrypted credentials for the data source, and sends it to the gateway queue for processing.
- The gateway cloud service analyzes the query and pushes the request to Service Bus messaging.
- Service Bus messaging sends the pending requests to the gateway.
- The gateway gets the query, decrypts the credentials, and connects to one or more data sources with those credentials.
- The gateway sends the query to the data source for running.
- The results are sent from the data source back to the gateway, and then to the gateway cloud service, which then uses the results.
Setup
To set up the foundation for your solution, you'll need to start with a few prerequisites. An Azure account and subscription are required, and if you don't have one, you can create a free account.
To begin the installation process, you'll need to download and run the gateway installer on a local computer. The minimum requirements for the local computer are specified in the main guide for installing the on-premises data gateway.
The installation process involves several steps, including reviewing the minimum requirements, keeping the default installation path, and accepting the terms of use. You'll also need to select the region for the gateway cloud service and Azure Service Bus messaging instance.
Here are the specific steps to register your gateway installation with the gateway cloud service:
- Select Register a new gateway on this computer > Next.
- Provide the email address for your Azure account and select Sign in.
- Select Configure to accept the default region, or change the region if it's not the closest one to you.
- Review the data gateway information and select Close.
Before you can use the gateway, you'll need to create the Azure resource for your gateway installation. This is a crucial step in setting up the solution.
To adjust communication settings for the on-premises data gateway, you'll need to follow these steps:
- Adjust communication settings for the on-premises data gateway
- Configure proxy settings for the on-premises data gateway
By following these steps, you'll be able to set up the necessary prerequisites and installation process for your solution.
Steps to Build
To build a high availability data gateway setup, you can use data gateway clusters with multiple gateway installations in standard mode. This setup avoids single points of failure and load balances traffic across gateways in the group.
First, you'll need to build a Virtual Network in Azure, which creates a private environment for your services instead of exposing them to the public internet. This is the first step in building the solution.
Next, build a private endpoint for Azure SQL, which inserts Azure SQL inside your private environment, eliminating internet access from the service. This is done by creating a private endpoint, which involves selecting the resource group, naming the endpoint, and selecting the region.
To create a private endpoint, you'll need to follow these steps:
- Click on + Create a private endpoint. A wizard with multiple steps will open.
- On the first window, select the resource group.
- Create the name for the private endpoint.
- Select the region. It should be the same as the Virtual Network region and the Azure SQL.
- The 2nd window has a confirmation of the target resource. Ensure SQLServer is selected and move forward.
- The 3rd window contains the details of the virtual network. Choose the virtual network and subnet to create the private endpoint.
- You can select or create an application security group for this private endpoint.
- You can select if the private endpoint will be static or dynamic.
- The 4th configuration is about the DNS Zone. It will be filled with default values. It’s the moment to choose how to manage your DNS naming resolution.
After creating the private endpoint, you'll need to build the Power BI Virtual Network Data Gateway to link Power BI with your virtual network. This involves registering the resource provider, creating a delegated subnet, and creating the Virtual Network Data Gateway.
To create the Virtual Network Data Gateway, follow these steps:
- On the New virtual network data gateway window, select the resource group.
- On the New virtual network data gateway window, select the virtual network.
- On the New virtual network data gateway window, select the subnet. Only subnets with the correct delegation will be listed.
- On the New virtual network data gateway window, choose the inactivity time before an auto-pause.
- On the New virtual network data gateway window, choose the number of gateways to be created.
- Click the Save button.
Once you have the Virtual Network Data Gateway set up, you can create your data source on the Virtual Network Data Gateway. This allows Power BI to access your private Azure SQL.
To create your data source, use Power BI Desktop to create a report using the Azure SQL Database as a source. Then, create a premium or PPU workspace in Power BI portal, publish the Power BI report to the created workspace, and refresh the created dataset.
Architecture
The architecture of a data gateway is a crucial aspect of its functionality. It's based on a diagram from Microsoft that illustrates how the gateway works.
Microsoft has a clear diagram that outlines the architecture of a data gateway. This diagram is a great resource to understand the inner workings of the gateway.
To get a better understanding, I recommend checking out the On-premises data gateway FAQ. It provides additional information and answers to common questions about the gateway's architecture.
A data gateway's architecture is designed to facilitate secure and efficient data transfer between different systems.
Accessing On-Premise Resources
You can access on-premise resources in Azure Functions using either Hybrid Connections or VNet Integration.
To connect to on-premise resources, you'll need to use a hosting plan other than the consumption-based plan, such as a premium or app service plan, and ensure the operating system is Windows.
Azure Functions allows you to access on-premise resources like SQL and Biztalk.
Here are the two methods you can use to access on-premise resources in Azure Functions:
- Hybrid Connections
- VNet Integration
You can also access on-premise resources in Azure Automation using the Hybrid runbook worker feature.
Azure Active Directory's Application Proxy provides secure remote access to on-premises web applications and allows for single sign-on.
To use Azure AD Application Proxy, you'll need an Azure AD Premium P1 or P2 license.
Here are some steps to expose your on-premise application or existing web API in Office 365 cloud:
- Add an on-premises application for remote access through Application Proxy in Azure Active Directory
- Secure access to on-premises APIs with Azure AD Application Proxy
- Use Azure AD Application Proxy to publish on-premises apps for remote users
- Deploy Azure AD Application Proxy for secure access to internal applications in an Azure AD Domain Services managed domain
A stored credential is used to connect from the gateway to on-premises data sources in Azure Data Gateway.
Frequently Asked Questions
What is the difference between Azure data Box gateway and Edge?
Azure Data Box Gateway is a virtual device, while Azure Data Box Edge uses a physical device supplied by Microsoft to accelerate data transfer. The main difference lies in their hardware setup, with Gateway operating in a virtual environment and Edge using a physical device.
What is the name of the Microsoft on-premise data gateway service?
The Microsoft on-premise data gateway service is called "PBIEgwService". It can also be found listed as "On-premises data gateway service" in the Services app.
What is the difference between on-premise data gateway and personal mode?
The main difference between on-premise data gateway and personal mode is the intended use: personal mode is for solo users, while on-premises gateway is designed for collaborative team environments. Choose the right one based on your work style and needs.
Sources
- https://learn.microsoft.com/en-us/azure/architecture/hybrid/gateway-logic-apps
- https://ashiqf.com/2020/06/08/multiple-ways-to-access-your-on-premise-data-in-microsoft-365-and-azure/
- https://learn.microsoft.com/en-us/azure/logic-apps/install-on-premises-data-gateway
- https://stackoverflow.com/questions/71163203/use-on-premises-data-gateway-in-azure-data-factory
- https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/using-virtual-network-data-gateways-to-secure-access-to-azure-sql-in-power-bi/
Featured Images: pexels.com
