Access: Linked table "connection failed" messages when using Azure AD interactive authentication

Note

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Symptoms

In Microsoft Access, you create a linked table that uses Azure Active Directory interactive authentication. When you open objects that use this connection, you experience repetitive "connection failed" messages that resemble the following:

Connection failed: SQLState: 'FA003' SQL Server Error: 0 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] User option must be specified, if Authentication option is 'ActiveDirectoryInteractive'.     

After you receive these messages, you are prompted to sign in.

Cause

When you establish the connection in Access, the Save Password option is not selected. Therefore, the connection string that is stored in Access is missing the user ID (UID).

Note

Although the option is labeled as Save Password, selecting this option stores both the UID and PWD (if it exists) in the connection string.

Resolution

In this situation, Azure Active Directory (Azure AD) interactive authentication doesn't use a stored password. However, you should select the Save Password option to store the UID in the connection string.

More Information

If you create the linked table through DAO in Visual Basic for Applications (VBA), you should specify the Save Password option as a table attribute, as follows:

td.Attributes = dbAttachSavePWD

For more information about the dbAttachSavePWD attribute, see TableDefAttributeEnum enumeration (DAO).