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 (without needing to export/copy data).

 

I attempted to create a linked server using the script below, but I’m encountering an “authentication failed” error.

 

I’m using Service Principal credentials, which work fine when connecting directly to the Fabric Lakehouse SQL endpoint via SSMS.

 

However, the same credentials fail when used to create a linked server from SQL Server.

 

Has anyone successfully configured a linked server or found an alternative approach to achieve near real-time access from on-prem SQL Server to Fabric Lakehouse data?

 

Any guidance, configuration steps, or workarounds would be greatly appreciated.

 

My Script to create Linked Server:
-- 1) Enable provider in-process (once per instance)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLEDBSQL',   N'AllowInProcess', 1;
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLEDBSQL19', N'AllowInProcess', 1;
GO

-- 2) Create the linked server (note the @provstr carries AAD options incl. Tenant)
EXEC master.dbo.sp_addlinkedserver
  server   = N'FABRIC_LH',
  @provider = N'MSOLEDBSQL19',  -- or MSOLEDBSQL19
  @srvproduct = N'',
  @datasrc=N'waexxxx.datawarehouse.fabric.microsoft.com',
  @provstr    = N'Database=Silver;
                  Encrypt=yes;TrustServerCertificate=no;
                  Authentication=ActiveDirectoryServicePrincipal;
                  Authority Id=01axxx391-xx;';
GO

-- 3) Map security (client id as User ID; secret as password)
EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'FABRIC_LH',
  @useself    = N'False',
  @locallogin = NULL,                -- all local logins map the same way; adjust if you need per-login mapping
  @rmtuser    = N'xxxxxx',
  @rmtpassword= N'xxxxxxx';
GO

-- 4) Test
SELECT TOP 5 *
FROM OPENQUERY(FABRIC_LH, 'SELECT TOP 5 name FROM sys.tables ORDER BY name');
​



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)

    😉



6 Replies

  • VenDaFabricator's avatar
    VenDaFabricator
    Icon for Resolver I rankResolver I
    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)

    😉



    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      10 months ago

      Hi VenDaFabricator ,

      Thank you for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. This details will assist others facing similar challenges and benefit the wider community. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

      Regards,

      Dinesh

  • 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)".

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi VenDaFabricator ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    You want to access Fabric Lakehouse data from SQL Server 2016/2022 . You try to create a linked server using the script, but encountering an “authentication failed” error.

     

    Please try below workarounds.

     

    1. Use Fabric Pipelines with On-Premises Data Gateway. Install the On-Premises Data Gateway. Create a SQL Server connection in Fabric. Use Lookup + ForEach + Copy Data activities to pull data into Lakehouse.

     

    Note: It is useful for Scheduled or triggered data refreshes and Migration or replication scenarios.

     

    2. If you need real-time data streaming, use Estuary Flow, which supports Change Data Capture (CDC) from SQL Server to Fabric Warehouse.

     

    Note: No manual refreshes, Near real-time updates and No Linked Server needed.

     

    3. Fabric supports Mirrored Databases from SQL Server. It requires Fabric capacity and tenant settings. It uses managed identity for secure access. And Ideal for read-only analytics scenarios.


    Note: Service Principal authentication works in SSMS and Notebooks. Ensure Fabric tenant settings allow Service Principals to use APIs. For pipelines, Organizational Account is preferred over Service Principal.

     

    Please refer below links.

    Seamless Data Migration from On-Prem SQL Server to... - Microsoft Fabric Community

    Tutorial: Configure Microsoft Fabric Mirrored Databases From SQL Server - Microsoft Fabric | Microsoft Learn

    Authentication in SQL database - Microsoft Fabric | Microsoft Learn

    Solved: Re: creating a lakehouse connection using service ... - Microsoft Fabric Community

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

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

    MJParikh v-dineshya

    Had a quick discussion with Microsoft, and the key takeaways were:

    1. Linked Server connections from on-prem SQL Server 2016/2022 to Fabric Lakehouse are not supported yet.

    2. OPENROWSET queries don’t work in this scenario either.

    3. Setting up External Data Sources and External Tables for Lakehouse access is quite cumbersome, mainly due to the SAS token limitation — it expires after one hour.

    As an alternative, we managed to get it working in reverse — by pushing Fabric Lakehouse tables down to SQL Server on demand using a Copy Activity through the Gateway.