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).
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.
The specified user account has not been granted permissions to the SQL Server database that you are trying to connect to.
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.
This behavior is by design in a Microsoft Access project.
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
Use Enterprise Manager to create two new databases on SQL Server. Name them Test1 and Test2.
Create a new logon. Set its default database as Test1.
Add this new logon as a database user on Test1.
Create a new Access project (existing data).
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.
Click Test Connection to verify that the connection works.
In step 3 of the Data Link Properties dialog box, select the Test1 database. Then, click Test Connection. Note that the connection works.
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.
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.
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: