Article ID: 297373 - View products that this article applies to.
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:
If you click the Test Connection button in the Data Link Properties dialog box, you receive the following error message:
Microsoft Access could not find the database on the server. Verify that the database name is correct.
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 ServerGrant 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 EngineA 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:
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
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:
Article ID: 297373 - Last Review: November 12, 2012 - Revision: 1.0