Article ID: 2650051 - View products that this article applies to.
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:
In this scenario, the connection string that is stored in Microsoft Office Access resembles the following:
DRIVER=SQL Server;SERVER=servername\sqlinstancename;APP=Microsoft Office 2010;DATABASE=NorthwindSQLBecause 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:
Choosing an Authentication Mode
If you want to use SQL Authentication but you do want not store the SQL login and password data in Access, you can use one of the following workarounds:
For more information about how to store SQL Server login IDs and passwords, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/default.aspx?scid=kb;EN-US;209502)How to store SQL database login IDs and passwords locally