ODBC driver connect to Fabric

[schneiw]

Hi 

I want to set up a linked server in an on prem SQL Server instance. My thought wa to set up the ODBC connection, then in SQL server set up the lnked server using the ODBC driver connection.

I am using the standard ODBC admin tool in Windows to set it up, and I was trying to see if using the "Microsoft Entra ID with password authetication" method would work, since for the linked server I would not be able to use the interactive authentication method.

However, it keeps throwing this message:

I know the credentials are correct because I can use them in Sql Server Management Studio to connect via the same method. Is there a bug in the ODBC driver or what should I be looking out for? Any ideas?

Thanks!

[v-agajavelly · Accepted solution]

Hey @schneiw 

You're doing everything right, using the non-interactive "Microsoft Entra ID Password authentication" option, credentials are correct, MFA is excluded, and it even works fine in SSMS. So honestly, you'd expect the ODBC driver to behave the same way, just take the username and password and connect. But instead, it throws that annoying “InteractionRequired” error anyway. Really confusing. From what I’ve seen, and based on that GitHub issue you linked, it does seem like the ODBC driver (especially version 18) is acting up in scenarios like this. Even though you're not using interactive auth, it's still trying to trigger something interactive behind the scenes, which kind of defeats the purpose. It feels like the driver is either not fully honoring the selected auth method or maybe it’s hitting some unexpected behavior when used in linked server setups or background processes.
__________________________________________________________________________________________________________________________

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Akhil.

[schneiw]

Thanks for confirming.

I tried the Service Principal option and that one works fine. I guess we just have to wait for MS to fix the bug.

Thank you, I will accept your answer as solution. 

[v-agajavelly]

Hi @schneiw 

Thanks for sharing the detailed info and the error message screenshot that definitely helps narrow things down!

What you're running into here is a common limitation with using Entra ID (Azure AD) Username/Password auth in non-interactive tools like the ODBC driver. Even though the credentials are correct (and work fine in SSMS), the issue is that the ODBC driver doesn't support interactive prompts and many Entra ID accounts these days are behind MFA or Conditional Access policies that require those interactive steps. That’s why you’re seeing the "InteractionRequired" and 'Found unknown spec version 'pii' errors.

According to my experience  couple of ways you could approach this using Azure AD Service Principal (App Registration), SQL-authenticated user and Using ActiveDirectoryPassword with Entra ID.

Using Azure AD Service Principal.
If possible, consider using Azure AD Service Principal (App Registration) with a client secret. This method is designed for non-interactive scenarios like yours (ODBC + linked server) and doesn’t rely on a user login or MFA. You’d set it up in Azure, assign it access to your Fabric SQL endpoint, and then use ActiveDirectoryServicePrincipal as the authentication type in your ODBC connection.

Using SQL-authenticated user.
If allowed in your environment, creating a SQL-authenticated user (with username/password) directly in the Fabric SQL endpoint and using that for the ODBC connection can also work this avoids Entra ID altogether.

Using ActiveDirectoryPassword.
Using ActiveDirectoryPassword with Entra ID credentials usually won’t work reliably unless MFA and Conditional Access are turned off, which isn’t typical in most orgs anymore.

________________________________________________________________________________________________________________________
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Reagrds,
Akhil.

[schneiw]

I see that message relating to "...converted from Unexpected to InteractionRequired. But look at these two connection options. One states to use a usernam and password whereas the other refers to Interation. I am not using the one referin to  Interaction, so it should just use the username and password, unless its bugged? 

I also found this link that seems to point the ODBC driver being bugged, can you confirm? Connection Issue with Microsoft Fabric Data Warehouse using MSODBC 18, sqlsrv 5.12.0, and pdo_sqlsrv...

[schneiw]

Using ActiveDirectoryPassword.: I did try this option, and confirmed with our Azure admin that the username that I a using is in an exclusion group for MFA. I was able to use this option successfully in a tool like SSMS, which is why I am confused that the ODBC driver will not connect using the same meathod.

[schneiw]

Thank you I will try some of these. 

Can you point to any documentation on this option? I cannot find it and ChatGPT does not seem to provide workable instauctions: Using SQL-authenticated user.