Forum Discussion

VenDaFabricator's avatar
VenDaFabricator
Icon for Resolver I rankResolver I
10 months ago
Solved

Accessing Fabric Lakehouse data from SQL Server 2016/2022 (Linked Server or Live Query)

I have a requirement to establish a linked server or equivalent capability that allows SQL Server 2016 or 2022 to query Microsoft Fabric Lakehouse data — ideally as a live connection (witho...
one lake
  • VenDaFabricator's avatar
    VenDaFabricator
    10 months ago

    Hi MJParikh, v-dineshya 

    I now can successfully create linked server in SQL2016 / 2022 to Fabric Lakehouse using Service Principal Credential, here is my script:

    EXEC master.dbo.sp_addlinkedserver 
       @server = N'FABRIC', 
       @srvproduct=N'Fabric SQL', 
       @provider=N'MSOLEDBSQL19', 
       @datasrc=N'', 
       @provstr=N'Server=xx.fabric.microsoft.com;Authentication=ActiveDirectoryServicePrincipal'

EXEC master.dbo.sp_addlinkedsrvlogin 
        @rmtsrvname=N'FABRIC',
        @useself=N'False',
	@locallogin=NULL,
        @rmtuser=N'xxxxx',
        @rmtpassword='xxxxx'

     
    2.  make sure 1433 is configured with your SQL server in SQL Server Configuration Managner > SQL Server Network Configuration > Protocols for SQL Server > TCP/IP > IP Addresses > TCP Port > 1433

    3.  On MSOLEDBSQL19 Provider > Properties > Provider Options > make sure 'Allow inprocess' is checked.

     

    Now you should be able to see linkedserver established (screen shot below)

    4. To query fabric lakehouse table, should use openquery (see below)

    😉



MJParikh's avatar
MJParikh
Icon for Super User rankSuper User
10 months ago

Linked Server to a Fabric Warehouse or Lakehouse SQL endpoint is not supported. Even if you get MSOLEDBSQL 19 to authenticate with a service principal, Microsoft does not support Linked Server for Fabric, so you will hit auth and stability issues. 

Use one of these approaches.

  • OPENROWSET with MSOLEDBSQL 18/19, no Linked Server
    This is ad hoc, but works from SQL Server 2016 and later.

 
-- Enable ad hoc access once
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

-- Query Fabric SQL endpoint directly
SELECT TOP 5 *
FROM OPENROWSET(
  'MSOLEDBSQL',
  'Server=waeXXXXXXXX.datawarehouse.fabric.microsoft.com;
   Database=Silver;
   Encrypt=yes;TrustServerCertificate=no;
   Authentication=ActiveDirectoryServicePrincipal;
   Authority Id=<tenant-guid>;
   User ID=<app-client-id>;
   Password=<app-client-secret>;',
  'SELECT TOP 5 name FROM sys.tables ORDER BY name'
);

Requirements: latest Microsoft OLE DB Driver for SQL Server, and Entra service principal access to the Warehouse or Lakehouse SQL endpoint. OPENROWSET is the supported alternative to Linked Server for one-time connections. 

  • PolyBase external tables over OneLake files
    If you need near real time without copying into SQL Server tables, point SQL Server 2022 PolyBase at OneLake using the ADLS Gen2 compatible ABFS endpoint, then read Delta or Parquet as external tables. You will configure PolyBase, create a database scoped credential, an external data source to the abfss path, then external file format and external tables.
    Key references: PolyBase external data sources for ADLS Gen2, OneLake ABFS endpoint format. 

Notes to fix your current script if you still want to test it, understanding it is unsupported for Linked Server:
• Use the correct provider name that matches your install, MSOLEDBSQL or MSOLEDBSQL19, and set “Allow inprocess.” 
• Keep Encrypt=yes and TrustServerCertificate=no. Use Authentication=ActiveDirectoryServicePrincipal. Use Authority Id with your tenant GUID. User ID is the app’s client ID. Password is the client secret. These are the exact OLE DB keywords for Entra service principal auth.
• Ensure the app has Workspace permissions and a corresponding external user in the Warehouse or Lakehouse SQL endpoint with the right grants. 

If you need scheduled or near real time jobs inside SQL Server, prefer OPENROWSET in stored procedures, or stage a small subset into Azure SQL DB and link to that, since Linked Server to Fabric is not supported.

VenDaFabricator's avatar
VenDaFabricator
Icon for Resolver I rankResolver I
10 months ago

Thanks MJParikh for detailed explanation, tried with openrowset, but still have issues.

EXEC SP_CONFIGURE 'show advanced options', 1; reconfigure;
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; reconfigure;

SELECT TOP (5) *
FROM OPENROWSET(
  'MSOLEDBSQL19',
  'Server=wae26xxxx.datawarehouse.fabric.microsoft.com;
   Databasae=Silver;
   Encrypt=yes; TrustServerCertificate=no;
   Authentication=ActiveDirectoryServicePrincipal;
   Authority Id = 0xxx5,
   User ID=xxxx;
   Password=xxxx;',
  'SELECT TOP 5 name FROM sys.tables ORDER BY name'
);

Error: 

 

OLE DB provider "MSOLEDBSQL19" for linked server "(null)" returned message "Invalid authorization specification".
OLE DB provider "MSOLEDBSQL19" for linked server "(null)" returned message "Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 71
The OLE DB provider "MSOLEDBSQL19" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 71
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL19" for linked server "(null)".