Forum Discussion

marcos-1992's avatar
marcos-1992
Regular Visitor
1 year ago
Solved

400 Error When Using On-Premises Data Gateway with Fabric Pipelines (SQL Server Source)

Hello everyone,

We are encountering a persistent issue when trying to connect a Fabric Data Factory pipeline to an on-premises SQL Server database using the On-Premises Data Gateway. The same connection works correctly from Power BI and Dataflow Gen2 (in the same Fabric Workspace), but not within the pipeline’s Copy Data activity.

Scenario Details:

  • Environment:

    • Microsoft Fabric (Data Factory Pipelines) - SKU: F8, Region: North Central US

    • SQL Server hosted on Windows Server 2019

    • On-Premises Data Gateway installed on the server (version 3000.270.10 - May 2025, upgraded and restarted)

    • Connection is configured and appears online under Manage Connections and Gateways in the Fabric workspace

  • Issue:
    When configuring a new Copy Data activity in a pipeline:

    1. We select the existing connection (the one that works with Power BI and Dataflows)

    2. When trying to browse the source tables (or validate the connection), we receive a 400 error

       

Troubleshooting Steps Taken So Far:

  1. Firewall Temporarily Disabled on the on-prem server (no change)

  2. Gateway Version Updated to the latest version, and the server was rebooted

  3. Tested Multiple Auth Types:

    • SQL Authentication

    • Active Directory Password Authentication

  4. Tested Connections to Multiple Databases on the same server

  5. Changed Privacy Level Settings (from Organizational to None)

  6. Verified Connection Works in:

    • Power BI Desktop (via Gateway)

    • Dataflow Gen 2

    • Connection test in the “Manage Gateways” area in Fabric (all green)

    • Connectivity with  *.frontend.clouddatahub.net from the Server where the gateway is installed

Observations:

  • The issue only occurs within Fabric Pipelines when configuring the source for the Copy Data activity.

  • It seems pipeline-specific, suggesting this could be a limitation, bug, or configuration gap within the pipeline service itself.

  • I've reviewed similar issues posted in the forums and applied suggested workarounds (e.g., firewall, gateway version, permissions), but the problem persists.

Questions:

  • Is there any known limitation or extra configuration step required for pipelines to use gateway connections that differ from Power BI/Dataflows?

  • Are there specific ports or services beyond the usual for Gateway-to-SQL that Fabric Pipelines use?

Any help, suggestions, or pointers would be highly appreciated. 

Thanks in advance!

data engineering
Data Pipeline
  • marcos-1992's avatar
    marcos-1992
    1 year ago

    Hi everyone,

    We were finally able to identify the root cause of the issue and resolve it, with the support of the Microsoft team, after submitting a support ticket.

    After several troubleshooting sessions and reviewing backend logs from the On-Premises Data Gateway, the Microsoft team discovered that, despite updating the Gateway to the latest version, the Fabric pipeline capability was not properly activated on the gateway instance.

     

    To resolve this, we had to uninstall and then reinstall the On-Premises Data Gateway.
    We used the recovery key available to restore and remap the existing connections during the reinstallation process.

    Once we reinstalled the gateway and confirmed the Fabric pipeline capability was correctly registered, the connection through the Fabric pipeline started working as expected.

     

    I hope this helps anyone who might face a similar issue.

     

    Thanks to everyone who supported this thread!

    Marcos

11 Replies

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    1 year ago

    Hi marcos-1992 ,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    I have included the learning documents for your reference; they may help you

     

    Configure SQL server in a copy activity - Microsoft Fabric | Microsoft Learn

    Solved: Re: Status 400 Error when setting up an SQL Gatewa... - Microsoft Fabric Community

    How to access on-premises data sources in Data Factory - Microsoft Fabric | Microsoft Learn

    The previously known issue has been fixed.

    Known issue - Multiple installations of on-premises data gateway causes pipelines to fail - Microsoft Fabric | Microsoft Learn

     

    If you're still facing the same issue,
    I suggest raising a support ticket so the support team can assist you in resolving the problem.
    Please refer to the link below for instructions on how to raise a support ticket:

    How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

     

    If this post helped resolve your issue, please consider marking it as the Accepted Solution.
    This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

    We appreciate your engagement and thank you for being an active part of the community.

    Best regards,
    LakshmiNarayana

  • marcos-1992's avatar
    marcos-1992
    Regular Visitor
    1 year ago

    Hi v-lgarikapat,

    Thank you for your response and for sharing the documentation.

    We’ve followed all the recommended steps and are using the same login type that works successfully with both Dataflow Gen2 and Power BI. Unfortunately, the issue still persists within the Fabric Pipeline.

    We also confirmed that the On-Premises Data Gateway instance running on the server is the only one configured and active.

    We’ll continue testing with different configurations, and if we’re unable to resolve it, we’ll proceed with opening a support ticket.

    Thanks again for your assistance!

     

    Best regards,
    Marcos

    • v-lgarikapat's avatar
      v-lgarikapat
      Community Support
      1 year ago

      Hi marcos-1992 ,

       

      Thanks for the update and for reviewing the setup on your end.

      We appreciate your efforts in testing different configurations. If the issue persists, opening a support ticket would be a good next step.

      Also, please continue to share any updates here, as it will help other community members find and follow the solution more easily.

      Feel free to reach out if you have any other questions.

       

      Best regards,
      Lakshmi Narayana

      • v-lgarikapat's avatar
        v-lgarikapat
        Community Support
        1 year ago

        Hi marcos-1992 ,

         

        I wanted to follow up and confirm whether you’ve had a chance to review the information we provided.
        If you have any questions or need further clarification, please don’t hesitate to reach out.

        Additionally, have you had a chance to raise a support ticket regarding this issue?

        If you’re still encountering any challenges, we’d be happy to assist you further.

         

         

        Best Regards,

        Lakshmi Narayana

  • marcos-1992's avatar
    marcos-1992
    Regular Visitor
    1 year ago

    Hi everyone,

    We were finally able to identify the root cause of the issue and resolve it, with the support of the Microsoft team, after submitting a support ticket.

    After several troubleshooting sessions and reviewing backend logs from the On-Premises Data Gateway, the Microsoft team discovered that, despite updating the Gateway to the latest version, the Fabric pipeline capability was not properly activated on the gateway instance.

     

    To resolve this, we had to uninstall and then reinstall the On-Premises Data Gateway.
    We used the recovery key available to restore and remap the existing connections during the reinstallation process.

    Once we reinstalled the gateway and confirmed the Fabric pipeline capability was correctly registered, the connection through the Fabric pipeline started working as expected.

     

    I hope this helps anyone who might face a similar issue.

     

    Thanks to everyone who supported this thread!

    Marcos

  • Soova_Kamall's avatar
    Soova_Kamall
    Frequent Visitor
    1 year ago

    Good Day Fabric Community,
    I have been through your problem, that you have faced issue with On premises
    Data Gateway to Fabric in "Copy Activity" inside a Pipeline , and i have seen that as a solution that
    every time you have to uninstall and again install data gateway to connect , but i
    have had face same almost issue while connecting On premises to Fabric.

     

    From MY SQL to Fabric journey i have experienced below:
    I would like to say that do not unistall and Install Data gateway everytime , when you get
    new data connection, instead you do what i have posted in below Screen shot ,

    what i did was My SQL server with different Database (ex:Databse 1 and Database 2)
    (both Database located in same My SQL on premises server ) i transfer to different Fabric Workspace.
    with same data gateway i have connected and do Copy Activity (Traditional)
    Copy Activity ( inside a pipeline) without uninstall Gateway.

     

    Gateway Link : https://www.microsoft.com/en-us/power-platform/products/power-bi/gateway

     

    Sol : Plese do clean your browser catche memory first .
    Sol: First try to test connection with Normal Copy Activity (traditional) not with inside pipeline.
    Sol : hit and with refresh and Test connection
    Sol: Wrost Sitution Change Pileline Name for Testing connection .
    Sol: When you can establish a connection with Normal Copy Activity (traditional) then Duplicate the Tab and take a
    pileline and do inside Copy Activity . and remove 1st tab source location and the connect 2nd Tab
    Source and Test connection . ( So my intension , i dont want to unistall my Gateway) 
    I have store my data into Lakehouse in File (format .csv)
    Sol : So what you have to do is : Activate the data gateway every time just before the connection and
    do paste your email credential every time after open on prem data Gateway. just like my below
    document . (Do refresh and test connection 5 to 10 times and then do test connection)

     

    Please check my Screenshot , hope this can solve your issue.

     

    Same Gateway use for this Activity,Workspace Different. (pic attached)
    Same Gateway was use in Different Activity , Different Workspacce, (pic attached)

     

    If you like my post , please like and send kudos. 
    Below Pic attached.

     

    SQL AuthenticationGateway ConnectNormal Copy(traditional)Duplicare TabFill DestinationRun PipelineSame Gateway ,WS:TestcommunityWorkspaceThank You ,
    Soova Kamall