Linked server to MS Access...

Question

Post reply

Linked server to MS Access...

Vidas Zabinskas

Hall of Fame

Points: 3258
More actions
May 25, 2005 at 6:16 am

#86875

Hello,

I have the following problem:

On server S1 I've made linked server MS to Microsoft Access database with the following scripts:

EXEC sp_addlinkedserver

    @server = 'MS',

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

    @srvproduct = 'OLE DB Provider for Jet',

    @datasrc = '\\server\user\Corp\MS.mdb'

Then added login:

EXEC sp_addlinkedsrvlogin

    @rmtsrvname = 'MS',

    @useself = false,

    @locallogin = 'sa',

    @rmtuser ='admin',

    @rmtpassword = NULL

When I'm trying to connect to MS from S1 then all work fine. The problem appears when I try to query MS from other place through QA connected to S1 or from other registered servers... for IE: I login to S2 (other sql server) then open QA then connect to S1 and my queries to MS are not successfull. Any ideas?

Thanks for your help in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15256 More actions · Answer 1

When you specify 'sa' as your @locallogin you will only be able to use the linked server if you are connected as sa. If you want other users to be able to use the linked server you must create additional linked server logins, or you can specify 'null' for @locallogin which tells SQL server that ANY authenticated login should be mapped to the remote login of that linked server. Be careful with that one as it is a very common way to create a huge security risk!

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 2

Thank you for a reply, but it doesn't work. On EM I get the same error as earlier:

Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

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

Trying to execute select statement through QA I get the following:

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

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

[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\server\user\Corp\MS.mdb'.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]

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

I checked the following KB about error 7399, but it didn't helped

http://support.microsoft.com/default.aspx?scid=kb;en-us;814398

I remember we had the same issue with linked sql servers, but we solved that by entering some values in provider string on the linked server like: server name, uid, pwd and network. But on access linked server I cann't find any parameters to enter that 'provider string'

Any ideas?

Parag Sohoni-154912 SSC Veteran Points: 246 More actions · Answer 3

i had a similar kind of an error when i created a linked server between SQL Server and Access (MDB file residing on my local machine).

found out that it is an privilege issue problem.

so copied the mdb file on the SQL Server machine in a folder which the user who has logged into SQL Server has access to and then added the link server.

now it works fine with no probs at all.

hope this helps.

-- Parag

Rolandas SSC Enthusiast Points: 107 More actions · Answer 4

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

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

[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\server\user\Corp\MS.mdb'.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]

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

Please review BOL article "delegation of security account" (from index)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

Your user has no access rights to fileshare \\server\user

There are two possibilities:

1. Use Windows authentication, configure linked server and delegation accordingly.

2. Use SQL authentication and run SQL Server not as a "LocalSystem", but as a real Windows account having access permissions to \\server\user.