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 |
SaaS Sources
| Source | Authentication |
|---|---|
| Salesforce | OAuth 2.0 |
| Dynamics 365 | Organizational account |
| SharePoint Online | Organizational account |
| Dataverse | Organizational account |
On-Premises Sources
On-premises data sources sit behind corporate firewalls and require an On-Premises Data 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 (AWS, GCP)
| 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 |
Cross-cloud sources do not require a gateway — Fabric connects directly over the internet using the provided credentials.
Creating a Connection
Create Inline (During Pipeline/Dataflow Setup)
When configuring a Copy Activity or Dataflow Gen2 source:
- Select source type (e.g., Azure SQL Database)
- Click New connection
- Enter server, database, credentials
- Click Test connection → verify ✅
- Click Create → connection saved to workspace
Create in Workspace Settings
- Open Workspace settings → Connections and gateways
- Click + New connection
- Select source type
- Configure endpoint and authentication
- 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
- Download from Microsoft Download Center → “On-premises data gateway (standard mode)”
- Install on a Windows Server inside the corporate network
- Requirements: Windows Server 2019+, .NET Framework 4.8+, always-on machine
- NOT on a laptop — needs to be always running
- Sign in with your organizational account (Azure AD)
- Name the gateway:
Corp-Gateway-Prod-01 - Set the recovery key (save this — needed to restore or migrate the gateway)
- Gateway registers with your Azure AD tenant
Configuring the Gateway
After installation:
- Open On-premises data gateway app on the server
- Service Settings: Verify it shows “The gateway is online”
- Connectors: Install additional connectors if needed (Oracle, SAP)
- 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 Architecture (No Inbound Ports)
The gateway creates an outbound HTTPS connection (port 443) to Azure Service Bus. It never requires inbound ports to be opened in your corporate firewall. Fabric sends commands to the Service Bus relay, the gateway picks them up, queries the on-premises source, and sends results back through the same outbound channel. This is the same proven architecture used by the Power BI gateway and the ADF Self-Hosted Integration Runtime.
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 interruptionTo 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
- Go to Azure Portal → Virtual Network Gateways for Power Platform (or Fabric admin portal)
- Click + New
- Select your Azure subscription and VNet
- Select the subnet (must be a dedicated subnet)
- Click Create → gateway provisions in 5-10 minutes
- 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 RBACKey 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.Rotating Credentials
Credential rotation is critical for security. For Service Principals, create a second client secret before the first expires, update the connection, then delete the old secret — zero downtime. For SQL authentication, update the password in the source database, then immediately update the Fabric connection. For Key Vault integration, rotation is automatic — update the secret in Key Vault and all connections using it pick up the new value at next use. Schedule credential rotation every 90 days as a security best practice.
Troubleshooting Connection Issues
Common Connection Errors and Fixes
| 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 |
Testing Connectivity
Always test before building pipelines. Every connection has a “Test connection” button — click it during creation and after any credential rotation. For gateway connections, also verify from the gateway server: open a command prompt and run telnet servername 1433 (for SQL) to confirm the gateway machine can reach the source. A connection error discovered after building a 10-activity pipeline wastes hours.
Real-World Scenarios
Scenario 1: On-Premises SQL Server to Fabric Lakehouse
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 → LakehouseScenario 2: Private Azure SQL (VNet) 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 (AWS S3 + Azure SQL + Snowflake)
Connections:
"AWS S3 Events" → Access Key + Secret Key (no gateway needed)
"Azure SQL CRM" → Service Principal (no gateway needed)
"Snowflake Analytics" → Username + Password (no gateway needed)
All three connections in one workspace.
One pipeline ingests from all three into a single Lakehouse.
No gateways needed — all sources are cloud-accessible.Common Mistakes
-
Not using a gateway for on-premises sources — Fabric cannot reach on-prem databases directly. You MUST install the on-premises data gateway.
-
Installing the gateway on a laptop — the gateway must be always-on. Install on a dedicated Windows Server, not a laptop that sleeps.
-
Single gateway without clustering — if the gateway server goes down, all data flows stop. Set up a two-node cluster for high availability.
-
Using shared SQL auth credentials — no audit trail, password rotation is painful. Use Service Principals for Azure sources.
-
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.