Access: Linked table "connection failed" messages when using Azure AD interactive authentication
Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.
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.
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).
Although the option is labeled as Save Password, selecting this option stores both the UID and PWD (if it exists) in the connection string.
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.
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).