ACC: Linked table to SQL Server does not prompt for SQL login / password as expected

Article translations Article translations
Article ID: 2650051 - View products that this article applies to.
Expand all | Collapse all

Symptoms

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.

Cause

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=NorthwindSQL
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.

Resolution

You could use Windows Authentication to prevent this issue. For more information, please see the following knowledge base article:

Choosing an Authentication Mode
http://technet.microsoft.com/en-us/library/ms144284.aspx

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:
  • Use a user DSN or a machine DSN instead of a file DSN.
  • Remove the user's Windows Authentication permissions from servar that is running SQL Server.
  • Use the SQL Native Client Driver instead of the SQL Server driver.

More information

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:
209502 How to store SQL database login IDs and passwords locally

Properties

Article ID: 2650051 - Last Review: July 12, 2013 - Revision: 2.2
Applies to
  • Microsoft Office Access 2003
  • Microsoft Office Access 2007
  • Microsoft Access 2010
Keywords: 
KB2650051

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com