Forum Discussion

Steefff's avatar
Steefff
Frequent Visitor
6 months ago
Solved

Copy JOb to write data from Fabric Warehouse to SQL Server On Premise

Dear all

i'm trying to create a Copy Job to read data from Fabric Warehouse and write to a SQL Server on premise. Th copy JOB fails with this error message: "Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'XXXX.fabric.microsoft.com', Database: 'XXX', User: ''."

it seams that the copy job is not able to reach the warehouse.

If i try to read from Warehouse and write to lakehouse all works fine.

Where is the problem? in the outbound to a SQL Server on premise?

Any suggestion to obtain the same results ?

Thanks in adnvace

Stefania

data warehouse
  • Steefff's avatar
    Steefff
    6 months ago

    We have resolved!

    The problem is the comunication in outboud between gateway and fabric on port 1433 (443 for file is open and this is way the write from lakehouse to sql on prem works).

    Since the reading of data from Fabric happens on the gateway, and not in Fabric itself as I thought, I was having a connectivity problem because the gateway could not reach Fabric. The system administrators fixed the network rules and now everything works.

    Thanks to everyone

9 Replies

  • deborshi_nag's avatar
    deborshi_nag
    Icon for Super User rankSuper User
    6 months ago

    Hello Steefff 

     

    You have already demonstrated that the Warehouse to Lakehouse integration functions correctly.

     

    This confirms the following:

    • Fabric is able to access the Warehouse.
    • Authentication and connectivity for the Warehouse are not causing any issues.

    It is important to note that the Copy Job validates both the source and sink simultaneously. When the sink connection fails, the job frequently returns a generic SQL connectivity error that still references the source server details, which is a recognised source of confusion.

     

    For SQL Server on-premises, the following are required:

    • Self-Hosted Integration Runtime (On-prem Data Gateway)
    • SQL authentication credentials accessible to the gateway
    • Outbound connectivity from the gateway to SQL Server

    Please review the following in Fabric:

    • Manage connections and gateways
    • Ensure the gateway is Online
    • Confirm it is explicitly selected for the SQL Server connection

    Only SQL Authentication (username and password) is supported. Entra ID / Azure AD authentication and Windows Integrated authentication (Kerberos) are not compatible in this scenario.

     

  • Steefff's avatar
    Steefff
    Frequent Visitor
    6 months ago

    We have resolved!

    The problem is the comunication in outboud between gateway and fabric on port 1433 (443 for file is open and this is way the write from lakehouse to sql on prem works).

    Since the reading of data from Fabric happens on the gateway, and not in Fabric itself as I thought, I was having a connectivity problem because the gateway could not reach Fabric. The system administrators fixed the network rules and now everything works.

    Thanks to everyone

  • YassineHachguer's avatar
    YassineHachguer
    Regular Visitor
    6 months ago

    It might be related to connectivity between Fabric and your on-premise SQL Server. Check if a Data Gateway is configured and if the firewall allows outbound access from Fabric to the on-prem server. Usually Copy Jobs to on-prem sources require a gateway.

    • Steefff's avatar
      Steefff
      Frequent Visitor
      6 months ago

      I have a gateway, and i try to write from lakehouse to sql on prem (via gateway) and it works fine.

      • Steefff's avatar
        Steefff
        Frequent Visitor
        6 months ago

        but read from warehouse and write to sql on prem still in error .. 

  • Steefff's avatar
    Steefff
    Frequent Visitor
    6 months ago

    Hi deborshi_nag 

    thanks for you response.

    i try some other scenario.

     

    Read from the warehouse and write to SQL Server 2017 on Premise --> FAILS

    Read from the lakehouse and write to SQL Server 2017 on Premise --> WORKS

    Read from the warehouse and write to lakehouse --> WORKS

     

    i have try with simple table with one single column, int type. but it still fails.

     

    it seems there a problem when the data from the warehouse go out of fabric, to the SQL server ON premise.

    It could be a problem of the SQl Server on prem version? it' a SQL server 2017 Developer edition

     

    Thanks

    Stefania

     

    • deborshi_nag's avatar
      deborshi_nag
      Icon for Super User rankSuper User
      6 months ago

      hey Steefff on your Data Gateway can you check if connectivity to your on-prem SQL database works?

       

      Run this on a PowerShell prompt:

      Test-NetConnection your-sql-server-name -Port 1433

       

      If you get "TcpTestSucceeded : True" it confirms there's no problem with connectivity. 

       

    • ssrithar's avatar
      ssrithar
      Icon for Super User rankSuper User
      6 months ago

      Hi Steefff ,

       

      The key finding from your testing is:

      • Warehouse → Lakehouse Works
      • Lakehouse → SQL Server on-prem Works
      • Warehouse → SQL Server on-prem Fails

       

      This suggests the issue is specifically how the Copy Job routes data from Warehouse through the gateway to on-prem. A few things to check:

      1. Explicitly assign the gateway to the SQL Server connection--In Fabric go to Manage connections and gateways and confirm the on-prem SQL Server connection has your gateway explicitly selected.

       

      2. Use SQL Authentication only

      Make sure you're connecting with a SQL username and password. Entra ID, Azure AD, and Windows/Kerberos authentication are not supported in this scenario.

       

      3. Verify gateway connectivity to SQL Server Run this on the machine where your gateway is installed --Test-NetConnection your-sql-server-name -Port 1433

       

      Please try the above options and provide the output so we can nail down the issue

       

      If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

      • V-yubandi-msft's avatar
        V-yubandi-msft
        Icon for Community Support rankCommunity Support
        6 months ago

        Hi Steefff ,

        Based on the scenarios you tested

        Warehouse → Lakehouse is successful
Lakehouse → SQL Server (on-prem via gateway) is successful
Warehouse → SQL Server (on-prem) is unsuccessful

        Since the Lakehouse to SQL Server connection works through the gateway, your SQL Server connectivity appears to be working. The issue may be occurring when the Copy Job attempts to transfer data directly from the Warehouse to the on-prem SQL Server.

         

        SQL Server 2017 should not be the cause, as it is generally supported via the gateway.

        As a workaround, try loading the data from Warehouse to Lakehouse first, then from Lakehouse to SQL Server through the gateway. Checking the gateway logs may also provide more error details.

         

        Thank you.