ODBC and OLE DB connection to Microsoft Fabric through Service Principal - Not working

[govindarajan_d]

Hi All,

There is a SQL DB in a VM that is used for the backend of a C# application. The SQL DB needs data from Fabric every 15 mins based on some parameters. 

1. We tried using Linked Servers through Service Principal connection and specified the connection as below. It succeeds during test connection, but I am not able to query. It just returns access denied even if I run something like this -

exec('SELECT 1 a ') AT FABRIC

EXEC master.dbo.sp_addlinkedserver @server = N'FABRIC', 
   @srvproduct=N'Fabric SQL', 
   @provider=N'MSOLEDBSQL19', 
   @datasrc=N'<server>.datawarehouse.fabric.microsoft.com', 
   @provstr=N'Authentication=ActiveDirectoryServicePrincipal', 
   @catalog=N'<warehouseOrLakehouseName>'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'FABRIC',
   @useself=N'False',
   @locallogin=NULL,
   @rmtuser=N'<clientId>@<tenantId>',
   @rmtpassword='<clentSecret>'

  2. We tried ODBC connection again through Service Principal. Even though test connection suceeds (and it also brings the database names), we get the following error when using the ODBC to query data.

ERROR [FA003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Both User and Password options must be specified, if 
Authentication option is 'ActiveDirectoryServicePrincipal'.

Has anyone successfully established OLEDB and ODBC through Service Principal in Fabric?

[Pseud0nym]

I too am running into the same error having attempted to follow the instructions found in this reddit thread from a Microsoft Employee: Extracting data from Fabric LH into a SQL server in VM : r/MicrosoftFabric (are you by any chance the same user who started that thread?)

I'm wondering if it's a tenant setting I need to change but so far all I can find on that is this post which I've already implemented How to use service principal authentication to access Microsoft Fabric's OneLake

Currently have no issue connecting using the service principal directly to the SQL endpoint, but I too get the following error when trying to use it in a linked server:

Please let me know if you get any further

[Pseud0nym · Accepted solution]

SQL Server linked server "Access denied" error when querying Connect64 for SSIS provider - Progress ...

This solved my issues when using the MSOLEDBSQL19.

Specifically:

Resolution

• Launch Microsoft SQL Server Management Studio
• Navigate to Server Objects > Linked Servers > Providers
• Right click DataDirect.SSISOLEDBProviderforSybase (or the provider being used) MSOLEDBSQL19
• Check "Allow inprocess"
• Delete and recreate Linked Server

The query should now succeed. I've only managed to make it work with EXEC and OPENQUERY statements so far but that's still a massive improvement.

[govindarajan_d]

Thanks! This actually helped for OLE DB connection!

[spencer_sa]

I'm not sure accessing the SQL endpoint via a service principal is supported as of yet.  It's certainly been a requested feature.  I have successfully connected externally using the ODBC driver using both R-Studio and standalone Python and had to use the 'user' based access.