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 c...
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

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.