Fabric Connections and Gateways: Connection Types, On-Premises Data Gateway, VNet Gateway, Managing Connections, and Accessing Data Behind Firewalls

Every pipeline, every Dataflow Gen2, every mirrored database starts with one thing — a CONNECTION. Without the right connection, Fabric cannot authenticate with your data source. And for data sources behind corporate firewalls (on-premises SQL Server, Oracle, file shares), you need a GATEWAY to bridge the gap.

Think of a connection like a passport — it proves your identity to the destination country (data source). A gateway is like an embassy — it sits between two countries (corporate network and Azure) and facilitates travel (data flow) without opening borders (firewall ports).

Table of Contents

• What Is a Connection in Fabric?
• Connection vs Linked Service (ADF Migration)
• Connection Types and Authentication
• Azure Sources
• On-Premises Sources
• Cross-Cloud Sources (AWS, GCP)
• SaaS Sources
• Creating a Connection
• Create Inline (During Pipeline/Dataflow Setup)
• Create in Workspace Settings
• Sharing and Reusing Connections
• On-Premises Data Gateway
• What It Is and Why You Need It
• Installing the Gateway
• Configuring the Gateway
• Gateway Architecture (No Inbound Ports)
• Gateway Clusters (High Availability)
• VNet Data Gateway
• What It Is
• When to Use VNet vs On-Premises Gateway
• Setting Up a VNet Gateway
• Connection Security Best Practices
• Service Principal vs Shared Credentials
• Key Vault Integration
• Rotating Credentials
• Troubleshooting Connection Issues
• Common Connection Errors and Fixes
• Testing Connectivity
• Real-World Scenarios
• Scenario 1: On-Premises SQL Server to Fabric Lakehouse
• Scenario 2: Private Azure SQL (VNet) to Fabric
• Scenario 3: Multi-Cloud (AWS S3 + Azure SQL + Snowflake)
• Common Mistakes
• Interview Questions
• Wrapping Up

What Is a Connection in Fabric?

A Connection defines HOW Fabric authenticates with a data source — the endpoint (where), the credentials (who), and the authentication method (how).

Connection = Endpoint + Authentication + (Optional) Gateway

Example:
  Name: "Production SQL Server"
  Endpoint: sql-prod.company.com,1433
  Database: SalesDB
  Auth: SQL Authentication (username + password)
  Gateway: On-Premises Gateway "Corp-Gateway-01"

Connection vs Linked Service (ADF Migration)

ADF (old)	Fabric (new)
Linked Service	Connection
Integration Runtime (Self-Hosted)	On-Premises Data Gateway
Integration Runtime (Azure)	Not needed (Fabric manages compute)
Per Data Factory	Per Workspace (shareable)
ARM template deployment	Workspace settings (UI or API)

If migrating from ADF: your Linked Services become Connections, and your Self-Hosted Integration Runtime becomes an On-Premises Data Gateway.

Connection Types and Authentication

Azure Sources

Source	Authentication Options
Azure SQL Database	Organizational account (Azure AD), Service Principal, SQL auth
Azure SQL Managed Instance	Organizational account, Service Principal, SQL auth
ADLS Gen2	Organizational account, Service Principal, Account Key, SAS token
Azure Cosmos DB	Account Key, Organizational account
Azure Synapse	Organizational account, Service Principal
Azure Blob Storage	Account Key, SAS token, Organizational account

On-Premises Sources (Require Gateway)

Source	Authentication	Gateway Required
SQL Server (on-prem)	Windows auth, SQL auth	✅ On-premises gateway
Oracle	Basic auth	✅ On-premises gateway
File shares (SMB)	Windows auth	✅ On-premises gateway
MySQL (on-prem)	Basic auth	✅ On-premises gateway
PostgreSQL (on-prem)	Basic auth	✅ On-premises gateway
SAP	Basic auth	✅ On-premises gateway

Cross-Cloud Sources

Source	Authentication
Amazon S3	Access Key ID + Secret Access Key
Amazon Redshift	Username + Password
Google Cloud Storage	Service Account Key (JSON)
Google BigQuery	Service Account Key
Snowflake	Username + Password, Key Pair

SaaS Sources

Source	Authentication
Salesforce	OAuth 2.0
Dynamics 365	Organizational account
SharePoint Online	Organizational account
Dataverse	Organizational account

Creating a Connection

Create Inline (During Pipeline/Dataflow Setup)

When configuring a Copy Activity or Dataflow Gen2 source:

1. Select source type (e.g., Azure SQL Database)
2. Click New connection
3. Enter server, database, credentials
4. Click Test connection → verify ✅
5. Click Create → connection saved to workspace

Create in Workspace Settings

1. Open Workspace settings → Connections and gateways
2. Click + New connection
3. Select source type
4. Configure endpoint and authentication
5. Test and save

The connection is now available for ALL items in the workspace (pipelines, dataflows, notebooks, mirrored databases).

Sharing and Reusing Connections

Workspace: DataEng_Prod
  Connection: "Prod Azure SQL" (created once)
    Used by:
      ├── PL_Ingest_Customers (pipeline Copy Activity)
      ├── DF_Clean_Orders (Dataflow Gen2 source)
      ├── Mirrored_AdventureWorks (mirrored database)
      └── NB_Read_SQL (notebook JDBC query)

  One connection, four consumers — no duplicate credentials.

On-Premises Data Gateway

What It Is and Why You Need It

The On-Premises Data Gateway is a software agent installed INSIDE your corporate network. It creates a secure outbound HTTPS connection to Azure, enabling Fabric to access data sources behind firewalls without opening inbound ports.

Corporate Network                          Azure / Fabric
┌────────────────────┐                    ┌──────────────────┐
│ SQL Server         │                    │ Fabric Workspace  │
│ Oracle DB          │                    │                   │
│ File Shares        │                    │ Pipeline          │
│                    │                    │ Dataflow Gen2     │
│  ┌──────────────┐  │                    │ Mirrored DB       │
│  │   Gateway     │──── HTTPS (443) ────►│                   │
│  │   Agent       │  │   outbound only   │                   │
│  └──────────────┘  │                    │                   │
└────────────────────┘                    └──────────────────┘
  No inbound ports opened!
  Gateway initiates ALL connections outbound.

Installing the Gateway

1. Download from Microsoft Download Center → “On-premises data gateway (standard mode)”
2. Install on a Windows Server inside the corporate network
3. Requirements: Windows Server 2019+, .NET Framework 4.8+, always-on machine
4. NOT on a laptop — needs to be always running
5. Sign in with your organizational account (Azure AD)
6. Name the gateway: Corp-Gateway-Prod-01
7. Set the recovery key (save this — needed to restore or migrate the gateway)
8. Gateway registers with your Azure AD tenant

Configuring the Gateway

After installation:

1. Open On-premises data gateway app on the server
2. Service Settings: Verify it shows “The gateway is online”
3. Connectors: Install additional connectors if needed (Oracle, SAP)
4. Network: Configure proxy settings if the server uses a proxy for internet access

In Fabric: 1. Settings → Manage connections and gateways 2. Your gateway appears under On-premises data gateways 3. Create connections that use this gateway

Gateway Clusters (High Availability)

Primary Gateway: Corp-Gateway-Prod-01 (Server A)
Secondary Gateway: Corp-Gateway-Prod-02 (Server B)
  → Same cluster name, same recovery key
  → If Server A goes down, Server B takes over automatically
  → No data flow interruption

To add a gateway to a cluster: 1. Install gateway on a second server 2. During setup, select Add to an existing gateway cluster 3. Enter the same cluster name and recovery key

VNet Data Gateway

What It Is

A VNet Data Gateway runs INSIDE your Azure Virtual Network — accessing private Azure resources (private endpoints) without installing software on a VM.

Azure VNet                                    Fabric
┌──────────────────────┐                    ┌──────────────┐
│ Private Azure SQL     │                    │ Fabric       │
│ (no public endpoint)  │                    │ Workspace    │
│                       │                    │              │
│  ┌─────────────────┐  │                    │              │
│  │ VNet Gateway     │──── managed by MS ──►│              │
│  │ (no VM needed)   │  │                    │              │
│  └─────────────────┘  │                    │              │
└──────────────────────┘                    └──────────────┘
  Microsoft manages the infrastructure.
  You just associate your VNet.

When to Use VNet vs On-Premises Gateway

Scenario	Use This
On-premises SQL Server behind corporate firewall	On-Premises Gateway
Azure SQL with private endpoint (no public access)	VNet Gateway
On-premises Oracle database	On-Premises Gateway
Azure Cosmos DB with VNet restriction	VNet Gateway
On-premises file shares	On-Premises Gateway
Azure resources with no VNet restrictions	No gateway needed (direct connection)

Setting Up a VNet Gateway

1. Go to Azure Portal → Virtual Network Gateways for Power Platform (or Fabric admin portal)
2. Click + New
3. Select your Azure subscription and VNet
4. Select the subnet (must be a dedicated subnet)
5. Click Create → gateway provisions in 5-10 minutes
6. In Fabric, create a connection and select the VNet gateway

Connection Security Best Practices

Service Principal vs Shared Credentials

❌ AVOID: Shared username/password
  Connection: SQL auth → "admin" / "P@ssword123"
  Problem: shared credentials, no audit trail, password rotation breaks everything

✅ RECOMMENDED: Service Principal (Azure AD)
  Connection: Service Principal → App ID + Client Secret (or Certificate)
  Benefits: individual identity, audit trail, rotate secret without downtime,
            grant specific permissions via RBAC

Key Vault Integration

Store credentials in Azure Key Vault:
  Key Vault: "fabric-prod-kv"
    Secret: "sql-connection-string" → "Server=prod-sql;User=...;Password=..."
    Secret: "s3-access-key" → "AKIAIOSFODNN7EXAMPLE"

Fabric reads secrets from Key Vault at runtime — credentials never stored in Fabric.

Troubleshooting Connection Issues

Error	Cause	Fix
Connection test failed	Wrong server name, credentials, or firewall blocking	Verify endpoint, check credentials, check firewall rules
Gateway offline	Gateway service stopped on the server	Remote into server, restart the gateway service
Gateway not found	Gateway registered with different Azure AD account	Re-register or sign in with correct account
Timeout connecting	Network latency or source database overloaded	Check network, increase timeout
SSL/TLS error	Certificate mismatch or expired	Update certificates on the gateway server
Access denied	Credentials valid but user lacks permissions on source	GRANT permissions in the source database

Real-World Scenarios

Scenario 1: On-Premises SQL Server to Fabric

Setup:
  1. Install On-Premises Gateway on Windows Server in data center
  2. Create Connection: SQL Server → server name, SQL auth, gateway = Corp-Gateway
  3. Create Pipeline: Copy Activity → source = on-prem SQL → destination = Lakehouse
  4. Schedule: Daily at 6 AM

Data flows: SQL Server → Gateway (outbound HTTPS) → Fabric Pipeline → Lakehouse

Scenario 2: Private Azure SQL to Fabric

Setup:
  1. Azure SQL has private endpoint (no public access)
  2. Create VNet Gateway in the same VNet as Azure SQL
  3. Create Connection: Azure SQL → private endpoint URL, Azure AD auth, VNet gateway
  4. Create Mirrored Database pointing to this connection

Data flows: Azure SQL (private) → VNet Gateway → Fabric (mirrored Delta tables)

Scenario 3: Multi-Cloud

Connections:
  "AWS S3 Events" → Access Key + Secret Key (no gateway)
  "Azure SQL CRM" → Service Principal (no gateway)
  "Snowflake Analytics" → Username + Password (no gateway)

All three connections in one workspace.
Pipeline ingests from all three into a single Lakehouse.

Common Mistakes

1. Not using a gateway for on-premises sources — Fabric cannot reach on-prem databases directly. You MUST install the on-premises data gateway.

2. Installing the gateway on a laptop — the gateway must be always-on. Install on a dedicated Windows Server, not a laptop that sleeps.

3. Single gateway without clustering — if the gateway server goes down, all data flows stop. Set up a two-node cluster for high availability.

4. Using shared SQL auth credentials — no audit trail, password rotation is painful. Use Service Principals for Azure sources.

5. Not testing connections before building pipelines — always click “Test connection” first. Finding a connection error after building a 10-activity pipeline wastes hours.

Interview Questions

Q: What is the difference between an On-Premises Gateway and a VNet Gateway? A: On-Premises Gateway is software installed on a Windows Server inside a corporate network for accessing on-prem data sources (SQL Server, Oracle, file shares). VNet Gateway is a Microsoft-managed service running inside an Azure Virtual Network for accessing private Azure resources (private endpoint Azure SQL, Cosmos DB). On-prem gateway for on-prem data, VNet gateway for private Azure data.

Q: How does the On-Premises Gateway work without opening inbound firewall ports? A: The gateway initiates ALL connections outbound over HTTPS (port 443) to Azure Service Bus. Fabric sends commands to the Service Bus, the gateway picks them up, queries the on-prem source, and sends results back through the same outbound connection. No inbound ports are ever opened in the corporate firewall.

Wrapping Up

Connections and gateways are the plumbing of your data platform — invisible when working, catastrophic when broken. Use direct connections for cloud sources, on-premises gateways for corporate data centers, and VNet gateways for private Azure resources. Always use Service Principals over shared credentials, cluster gateways for high availability, and test every connection before building pipelines.

Related posts: – Fabric Data Factory & Pipelines – Mirrored Databases – Azure Connections & Authentication – Dataflow Gen2: Introduction

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.