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

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.