MSSQL - Permission Issue on OPENROWSET

Got error when running OPENROWSET to read data from external Excel file:

SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0;Database=C:\TEMP\test.xlsx;HDR=YES;IMEX=1',
                 'SELECT * FROM [Excel$]') FC

Msg 7303, Level 16, State 1, Server MyServer, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

SOLUTION 1:

As it turns out when running the sql with any domain account the sql will need to have access to the folder “C:\Users\someaccount\AppData\Local\Temp” where the someaccount is the logon account used by SQL service.  Giving full control for users who need to run the sql to this folder on the SQL server solved the issue.  An easy way to debug which folder needs permission is to use PROCMON.EXE to monitor the file got rejected.

SOLUTION 2:

SET Temp=C:\Temp and SET Tmp=C:\Temp; this cause all user to use the given folder for temp files when running reports.

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

SOLUTION 3:

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREESESSIONCACHE

SOLUTION 4:

http://www.johnsoer.com/blog/?p=538