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

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

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.