Azure SQL Managed Instance + ODBC integrated auth issues - A disconnect event was raised when server is waiting for Federated Authentication token. This could be due to client close or server timeout expired

Question

I'm trying to configure a simple data entry tool

I have MS Access as the front end forms (.accde) using the SQL Server 18 ODBC driver to connect to a azure managed instance

everything works fine using a hardcoded sql login in the connection string

I'm not able to get entra/azure ad auth working seamlessly

quick background:

small company with no on-prem servers or a real AD server - its all EntraID

the managed instance service principal has access to the entra id graph to read from the domain - I'm able to add external users to the managed instance

Using WindowsAuthenticationIntegrated fails for everyone except me (i'm assuming i've got my creds cached somewhere)

DIAG [FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Entra ID (Authentication option is 'ActiveDirectoryIntegrated').
Error code 0x4B0; state 10
The credential is invalid.Exception during 'WindowsIntegratedAuthExchange' converted from Unexpected to InteractionRequired. Original context: 'WIA can only be used for federated accounts, but this account was Managed' (0) 
DIAG [01S00] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0) 

using WindowsAuthenticationInteractive populates the login prompt with the wrong username (FirstnameLastname) instead of the proper format for logging into azure (******@companydomain.com)

But even if i manually type in the correct username format it prompts for a password and triggers a MFA request every time the file is opened. This is for users already logged into their local PC and logged into all the microsoft cloud services (o365, email, onedrive, teams, etc etc)

Is it possible to make this less painful/enable SSO?

If a user is already logged into o365 it should 'just work'. Or at worst populate their actual username which works for logging into AzureAD

or do i need to stick to the hardcoded sql login which gives everyone with a copy of the .accde file read/write access to the DB?

Answer 1

Hi JK,

We would like to thank you for your feedback provided on survey. We found your recent engagement on this question was rated low. However, we are able to share some additional information. Would you please re-evaluate your feedback.

Using ActiveDirectoryInteractive triggers a login prompt with MFA every time because:

• The ODBC driver does not cache tokens persistently.
• The username format defaults to FirstnameLastname instead of ******@domain.com.
• There's no SSO context passed from Office apps like Teams or Outlook into the ODBC driver.
• The ODBC driver (even in version 17.6+) does not cache Entra ID tokens persistently
• ActiveDirectoryInteractive is designed for interactive use, not for seamless or silent SSO.
• The driver does not inherit SSO context from the OS or Office apps.
• The default behavior is not optimized for environments without full Active Directory federation or hybrid identity setup

Workarounds and Alternatives:

1.Use ActiveDirectoryIntegrated (if possible)

• If your environment supports it (i.e., domain-joined machines with hybrid Azure AD join), switch to:

Authentication=ActiveDirectoryIntegrated

• This mode attempts to use the current Windows session for authentication and avoids repeated MFA prompts.

2.Use ActiveDirectoryPassword with Secure Credential Storage

• If you must use username/password, consider:

Authentication=ActiveDirectoryPassword

UID=******@domain.com

PWD=yourpassword

This avoids the MFA prompt but is less secure unless paired with secure credential storage.

3.Use SQL Authentication

• As you noted, if integrated auth is too cumbersome, SQL logins may be a more practical alternative for now.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi JK,

To enable Single Sign-On (SSO) for Entra/Azure AD authentication with MS Access using SQL Server 18 ODBC driver, follow these steps and considerations based on the gathered information:

Steps to Enable SSO

1.Set Up Entra ID Admin:

• Ensure that your SQL Server instance has an Entra ID admin configured. This can be done through the Azure portal, PowerShell, Azure CLI, or REST APIs

Example using Azure portal:

• Navigate to your SQL Server instance.
• Click on "Active Directory admin" in the left-hand menu.
• Click on "Set admin" and select the desired Azure AD user or group.
• Save the configuration.

2.Configure Connection String:

• Use the appropriate connection string for Entra ID authentication. Here are examples for different authentication methods:
• Active Directory Password:

string ConnectionString = @"Server=demo.database.windows.net;Authentication=Active Directory Password;Encrypt=True;Database=testdb;User Id=******@domain.com;Password=<password>";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
}

• Active Directory Integrated:

string ConnectionString = @"Server=demo.database.windows.net;Authentication=Active Directory Integrated;Encrypt=True;Database=testdb;";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
}

• Active Directory Interactive:

string ConnectionString = @"Server=demo.database.windows.net;Authentication=Active Directory Interactive;Encrypt=True;Database=testdb;";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
}

• Active Directory Service Principal:

string ConnectionString = @"Server=demo.database.windows.net;Authentication=Active Directory Service Principal;Encrypt=True;Database=testdb;User Id=<client_id>@<tenant_id>;Password=<client_secret>";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
}

3.Enable SSO:

• To enable Single Sign-On (SSO) and avoid repeated MFA prompts, ensure that the users are properly authenticated and their credentials are cached. This can be achieved by using the Active Directory Integrated authentication method, which leverages the user's existing Windows session

Troubleshooting Common Issues:

1.Invalid Credentials:

• Ensure that the credentials provided in the connection string are correct and match the format required for Azure AD authentication. For example, use ******@companydomain.com instead of FirstnameLastname.

2.MFA Prompts:

• If users are repeatedly prompted for MFA, consider using Active Directory Integrated or Active Directory Service Principal authentication methods to streamline the login process

3.Network Connectivity:

• Verify that the network connectivity between the local machine and Azure SQL Database is not restricted by firewall rules or network policies

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.