Sql Server Linked Server connection to Fabric Sql Endpoint

[hwallop76]

Hi,

I'm trying to create a Linked Server connection from SQL Server to Fabric Lakehouse SQL Endpoint. The idea is to easily get data from Fabric to on-prem SQL Server. The current On-Prem solution is not using e.g SSIS to integrate data but all the integrations are done with Linked Server connections.  

Following this documentation (https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/azure-ad-a...) I'm trying to use AAD application credentials to authenticate against Lakehouse SQL endpoint.

Error:

"Cannot use Access Token with any of the following options: Authentication, Integrated Security, User, Password.
OLE DB provider "MSOLEDBSQL" for linked server "F2" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 0)""

Seems like this is not possible with Fabric, does anyone know better? Thanks.

[GilbertQ]

Hi @hwallop76 

It does appear not to work because of the way it needs to authenticate with a username and password as if you were logging in.

Have you tried using the data pipeline copy activity and try and copy the data from the Fabric SQL End point to your SQL Server via the gateway?

[hwallop76]

Hi @GilbertQ 

To my knowledge there is no Self-Hosted Integration Runtime for Fabric, so I cannot push data to On-Prem with Copy Activity. 

The requirement was for the client application (SQL Server) to pull data from Fabric. I ended up using SSIS after all, since the connection worked well with AAD service principal.