Access: Linked table "connection failed" messages when using Microsoft Entra interactive authentication
Symptoms
In Microsoft Access, you create a linked table that uses Microsoft Entra interactive authentication. When you open objects that use this connection, you experience repetitive "connection failed" messages that resemble the following example:
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're 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, Microsoft Entra 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).
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for