Microsoft.ACE.OLEDB.16.0 - OPENRowSet not working from remote machine or agent

[Pete673]

Hi guys

New to forum here so treat me gently.

We have been using OPENROWSET with Microsoft.ACE.OLEDB.16.0 to import excel data to SQL server for some time and when logged into the physical server on which the instance is installed everything works fine.

However we are unable to run the OPENROWSET query:

a. Through SQL Server agent

b. Via SSMS from a laptop/PC that is connecting to the instance vis SSMS with Windows login.

This means that overnight agent jobs can't be set up to manage excel files which have changed during the day. Also have to access server via remote desk top to the physical server where the instance is.
None of this is ideal!

Error message is:

Msg 7399, Level 16, State 1, Line 5

The OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 5
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".

All the googling on the issue talks about getting it connected and working. We have done that on the physical server, but I can't find any help on running a query remotely or through the agent.
I guess the issue centres around permissions. When working on the server we are using {SERVERNAME]\Administrator whereas remotely we are logging in using Windows user name. No Idea how the agent logs in! 

I have tried EXECUTE AS but doesn't seem to work either even when executing as Administrator on physical server.

If anyone can offer some help here, I'd be really grateful

Thanks

Pete

[#2]

We have done some work on this over the last few days and found a workaround that will work for us.

Firstly EXECUTE AS is not a thing since this doesn't work wit distributed/ad hoc queries.

The inspiration came from this post: Error: 17053 -UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered, messages ....

Having changed the registry entry on the server as described above everything started to take shape.

A stored procedure using ACE can now be run remotely and by SQL Agent. The only proviso is the excel files being accessed must be in a folder on the physical server where the SQL instance sits and permissions given to users to access that folder.

Hope this was helpful

Pete