Forum Discussion
Microsoft.ACE.OLEDB.16.0 - OPENRowSet not working from remote machine or agent
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 o...
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 - Microsoft Tech Community.
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