Hi both,
If you read my question, you will see that the issue really has nothing to do with linked servers, so my reason for using them is irrelevant.
Scenario 1:
I log into a laptop (remote client) and launch SSMS as domain\user1, without elevated permissions
I connect to the instance using Windows Auth (i.e. as domain\user1)
I run a script that queries files stored on the server's local file system using the ACE 12 provider
It works totally fine
Scenario 2:
I log onto the server running SQL and launch SSMS as domain\user1, without elevated permissions
I connect to the instance using Windows Auth (i.e. as domain\user1)
I run a script that queries files stored on the server's local file system using the ACE 12 provider
It fails
Scenario 3:
I log onto the server running SQL and launch SSMS as domain\user1, WITH elevated permissions
I connect to the instance using Windows Auth (i.e. as domain\user1)
I run a script that queries files stored on the server's local file system using the ACE 12 provider
It works
How is it possible that scenario 1 works without having to consider UAC, but when I execute from the server (where the files are, located, I have to elevate. I would kind of expect it the other way around, but this makes no sense to me.
Can you explain it? Ignore linked servers. FYI I did start out using OPENROWSET, and it obviously has the same problem. But all that history is irrelevant and distracting, so I left it out. I simply want to fix it as it is currently setup. I need to solve 7303 error, and the answer lies between scenario1, 2 and 3.
Thanks