Linked Server Issue

[buddafish]

All,

I have a linked server established between 2005 sql box and network file server holding an access .mdb -

select * from linkedserver...tablename works when I am on the actual sql box logged in as SA or myself. Great!

When I am at my workstation using SSMS, I have success if I logon as SA but... if I logon as myself, no dice!

I have been attempting to use login mappings:
Local Login = me
Remote User = remote database admin
Remote PW = remote database admin pw

But this is no help. Common error message: basically says the database is either open exclusively by another user (false) or I do not have perms to read the data (also false).

Any help?

TIA - Scott

 

[mrdenny]

SQL 2005 likes to use the connecting users account when accessing file system objects on remote servers. This however causes the NTLM double hop issue to present it self.

I had the same issue when dealing with xml files stored on a remote file server. The files could be loaded fine when run as a job, but I got funky access denied messages when running from remote.

If you are building an SSIS package why are you trying to access the Access MDB via a linked server instead of making a connection object direct to the Access file?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[buddafish]

Thanks for the info. I am not using SSIS, but SSMS (management studio), just attempting to link to some access tables on a remote server for some views I am building in some databases on my SQL box.

If I might ask, what did you do for your xml file access issue?

thanks
Scott