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

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)

😉