Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Logon errors occur when you connect to a SQL Server database from a Microsoft Access project

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

View products that this article applies to.

Advanced: Requires expert coding, interoperability, and multiuser skills.

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


↑ Back to the top

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.

↑ Back to the top

Cause

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

↑ Back to the top

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.

↑ Back to the top

Status

This behavior is by design in a Microsoft Access project.

↑ Back to the top

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.


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.

↑ Back to the top

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://msdn2.microsoft.com/en-us/library/aa257103(SQL.80).aspx

↑ Back to the top

Keywords: kbprb, kbswept, kboffice2003yes, kbbillprodsweep, kbfreshness2006, kberrmsg, kbdownload, kboffice12yes, kb, kbsweptsoltax

↑ Back to the top

Article Info
Article ID : 297373
Revision : 3
Created on : 4/18/2018
Published on : 4/19/2018
Exists online : False
Views : 357