Organizations today face significant challenges in integrating data across cloud and on-premises environments. As businesses adopt more cloud services while maintaining existing on-premises infrastructure, establishing reliable connections between these environments becomes critical. The on-premises data gateway serves as a crucial bridge, enabling secure and efficient data transfer without compromising security or performance. This comprehensive guide explores the fundamentals of on-premises data gateways, their implementation in Power BI and other Microsoft services, installation processes, and best practices for enterprise deployment.
Table of Contents
What is an On-Premises Data Gateway?
An on-premises data gateway is a software component that facilitates secure data transfer between local data sources and cloud services. It acts as an intermediary, installed within an organization’s network, that establishes outbound connections to the Microsoft cloud and provides cloud services with controlled access to on-premises resources. The gateway eliminates the need to migrate databases to the cloud while allowing cloud services to utilize this data.
The gateway maintains a secure, encrypted connection channel and handles authentication between cloud services and your local data sources. It enables organizations to leverage cloud capabilities like visualization, automation, and analytics while keeping sensitive data within their network perimeter. This architecture maintains security compliance requirements while providing the flexibility to work with cloud-based productivity tools.
What is Power BI Data Gateway?
Power BI Data Gateway is a specialized implementation of the on-premises data gateway specifically optimized for Power BI services. It enables Power BI users to schedule automatic data refreshes, create real-time dashboards, and build reports using on-premises data sources without manually transferring files to the cloud. The gateway supports a wide range of data connections, including SQL Server, Oracle, SAP, SharePoint, and file-based systems.
While the standard on-premises data gateway serves multiple Microsoft cloud services, the Power BI Gateway offers specific features tailored to business intelligence needs, including query optimization for analytical workloads, support for DirectQuery mode (allowing live connections without importing data), and integration with Power BI’s semantic models. Power BI Gateway also enables report sharing and collaboration through the Power BI service while maintaining a secure connection to the underlying data sources.
How Does the Data Gateway Work?
1. High-level explanation of the architecture
The gateway is installed on a local machine that connects to both your internal data source and Microsoft’s cloud services. It acts as a middle layer that processes data requests, pulls the required data securely, and transmits it to the cloud application in real-time or on a schedule. The cloud service never directly accesses your internal data source.
2. Connection between on-premises network and Microsoft cloud services
Once installed and configured, the gateway creates an outbound connection from your on-premises environment to the Microsoft cloud. It uses this tunnel to transfer queries and data securely. Because the connection is outbound, there is no need to open firewall ports, reducing potential security risks while enabling controlled cloud access to internal data.
3. How data is encrypted and transferred securely
All communication between the gateway and Microsoft cloud services is encrypted using HTTPS and SSL protocols. Credentials are also encrypted and securely stored in the cloud using Azure Key Vault. This ensures sensitive information remains protected during transit and at rest, aligning with enterprise security standards and regulatory requirements.
4. Use cases for Power BI, Power Apps, Power Automate, Azure Analysis Services, etc.
The on-premises data gateway supports multiple Microsoft services. In Power BI, it enables data refresh and live queries. In Power Automate and Power Apps, it facilitates workflows and apps using local data. It also supports Azure Analysis Services and Logic Apps, helping businesses unify cloud automation with internal systems for seamless data integration and operations.
Enroll Now: Power BI Course
Types of On-Premises Data Gateways
1. Standard Mode (Enterprise Mode)
Standard Mode is designed for enterprise-wide implementation across departments. It enables multiple users to access various data sources through a single gateway installation. This mode supports all Microsoft cloud services including Power BI, Power Apps, and Power Automate. IT administrators typically manage these gateways centrally, allowing them to monitor usage, configure data source connections, and manage permissions from the admin portal.
2. Personal Mode
Personal Mode is limited to individual use within Power BI. It can be installed on a personal computer and only the installing user can access data through it. This mode doesn’t appear in administrative portals and cannot be shared with other users or services. Personal Mode is primarily used for testing, personal projects, or when users need to connect to local data sources without IT involvement for simple Power BI reports.
3. Virtual Network Data Gateway
Virtual Network Data Gateway runs entirely within Azure infrastructure rather than on an on-premises server. It connects to data sources hosted in Azure virtual networks, eliminating the need for local hardware. This option works well for organizations that have migrated databases to Azure but need the connectivity features of a traditional gateway, offering simplified management and integration with Azure security models.
4. Key differences and when to use which
Choose Standard Mode for enterprise scenarios requiring central management and multi-user access. Select Personal Mode for individual development, testing, or small projects with Power BI only. Deploy a Virtual Network Gateway when your data sources already exist in Azure but need gateway functionality. Standard Mode requires administrator rights to install, while Personal Mode can be installed by regular users with local machine permissions.
Read More: 12 Types of Filters in Power BI
Comparison Table: Standard vs Personal vs Virtual Network Gateway
| Feature | Standard Mode | Personal Mode | Virtual Network Gateway |
| User Access | Multiple users | Single user | Multiple users |
| Services Supported | All Power Platform services | Power BI only | Azure services (Power BI, Power Automate, Logic Apps, etc.) |
| Installation Location | On-premises server | Personal computer | Azure (deployed in a VNet) |
| Administration | Centralized via Gateway app | Managed by individual | Managed in Azure Portal |
| High Availability | Supports clustering | No clustering | Built-in Azure availability features |
| Best For | Enterprise on-premises data access | Personal Power BI reports | Secure Azure-hosted data access (within VNets) |
Also Read: Power BI vs Tableau
When Do You Need an On-Premises Data Gateway?
Scenarios where gateways are required
You need a gateway when connecting cloud services to on-premises databases like SQL Server, Oracle, or MySQL. File-based data sources stored on local servers or network shares also require gateways. On-premises SharePoint servers, Dynamics installations, and SAP systems all need gateways for cloud service connectivity. Real-time or scheduled data refresh in Power BI from any local source makes gateways necessary.
When it’s not required
Gateways are unnecessary when all your data sources are already cloud-based, such as Azure SQL Database, Dynamics 365 Online, or SharePoint Online. Web-based API connections to SaaS applications like Salesforce or Google Analytics don’t need gateways. Files uploaded directly to Power BI or stored in cloud storage services like OneDrive don’t require gateway connections. Microsoft 365 data sources connect directly without gateways.
Also Read: Power BI vs Excel
Key Considerations Before Installing the Gateway
1. Supported data sources and cloud services
Verify that your specific data sources are supported by the gateway. Common databases like SQL Server, Oracle, and MySQL work well, but some legacy systems may have limitations. Check compatibility with the cloud services you intend to use, as certain connectors may work with Power BI but not with Power Automate. Microsoft regularly updates connector support, so review the latest documentation for current compatibility information.
2. Network and firewall configurations
The gateway requires outbound connections to Azure Service Bus on ports 443, 5671, 5672, and 9350-9354. No inbound ports need opening. Proxy servers must allow these connections to Azure domains. For organizations with strict network policies, allowlist the necessary Azure service endpoints. Consider network latency between the gateway and data sources for optimal performance.
3. System requirements and prerequisites
The minimum system requirement to install the on-premises data gateway is 1 GB RAM, but Microsoft recommends at least 8 GB RAM for stable performance in real-world scenarios. The system should run a 64-bit Windows OS (Windows 8 / Server 2012 R2 or later) and have .NET Framework 4.8 installed. Avoid installing the gateway on domain controllers or systems running resource-heavy applications.
(Source – Microsoft Docs)
4. High availability and load balancing (gateway clusters)
For business-critical environments, set up gateway clusters by installing multiple gateways registered to the same cluster name. This provides redundancy if one gateway server fails. Requests are distributed across available gateways in the cluster. Each gateway must have network access to the same data sources. Regular monitoring helps ensure the cluster operates efficiently.
5. Security and compliance (SSL, Azure integration)
Encrypt data source connections using SSL/TLS wherever possible. Data in transit is always encrypted between the gateway and Azure. Consider identity requirements, as gateways can use various authentication methods including Windows authentication and basic authentication. Data source credentials are encrypted and stored in the Azure service. For regulated industries, document the gateway architecture as part of compliance frameworks.
Read More: Components of Power BI
How to Install and Set Up a Power BI Gateway
Step 1: Installing the Gateway
Download the on-premises data gateway installer from the official Microsoft website in the Power BI section. The download link is typically found on the Power BI Gateway documentation page. After downloading, run the installation file and follow the wizard prompts. You’ll need to accept the license terms and choose an installation location. When prompted, select either Standard Mode for organizational use or Personal Mode for individual Power BI access. Standard Mode requires administrator credentials and an organizational email address.
Step 2: Creating or Joining a Gateway Cluster
Gateway clusters provide failover support if one gateway becomes unavailable, ensuring business continuity for critical reports and applications. To create a cluster, set up your first gateway as normal, then install additional gateways on separate machines. During setup of subsequent gateways, choose the option to join an existing gateway rather than creating a new one. Enter the same recovery key used for the first gateway installation. The administration portal will display all gateways in the cluster with their online status.
Step 3: Connecting to Power BI, Power Automate, and Power Apps
After installation, the gateway appears in the Power BI service under the settings menu. For Power BI, navigate to the gateway section in settings and click “Connect” to add data sources. For each data source, select the connection type, enter server details, and choose the authentication method. Options include Windows authentication, Basic (username/password), OAuth, or database-specific methods. Once configured, the same gateway can be selected in Power Automate or Power Apps when creating connections to on-premises sources.
Also Read: What are Bookmarks in Power Bi
Using the Gateway in Real Scenarios
1. Setting up scheduled refresh in Power BI
To set up scheduled refreshes in Power BI, open the dataset settings for your report. Under the Scheduled Refresh section, toggle refresh on and set the frequency (daily or weekly) and specific times. The gateway must be online during scheduled refresh windows. You can configure up to 8 daily refresh times. Power BI will automatically use your configured gateway connection to access the data source based on the stored credentials. The refresh history tab shows success or failure status for previous refresh attempts.
2. Creating dataflows
Dataflows allow extracting, transforming, and loading data into Power BI without requiring direct report connections. To create a dataflow that uses on-premises data, navigate to the dataflows section in Power BI, create a new dataflow, and select your on-premises data source. The system automatically detects and uses your gateway. You can then transform this data using Power Query functions before saving. The processed data becomes available to multiple reports without each needing separate gateway connections.
3. Using the gateway in Power Automate and Azure Logic Apps
In Power Automate, create a new flow and add an action that connects to on-premises data. When configuring the connection, select your gateway from the dropdown list. You can create triggers that respond to database changes or actions that write data to local systems. For Azure Logic Apps, the process is similar – when adding a connector that supports on-premises connections, you’ll be prompted to select an available gateway if your account has access to one.
4. Enabling the gateway for Microsoft Fabric data pipeline
Microsoft Fabric data pipelines can use the on-premises data gateway to access local data sources. In the Fabric workspace, create a new pipeline and select an on-premises source. The system presents available gateways that your account can access. Select the appropriate gateway and configure the connection details. The pipeline can then extract data from on-premises systems, transform it within Fabric, and load it into your chosen destination while maintaining security and compliance boundaries.
Also Read: What is a Power BI Heatmap
Gateway Management and Monitoring
1. How to manage gateway settings in the Power Platform Admin Center
The Power Platform Admin Center provides a central location to manage all gateways in your organization. Navigate to the Gateways section to view all deployed gateways with their status, version, and administrators. From here, you can add or remove gateway administrators, view and manage data sources connected to each gateway, and check cluster health. You can also configure email notifications for gateway downtime or update availability to stay informed about potential issues.
2. Monitoring performance, failures, and refresh logs
Monitor gateway performance through the gateway app’s status page on the server where it’s installed. For more detailed information, check the gateway logs located in the Event Viewer under “Applications and Services Logs.” In Power BI, view refresh history for each dataset to identify failed refreshes and error messages. The Power Platform Admin Center shows cluster-wide statistics including queries processed and average response times. Set up automated alerts to be notified of persistent failures or performance degradation.
3. Updating and maintaining the gateway
The gateway application checks for updates automatically and installs them when available. You can configure update behavior in the gateway app settings, including scheduled update windows to prevent disruption during business hours. For manual updates, download the latest installer and run it on the gateway machine. Establish a regular maintenance schedule to review gateway logs, check resource usage on the host machine, and validate that all data source connections remain functional. Document configuration settings for disaster recovery purposes.
Also Read: Conditional Formatting in Power BI
Common Limitations and Challenges
1. Gateway downtime issues
Gateway downtime can occur when the host machine restarts, undergoes maintenance, or experiences network interruptions. Since the gateway must maintain a continuous connection to the Azure Service Bus, any disruption affects all dependent services. Organizations should implement gateway clusters for critical workloads and monitor the gateway service status regularly to minimize downtime impact.
2. Compatibility constraints with certain connectors
Not all data connectors work uniformly across Microsoft cloud services. Some connectors available in Power BI may not function in Power Automate or may have different parameter requirements. Custom connectors often have limited gateway support. Check Microsoft’s documentation for specific connector compatibility before building solutions that rely on gateway connections.
3. Limitations in Personal Mode
Personal Mode gateways only work with Power BI and can only be used by the person who installed them. They don’t support clustering for high availability and appear offline when the computer running them is turned off. Scheduled refreshes fail if the computer is not running at the scheduled time. They’re unsuitable for team projects or production environments.
4. Troubleshooting tips
When troubleshooting gateway issues, check the gateway machine’s connectivity to both the data source and the Azure Service Bus. Review gateway logs in the Windows Event Viewer for specific error messages. Verify that the service account has appropriate permissions to the data sources. Test the direct connection to the data source from the gateway machine. Restart the gateway service if it becomes unresponsive.
Conclusion
The on-premises data gateway bridges the critical gap between cloud innovation and on-premises data assets, enabling organizations to leverage Microsoft cloud services without migrating all data. By implementing gateways thoughtfully—considering high availability needs, security requirements, and performance implications—companies can build robust hybrid architectures that balance security and accessibility.
Best practices include deploying gateway clusters for mission-critical workloads, maintaining regular update schedules, documenting configurations thoroughly, and monitoring gateway health proactively rather than reactively. As organizations continue their cloud transformation journeys, the gateway remains an essential component that enables incremental modernization without disruptive data migrations.
Organizations should approach gateway planning as a foundational element of their data strategy rather than as an afterthought, engaging both IT infrastructure teams and business users to ensure the solution meets technical requirements while supporting business objectives.
FAQs: On-Premises Data Gateway
1. Can I use one gateway for multiple data sources and services?
Yes, a single Standard Mode gateway can connect to multiple data sources including different database types, file shares, and web services. The same gateway instance can simultaneously serve Power BI, Power Apps, Power Automate, and other Microsoft cloud services, centralizing your data access point and reducing infrastructure requirements.
2. What’s the difference between standard and personal mode in Power BI gateway?
Standard Mode supports multiple users and services, requires administrative privileges to install, enables centralized management, and supports clustering for high availability. Personal Mode works only with Power BI, can be installed without admin rights, supports only the installing user, cannot be shared, and does not support clustering.
3. Do I need a separate gateway for each user or service?
No, one Standard Mode gateway can support multiple users and services. The gateway manages connections based on the credentials provided for each data source. For performance reasons, you might deploy multiple gateways or clusters based on geographic location, department needs, or to separate development from production environments.
4. Is the on-premises data gateway free to use?
The gateway software itself is free to download and install. However, the cloud services that use the gateway (like Power BI, Power Apps, or Power Automate) may require paid licenses depending on your usage scenario. There are no additional charges for gateway usage beyond your existing cloud service subscriptions.
5. How do I secure my gateway and data connections?
Secure your gateway by installing it on a protected server, using strong recovery key passwords, and keeping it updated. Configure encrypted connections to data sources using SSL/TLS. Store gateway recovery keys in a secure location. Use Windows authentication or OAuth where possible instead of stored credentials. Review and rotate data source credentials regularly.
6. Can I use the same gateway across Power BI, Power Automate, and Power Apps?
Yes, a single Standard Mode gateway can be shared across all Microsoft Power Platform services. Once installed and configured, the gateway appears as an available connection option in each service when creating data connections. This unified approach simplifies management and ensures consistent data access across your entire Power Platform implementation.
Join thousands of others in growing your Marketing & Product skills
Receive regular power-packed emails with free tips to keep you ahead of the competition.