Logon errors occur when you connect to a SQL Server database from a Microsoft Access project (297373)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q297373
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you try to connect to a Microsoft SQL Server database from a Microsoft Access project (ADP), you may receive the following error:
Microsoft Access could not find the database on the server. Verify that the database name is correct.
If you click the Test Connection button in the Data Link Properties dialog box, you receive the following error message:
Test connection failed because of an error in initializing provider. Cannot open database requested in login <database name>. Login Fails.

CAUSE

The specified user account has not been granted permissions to the SQL Server database that you are trying to connect to.

RESOLUTION

With a Full Installation of SQL Server

Grant permission for the specified account on the database that you are trying to connect to. Access projects can connect to SQL Server through a specific SQL Server user or by using a network domain account. With full installations of SQL Server, you can use Enterprise Manager to create user accounts and grant permissions.

With SQL Server 2000 Desktop Engine

A SQL Server 2000 Desktop Engine installation does not include Enterprise Manager. Addionally, by default, SQL Server 2000 Desktop Engine is configured to allow only members of the local Windows NT or Windows 2000 administrators group or the SQL Server Administrator account, "sa," to access the database.

To grant permissions to the database in SQL Server 2000 Desktop Engine, you can use the following options:
  • You can use Integrated Security to add the user who is running the Access project to the Windows NT or Windows 2000 local administrators group. However, this also gives the user full access to the computer.
  • You can enable the "sa" account with a password and allow the user to log on as a SQL Server Administrator. The user will have full access to the SQL Server, but not full access to the Windows NT-based computer or the Windows 2000-based computer.
  • You can install SQL Server Enterprise Manager from the SQL Server 7.0 or SQL Server 2000 compact disc. This gives you the management tools to add network domain users to the database and to provide security at the object level.
  • You can install Microsoft Office XP Developer. Microsoft Office XP Developer includes the SQL Server client tools, including Enterprise Manager.
  • You can execute SQL Server stored procedures to grant access to the database.

STATUS

This behavior is by design in a Microsoft Access project.

MORE INFORMATION

By default, when you run SQL Server 2000 Desktop Engine on Microsoft Windows NT 4.0 or Microsoft Windows 2000, SQL Server 2000 Desktop Engine Setup configures the installed instance of Microsoft SQL Server to use Windows Authentication and places the Windows local administrator's group in the SQL Server sysadmin fixed server role. When you run SQL Server 2000 Desktop Engine on Microsoft Windows 98, SQL Server 2000 Desktop Engine Setup always configures the installed instance to use SQL Server Authentication.

To allow access to the database, you must grant the user permissions. For integrated security, you must make the user a member of the local admins group on the Windows 2000 server. For Windows 98, you must use the SQL Server administrative account, "sa."

There are additional options that you can use to provide more restricted security. These involve installing Enterprise Manager or executing SQL Server stored procedures to create logons and database access rights.

These additional options are covered in more detail in the "Configuring SQL Server 2000 Desktop Engine" white paper. You can download this white paper from the following Microsoft Web site:

http://download.microsoft.com/download/Access2002/whitepap/2002/WIN98Me/EN-US/AccSQL02.exe

Steps to Reproduce the Behavior

  1. Use Enterprise Manager to create two new databases on SQL Server. Name them Test1 and Test2.
  2. Create a new logon. Set its default database as Test1.
  3. Add this new logon as a database user on Test1.
  4. Create a new Access project (existing data).
  5. In step 1 of the Data Link Properties dialog box, select your server. In step 2, click Use a specific user name and password. Then, enter the user name and password of your new user.
  6. Click Test Connection to verify that the connection works.
  7. In step 3 of the Data Link Properties dialog box, select the Test1 database. Then, click Test Connection. Note that the connection works.
  8. In step 3 of the Data Link Properties dialog box, select the Test2 database. Note that you receive the second error message that is mentioned in the "Symptoms" section of this article.
  9. Click OK to the error message that you receive in step 8. Then, click OK in the Data Link Properties dialog box. Note that you receive the first error message that is mentioned in the "Symptoms" section of this article.

REFERENCES

For more information about security in Microsoft SQL Server, refer Microsoft SQL Server 2000 Books Online. Microsoft SQL Server 2000 Books Online is available at the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/portal_7ap1.htm

Modification Type:MinorLast Reviewed:8/9/2004
Keywords:kbdownload kbdownload kberrmsg kbprb KB297373
©2004 Microsoft Corporation. All rights reserved.