Microsoft KB Archive/285833

= PRB: Need to map to default Admin account and use NULL for password to query linked server to Access database =

Article ID: 285833

Article Last Modified on 12/23/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft Access 2000 Standard Edition
 * Microsoft SQL Server 2000 Desktop Engine (MSDE)
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition Service Pack 3
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q285833



SYMPTOMS
When you set up a linked server from a Microsoft SQL Server server to a Microsoft Access database, if explicit login mapping is performed, which is required for non-sa SQL Server server logins after you apply US Service Pack 2 for SQL Server 7.0, you must map local SQL Server logins to the default Admin user account in the Access database. Moreover, you have to pass NULL to the @rmtpassword parameter in the sp_addlinkedsrvlogin stored procedure, or leave an empty password for the Admin account in the Linked Server Properties dialog box from the SQL Enterprise Manager, even when the Access Admin account does not have a blank password. If you provide the actual password for the Admin account in sp_addlinkedsrvlogin or in the SQL Enterprise Manager, or you map the local SQL Server server logins to a regular user account other than the default Admin account in the Access database, querying such a linked server fails with the following error messages depending on whether the local server is a SQL Server 7.0 server or a SQL Server 2000 server.

With trace flag 7300 turned on, SQL server 2000 will return the following error message:

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.

[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

SQL Server 7.0 returns a different error message:

Server: Msg 7303, Level 16, State 2, Line 1

Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.

[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d].



RESOLUTION
The workaround is to map the SQL Server logins to the Admin account of the Access database and:

  Pass NULL to the @rmtpassword parameter in the sp_addlinkedsrvlogin stored procedure. For example: USE master GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'linkedsvrname', @useself = false, @locallogin = 'loginame', @rmtuser = 'admin', @rmtpassword = NULL -or-

 Leave an empty password in the Remote Password text box for mapping the Admin account in the Linked Server Properties dialog box of SQL Enterprise Manager.



MORE INFORMATION
To secure an Access database, Microsoft highly recommends that you follow these steps:
 * 1) Create your own system workgroup file.
 * 2) Remove the default Admin user from the Admins group.
 * 3) Revoke all permissions from the Admin user.

For more information about Microsoft Access security, click the following article number to view the article in the Microsoft Knowledge Base:

254372 Overview of how to secure a Microsoft Access database

When all permissions are revoked from the Admin user account, querying the linked server returns the following error message as expected:

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider indicates that the user did not have the permission to perform the operation.

[OLE/DB provider returned message: Record(s) cannot be read; no read permission on 'AccessTableName'.]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' ICommandText::Execute returned 0x80040e09: The provider indicates that the user did not have the permission to perform the operation.].

By design, SQL Server system administrators can query a linked server to an Access database without using any security context. By revoking all permissions from the Admin user, it prevents anyone on the SQL Server side from accessing tables in the Access database. So, for SQL Server to be able to link to the Access database, the Access administrator must grant at least Read permission to the Admin user on the tables involved.

The following reproduction scenario uses the sample Northwind database. The same behavior is also observed in a secured user database.

Steps to Reproduce Behavior
 Start Microsoft Access 2000 and open the sample Northwind database. Change the password for the Admin account from Null to a non-empty password, such as &quot;access&quot;. To change the password, from the Tools menu, click Security and then select User and Group Accounts. Create a new user account named testuser. Grant proper permissions to the testuser account.</li> Restart Access and log on as testuser. Change the password for testuser to testsql.</li>  In the SQL Query Analyzer, run the following stored procedure to add the linked server named test_Access to the Northwind database in Access: USE master GO

EXEC sp_addlinkedserver

@server = 'test_Access',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@srvproduct = 'OLE DB Provider for Jet',

@datasrc = 'C:\samples\northwind.mdb'

GO </li>  In the SQL Query Analyzer, map the local login named john to the remote admin account whose password is &quot;access&quot;: USE master GO

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'test_access',

@useself = false,

@locallogin = 'john',

@rmtuser ='admin',

@rmtpassword ='access',

GO </li>  In SQL Query Analyzer, log on to the SQL Server server as john, and then run the following test query: USE master GO

SELECT * FROM OPENQUERY( test_access, 'select * from customers' ) The error message described in the &quot;Symptoms&quot; section occurs.

</li>  Modify the call to the sp_addlinkedsrvlogin stored procedure as follows, and then run the stored procedure in Query Analyzer as sa: USE master GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'test_access',

@useself = false,

@locallogin = 'john',

@rmtuser ='admin',

@rmtpassword = NULL,

GO </li> Log on to the SQL Server server as john, and then run the preceding SELECT query again. Now, the query returns all the records.</li>  Log on to the SQL Server server as sa, run the sp_addlinkedsrvlogin stored procedure with different parameters in order to map john to testuser: EXEC sp_addlinkedsrvlogin @rmtsrvname = 'test_access',

@useself = false,

@locallogin = 'john',

@rmtuser ='testuser',

@rmtpassword = 'testsql' -- or pass null instead of real password

</li>  In the Query Analyzer, log on to the SQL Server server as john, and then run the same test query: USE master GO SELECT * FROM OPENQUERY( test_access, 'select * from customers' ) The error message described in the &quot;Symptoms&quot; section occurs. </li></ol>

This problem exists for both SQL Server authentication logins and Microsoft Windows NT authentication logins. The problem also applies to both system admin logins and non-system admin logins.

Keywords: kbbug kbprb KB285833

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.