Consider the following secnario: You create a File DSN that uses the Microsoft SQL Server driver and SQL Authentication for security. When you create the linked table, one of the following conditions is true:
You do not click to select the Save Password option in the Link Tables dialog box.
The MSysConf table has been configured on server that is running SQL Server to prevent the password from being saved in Access.
In this scenario, when you open the linked table in Access, you expect to be prompted for the SQL Server login and password. Instead, the table opens without requesting credentials.
In this scenario, the connection string that is stored in Microsoft Office Access resembles the following:
Because this connection string does not specify the user ID (UID), Access tries to use Windows Authentication. If the user is successfully authenticated by using Windows Authentication, you are not prompted for the SQL Server login and password.
You could use Windows Authentication to prevent this issue. For more information, please see the following knowledge base article: