Local SQL Server unable to retrieve data from Microsoft Fabric Lakehouse through SSMS

[janudaS]

Hi everyone,

I am able to successfully connect to a Microsoft Fabric Lakehouse from SSMS.

I also have a local SQL Server instance running on localhost.

However, the issue is that my local SQL Server connection is unable to retrieve/load data from the Fabric Lakehouse connection.

Scenario:

• Fabric Lakehouse connection works in SSMS
• Localhost SQL Server connection also works separately
• But I cannot pull Fabric data into local SQL Server tables

I would like to understand:

1. Is direct data transfer between Fabric Lakehouse and local SQL Server supported through SSMS?
2. Can stored procedures or queries in localhost SQL Server access Fabric Lakehouse tables?
3. Are linked servers, external tables, gateways, or any additional configurations required?
4. What is the recommended approach for loading Fabric data into local SQL Server tables?

My objective is to validate large-volume data movement between the source and local target tables.

Any guidance would be appreciated.

[v-echaithra]

Hi @janudaS ,

We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-echaithra]

Hi @janudaS ,

Thank you @tayloramy , @Murtaza_Ghafoor  for your inputs.
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you.

[tayloramy]

Hi @janudaS, 

If you want to push data from Fabric down to a local SQL Server, you will need an enterprise data gateway. 

THen you can set up a pipeline in Fabric that will target your local SQL Server through the gateway.  

[Murtaza_Ghafoor]

@janudaS 

Why this is happening:
Your local SQL Server instance cannot directly “see” or query Microsoft Fabric Lakehouse tables just because both connections are available in SSMS. SSMS only acts as a client tool; it does not necessarily enable cross-environment querying between Fabric and local SQL Server.

Recommended Approaches

Linked Server (Recommended for Validation Scenarios)

You need to create a Linked Server in your local SQL Server pointing to the Fabric SQL Endpoint.

Workaround:

Connect to Fabric SQL Endpoint

Create Linked Server in local SQL Server

Query Fabric tables using OPENQUERY

Example:

SELECT *

FROM OPENQUERY([FabricLakehouse], 'SELECT * FROM dbo.TableName')

Then load into local tables:

INSERT INTO LocalTable

SELECT *

FROM OPENQUERY([FabricLakehouse], 'SELECT * FROM dbo.TableName')

Requirements:

ODBC Driver 18 for SQL Server

Fabric SQL Endpoint hostname

Authentication configuration

Network/firewall access must be enabled

This is usually the straightforward approach for testing large-volume validation.
If this option does not work,

Then try next option

Option 2 — Fabric Data Pipeline / Copy Activity (Best Enterprise Approach)

Use Microsoft Fabric Data Factory pipelines.

Source:

Lakehouse

Destination:

Local SQL Server

Requirements:

On-Premises Data Gateway

SQL connectivity from gateway machine

Advantages:

Better for large-scale data movement movement

Comparable loading

Monitoring and Retries

Production-ready-data.

If this helps, ✓ Mark as Kudos | Help Others