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

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.