Share via

How can I extract data from an on-premises SQL Server using Microsoft Fabric Data pipelines?

LearnAndLearn 120 Reputation points
2025-08-13T12:56:36.83+00:00

In ADF I’d install a SHIR, create a linked service to the on-prem SQL Server, and run a copy activity.

What’s the equivalent in Microsoft Fabric data pipelines?

  • Do I still use a self-hosted IR (or the on-premises data gateway) for connectivity?
  • What are the step-by-step configuration steps to copy tables from on-prem SQL Server into a Fabric destination ?
  • Any prerequisites (firewall/ports), supported authentication methods (SQL Auth vs Entra ID), or limitations to be aware of?
Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

0 comments

Answer accepted by question author

  1. Amira Bedhiafi 41,386 Reputation points MVP Volunteer Moderator
    2025-08-13T13:11:42.9566667+00:00

    Hello !

    Thank you for posting on Microsoft Learn.

    You need to install the on-prem data gateway (Enterprise) on a server that can reach your SQL Server. Sign in with your Fabric/Power BI tenant account and make sure that the outbound TCP 443 is open to Microsoft endpoints.

    In Fabric, follow this path : Manage → Connections → New → SQL Server → select your Gateway → enter server & DB → choose auth (SQL Auth or Windows Entra ID via Kerberos SSO if configured) → Test.

    In your data pipeline, in your copy data activity:

    • source: your SQL Server connection (table or custom query).
    • sink: Lakehouse (files or tables/delta) or warehouse. You can enable “auto-create table” if loading to Lakehouse tables.

    You can enable partitioned copy on an integer/date key, set batch size, and map types and schedule the pipeline and monitor runs in pipeline monitoring.

    The gateway is the Fabric equivalent of ADF SHIR for on-prem. Keep the gateway host always on throughput/concurrency is limited by that host.

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.