This post is mainly for our own sanity if, or probably when, we encounter this problem again in the future but if it helps anybody else out, that's great!
We've been experiencing an issue with SQL Server and trying to execute an Openrowset query which reads from an Access database located on a network share.
No matter what we tried, one of our developers could not get the Openrowset query to execute, unless we promoted their Windows user account to a Domain Administator.
Everything seemed to be setup correctly:
SQL Server login for the Windows user account was set to be a sysadmin (only for the purposes of running this query in particular, bulkadmin would be enough under normal circumstances).
User could access the folder where the Access database is located through Windows Explorer.
Connection to SQL Server was made via Kerberos and not NTLM, in order to allow for the double-hop authentication.
The SQL Server SPN's had been set appropriately.
Despite all of these being in place, the only way the query would execute was to promote the Windows account to be a Domain Adminstrator.
This frustated us for quite a long time, until we found this Stack Overflow post.
The solution suggested, which worked for us, is to ensure that the user trying to execute the query, needs to have read/write access to the Temp directory of the user account, which the SQL Database Engine service is running as.
So, for example, if your SQL server is running under a user account named "SQLDatabaseEngineAccount", on the server, the folder should be located in:
We gave the developer read/write on that folder and everything worked as intended without the need for Domain Adminsitrator permissions.
I imagine we'll be back reading this the next time we setup a new SQL Server!