Can Microsoft Fabric connect to our on-prem SQL Server without opening firewall ports to the internet?
Yes. An on-premises data gateway installed on a Windows host inside the network makes an outbound, encrypted connection to the Microsoft cloud, so no inbound firewall ports are opened and the SQL Server is never published to the internet. Fabric Data Factory pipelines then read the database through that gateway using a read-only service account registered in the gateway's own credential store.
- 01The gateway dials out; nothing dials in. A gateway installed on a Windows host inside the network holds an outbound HTTPS connection to the Microsoft cloud and polls for work, so the firewall needs no inbound rule, the SQL Server needs no public IP, and the instance does not move to a DMZ. Confirm the current outbound endpoint and port list against Microsoft's gateway documentation before the change request goes in — that list is Microsoft's to maintain and it does change.
- 02Three things to provision, in this order: a Windows host somebody owns (a small always-on VM, not a developer laptop), outbound network access from that host, and a dedicated SQL login holding db_datareader on the databases in scope and nothing more. Register the gateway in the same region as the Fabric capacity — a region mismatch is far easier to fix before installation than after.
- 03High availability is a gateway cluster, not a second standalone install. A second host joined to the same cluster with the same recovery key takes over when a member is offline. With a single member, a patch reboot is a failed refresh: the Direct Lake semantic model keeps serving the last successfully loaded data, so dashboards go stale rather than blank — which is worse, because nobody notices.
- 04Incremental refresh keys off a watermark column — a rowversion, an identity key, or a LastModifiedDate the application actually maintains — so each run moves changed rows instead of the whole table. Hard deletes are invisible to a watermark, so any table whose rows are physically removed needs a tombstone table or a scheduled full reconciliation.
- 05Point the pipeline at a readable secondary, a log-shipped copy or a nightly snapshot wherever one exists, rather than the production primary. Analytical extracts scan ranges that transactional workloads never touch, and reading a replica settles the argument with the DBA before it starts. Typical sequencing: gateway installed and tested in week one, one or two tables landing in OneLake in week two, the remainder of the first module across the four-to-six-week build.
- 06Honest limit: a gateway is a server your team now operates. If nobody will patch, monitor and own that host, refreshes fail silently and the dashboards quietly go wrong. If the requirement is sub-minute freshness over very large tables, one gateway host becomes the throughput bottleneck — replicating the database into Azure and reading it natively is the better design, and it costs more. Mirroring and native replication cover a source list that changes release to release, so check current support before designing around either.
Where to go deeper
Related questions, answered
What does the on-premises data gateway actually need from our network team?
A Windows host inside the network — a small VM is enough — plus outbound access from that host to Microsoft's gateway endpoints and line-of-sight to the SQL Server on its own listener port. No inbound rule, no public IP, no DMZ placement. The exact endpoints and ports depend on the gateway's communication mode, so take the current list from Microsoft's gateway documentation into the firewall change request rather than assuming 443 alone.
Where are the SQL credentials stored, and can Microsoft read them?
Credentials entered for a gateway data source are encrypted on the gateway host, and Microsoft documents the decryption key as staying on that host rather than in the cloud service. Treat that as Microsoft's design and not a guarantee you control: use a dedicated SQL login with db_datareader on the specific databases and nothing else, and rotate it like any other service account.
What happens to dashboards if the gateway host goes down?
Refreshes fail and the semantic model keeps serving the last successfully loaded data, so dashboards go stale rather than blank. A second gateway on a separate host in the same cluster gives failover, and the recovery key must match across members. Patch windows on a single-member gateway are a common cause of a missed refresh.
Should pipelines read production SQL Server or a replica?
Read a secondary where one exists. Analytical extracts scan wide ranges and compete with transactional work, so a readable secondary, a log-shipped copy or a nightly snapshot keeps the load off the primary. Where only the primary is available, restrict reads to a watermark window, schedule them outside the business day, and give the pipeline login its own resource governance.
What does incremental refresh key off on a SQL Server source?
A monotonic watermark column — a rowversion, an identity key, or a reliable LastModifiedDate — so each run moves only rows above the last recorded value. Tables without one need change tracking enabled, a trigger-maintained audit column, or a full reload. Hard deletes are the usual trap: a watermark never sees them, so deletes need a tombstone table or a periodic reconciliation pass.
When is a gateway the wrong answer for on-prem SQL Server?
When nobody will own the host. A gateway is a Windows machine needing patching, monitoring and a named owner, and an unowned one breaks refreshes quietly. Sub-minute freshness over very large tables also outgrows a single host: at that point replicate the database into Azure and read it natively. A policy forbidding any outbound cloud connection rules the pattern out entirely.
Sources
- Install an on-premises data gateway · checked 2026-10-06
- Direct Lake mode for Power BI semantic models · checked 2026-10-06
- Microsoft Fabric region availability · checked 2026-10-06
Figures on this page: an on-premises data gateway · data stays in your tenant · 4–6 weeks
See the mechanism, not the marketing
The architecture, the pipelines, and the governed semantic model that make this work on your own tenant.
See how it worksPeople also ask
- Does Microsoft Fabric need an on-premises data gateway to read a SQL Server database?
- Which outbound ports and endpoints does the on-premises data gateway require?
- How do you cluster two data gateways for high availability?
- Who can install and configure a Fabric data gateway inside our network?
- Is a read replica better than reading production SQL Server for analytics?