Configuring the On-Premises Data Gateway for Microsoft Fabric

What Is the On-Premises Data Gateway?

The On-Premises Data Gateway is a lightweight Windows service that acts as the secure relay between your Microsoft Fabric cloud environment and on-premises data sources like SQL Server. Think of it as a bridge: when your Fabric pipelines need to read or write data to an on-premises database, they send requests through this gateway, which executes the queries locally and streams the results back to the cloud.

The gateway is installed on the same machine where your SQL Server runs, or on any machine that has network access to your SQL Server. Once installed and configured, it registers itself with the Microsoft cloud services and becomes available as a connection option in your Fabric workspace and Power BI Service.

Key Characteristics of the Gateway

• Shared Service: The same gateway infrastructure used by Power BI Service. If you already use Power BI with on-premises data sources, you likely have this gateway installed.
• Two Modes: Personal mode (for individual testing) and Standard mode (for enterprise production). Always use Standard mode in production — personal gateways are restricted and unstable.
• Lightweight: Minimal resource footprint; typically consumes less than 500MB of RAM during normal operation.
• Outbound Only: The gateway initiates outbound HTTPS connections to Microsoft cloud services. No inbound ports need to be opened, making it firewall-friendly.
• Credential Storage: Credentials are encrypted at rest and never transmitted in plaintext; the gateway uses them only to execute queries locally.

Step-by-Step Installation

# Gateway Installation Flow — Step by Step
1. Run installer.msi as Administrator
2. Accept license agreement
3. Choose installation path  (default: C:\Program Files\On-premises data gateway)
4. Select "Standard" mode        (NOT Personal)
5. Click "Sign in" — authenticate with Microsoft 365
6. Service begins registration with cloud
7. Name gateway:  TaxiBusiness-OnPrem-GW
8. Set recovery key  (store safely — do NOT lose this)
9. Wait for green "The gateway is online and ready" message

# Store in encrypted format (DO NOT share or commit to version control)

Gateway Name:     TaxiBusiness-OnPrem-GW
Recovery Key:     [Your-Secure-Passphrase-Here — 64 characters minimum]
Stored Location:  Company Password Manager / LastPass / Azure Key Vault
Last Updated:     2026-04-16
Registered By:    DataEngineering@company.com

Creating the Data Source Connection in Fabric

With the gateway installed and running, you now need to create a data source connection in Fabric that tells Fabric how to use this gateway to reach your SQL Server.

Navigate to Manage Connections and Gateways

In your Fabric workspace, open Settings (gear icon) → Manage Connections and Gateways. This is where all data source configurations are stored and managed.

Create a New SQL Server Data Source

1. Click New → Data Source
2. Select SQL Server as the data source type
3. Fill in the connection fields as shown below
4. Click Test Connection to verify
5. Click Save

Gateway cluster name:   TaxiBusiness-OnPrem-GW  (select from dropdown)
Connection name:        TaxiBusiness-OnPrem-Connection
Connection type:        SQL Server
Server:                 localhost  or  sql-server-hostname.yourdomain.com
Database:               CabBusinessDB        (created in Part 2)
Authentication method:  Basic
Username:               fabric_gateway_user  (read-only user from Part 2)
Password:               <your-read-only-password>
Privacy level:          Organizational

Privacy Level — Explained

The Privacy Level setting controls how Fabric handles query folding and data combining across sources:

• Public: No privacy restrictions. Fabric can combine data from multiple sources freely.
• Organizational: Internal to your organization. Fabric can combine it with other Organizational sources but not with Public sources. Use this for the cab business database.
• Private: Most restrictive. Fabric cannot combine this data with any other source. Reserved for highly sensitive PII or financial data only.

Verifying Gateway Connectivity from Fabric Pipelines

The ultimate test of gateway functionality is a successful pipeline run. When you create a Pipeline with a Copy Activity that sources from your SQL Server via the gateway, the gateway becomes the execution point for that query.

From Fabric Pipeline: Copy Activity Setup

In your Fabric Pipeline, when configuring a Copy Activity, set the Source to your SQL Server connection. A table browser dropdown will appear showing all tables in your database — this is the gateway doing the work, connecting to your SQL Server and enumerating the table list in real time. If this dropdown populates correctly, your gateway is fully operational.

When you run the pipeline, the Copy Activity sends the SQL query to the gateway, the gateway executes it on your SQL Server, and the results stream into your Fabric Lakehouse. If any step fails, the gateway logs will reveal the exact failure point.

Gateway Best Practices for Production

High Availability: Gateway Clustering

For production environments, never rely on a single gateway instance. Deploy a second (or third) gateway on a different machine and add it to the same cluster. This ensures that if one machine fails, your pipelines continue running on the other gateway instance without manual intervention.

To cluster gateways, install the gateway on a second machine with the same recovery key. During setup, choose "Add to an existing cluster" and provide the recovery key and the name of the primary gateway. Both gateways will be registered as part of the same cluster and will load-balance queries between them automatically.

Monitoring: Gateway Performance and Error Logs

The gateway writes detailed logs to a local file. Monitor this file to catch issues early:

• Log Location: %localappdata%\Microsoft\On-premises data gateway\GatewayErrors.log
• Rotation: Logs rotate weekly; old logs are archived automatically.
• Key Signals: Search for timeout, authentication failed, connection refused, or query execution error.

Set up automated log monitoring using Windows Event Viewer or a SIEM tool to alert your team when errors spike above a threshold.

Gateway Auto-Updates

Microsoft automatically updates the gateway service, typically with no downtime. However, after an update, always run a test pipeline to ensure compatibility. Document the gateway version and patch date for compliance and audit purposes.

Credential and Permission Management

The gateway account (fabric_gateway_user created in Part 2) must have the following SQL Server permissions on every database accessed via the gateway:

• db_datareader: Allows reading all tables and views — required.
• db_datawriter: Optional. Only if your pipelines write back to SQL Server.
• db_ddladmin: Never grant this. DDL (CREATE/ALTER/DROP) permissions should never be granted to the gateway account.

Audit these permissions regularly and remove access to databases that are no longer needed by your pipelines.

What Happens at Pipeline Runtime

Let's trace the full journey of a data ingestion request through the gateway — from pipeline trigger to data landing in the Lakehouse. This end-to-end understanding is critical for debugging and performance optimization.

1. Pipeline Trigger: You manually trigger pl_ingest_landing or it fires on a schedule.
2. Lookup Activity (Config Table): The first activity reads config.landing_control_tbl from the Development Warehouse. This is a Fabric-to-Fabric query — no gateway involved. It returns the list of enabled tables and their incremental fields.
3. ForEach Loop: For each enabled table in the Lookup result, the pipeline iterates and triggers a Copy Activity.
4. Copy Activity — Dynamic SQL: The Copy Activity builds a dynamic SQL query from the config table metadata. For example: SELECT * FROM dbo.DRIVERSENTITY WHERE LAST_MODIFIED_AT > '2026-04-15 14:30:00'
5. Query Sent to Gateway: The Copy Activity sends this SQL query to the gateway over HTTPS. The gateway receives it and recognizes the target data source based on the connection configuration.
6. Gateway Executes on SQL Server: The gateway (running locally on your machine) executes the SQL query against your on-premises SQL Server using the fabric_gateway_user credentials over TCP/IP port 1433.
7. Results Stream Back: The query results — potentially millions of rows — stream back through the gateway to the Pipeline Copy Activity in the Fabric cloud.
8. Data Lands in Lakehouse: The Copy Activity inserts the streamed data into the cab_landing.DRIVERSENTITY Delta table in the Lakehouse.
9. Update Config Watermark: After the Copy Activity succeeds, a Stored Procedure activity calls config.update_landing_config_watermark, updating the last_pipeline_runtime timestamp. This watermark ensures the next run only picks up new records.

If the gateway is offline or the SQL Server is unreachable, the Copy Activity will fail at step 5 with a timeout error. If credentials are wrong, it fails at step 6 with an authentication error. The gateway logs tell you exactly which step failed.

-- Full Load (load_type = 'full')
SELECT * FROM dbo.DRIVERSENTITY;

-- Incremental Load (load_type = 'incremental', incremental_field = 'LAST_MODIFIED_AT')
SELECT * FROM dbo.DRIVERSENTITY
WHERE LAST_MODIFIED_AT >= DATEADD(day, -30, CAST('2026-04-08 12:30:35' AS DATETIME));

-- The gateway receives this query via HTTPS, executes it locally,
-- and streams results back to the Fabric Copy Activity

Summary: The Gateway Is the Bridge

The On-Premises Data Gateway is the critical infrastructure that enables Microsoft Fabric to reach your on-premises SQL Server. Without it, your cloud-native pipelines have no way to read from legacy systems. With it properly configured, you have a secure, scalable, monitored connection that can handle high-volume data movement — all without opening inbound firewall ports.

Key takeaways from this part:

• Install the gateway on a 24/7 machine that has network access to SQL Server.
• Always use Standard mode, not Personal mode, for production deployments.
• Create a data source connection in Fabric that points to your SQL Server through the gateway.
• Test the connection before building any pipelines against it.
• Deploy at least two gateways in a cluster for production high availability.
• Monitor gateway logs and document auto-update behavior.
• The gateway account must have db_datareader permissions on all databases it accesses.

In Part 4, we'll build the Fabric foundation: workspaces, lakehouses, warehouses, and the config tables that control your entire pipeline orchestration. The gateway is the bridge — the config tables are the brain.

Rohit Ram

Senior Data Analyst · LinkedIn

Building production-grade data platforms on Microsoft Fabric. This series documents a real project — every SQL query, every design decision, every challenge encountered and solved.