linked-server sql - access Problem

Question

Hi,

I have a SQL server 2000 and an Access database mdb connected by Linked server on the other hand I have a program in c # that updates data in a SQL table (Users) based data base access.

When running my program returns the following error message:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed. [OLE / DB provider returned message: Can not start the application. Missing information file of the working group or is opened exclusively by another user.] OLE DB error trace [OLE / DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize:: Initialize returned 0x80040E4D: Authentication failed.]´ .

Both the program, the sql server and database access are on a remote server.

On the local server the problem was solved by running the following:

"sp_addlinkedsrvlogin 'ActSC', 'false', NULL, 'admin', NULL".

Try on the remote server the next, without result: "sp_addlinkedsrvlogin 'ActSC', true, null, 'user', 'pass'".

On the remote server and from the "Query Analyzer" sql update statements are working correctly.

Can you think of what may be the problem?

Thanks!

Answer 1

You can't access mdw file remotely? Specify remote mdw file location in provider string (Jet OLEDB:System Database=MySystem.mdw) and allow access to it from local server.

Answer 2

My guess is that this is related to user impersonation. Simply said: when you use Query Analyser you're using a different login than when you're accessing the server from your C# application. Try by setting the same username & password in your C# app.

You could solve this by configuring the linked server connection with a username and password, so that SQL server disregards any other password/current user credentials. Just use/check the last option/radio in this printscreen (disregard the login mappings list in the middle) and enter the username & password to use for ALL connections: