Problem with Linked Server?!?

Question

Problem with Linked Server?!?

stephenmbell

SSC Eights!

Points: 926
More actions
October 13, 2011 at 10:17 am

#249173

Let me try to set this up -
I have a SQL 2008 R2 Express installation on machine SQLServer. This data is accessed via an MS Access front end. The end users basically import data into various tables on the sql server using a pass through query that calls a stored procedure. The stored procedure does a bulk insert from a text file into a table.
In this stored procedure, I needed some data from a different Access DB on machine FileShare. So I created a Linked Server to this Access DB. I am using Windows Authentication for my authentication model.
When I open the Access Front End and try to run the pass through query - WHILE I AM LOGGED ON TO THE MACHINE SQLServer (as my account) - the import runs fine - it completes successfully.
When I try to run the Access Front End from my machine, logged in as me, the process errors?!?
The error I am getting is:
OLE DB provider for linked server "servername" returned message "The Microsoft Jet database engine cannot open the file "\\fileshare\folder\access.mdb" It is already opened exclusively by another user, or you need permission to view its data"
The error itself seems pretty straight forward, my question is - why does it work when I am logged on to the SQL Server locally but not when I run it from my local machine?!?
Thanks
sb

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

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Surely this is a permissions issue? Are you using a domain account to log in at both locations, or are you using two different local accounts? Have you tried modifying your ODBC connection (MS Jet) to Access to use a dedicated SQL Server credential instead of Kerberos/NTLM? Check your local security policies and ensure the access is provided.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

stephenmbell SSC Eights! Points: 926 More actions · Answer 2

I agree - it must be a permission issue. I am just confused by it.

The SQL Server service is a domain account. It is the same account that we use for all of our SQL Servers. My machine and the SQLServer machine are members of the same domain, while the FileServer is a member of a different forest / domain, we are currently migrating away from - but there are trusts set up so that the users in all forests / domains can access this FileServer. It is our main file server - everyone in the company accesses this server in this manner.

I will try changing it to a SQL Server Authentication account and see what happens.

Will post back the results.

Thanks

sb

stephenmbell SSC Eights! Points: 926 More actions · Answer 3

I just tried to do this using a different user in my ODBC and I am getting the same error....

I feel like maybe it is an issue with the hops it is taking...

from my machine --> sql server --> linked server ???

Just a hunch - not really based on anything though...

sb