I’m running SQL2008R2. I have a stored procedure that uses OPENROWSET and everything is fine… because I am an administrator.
A user with all the usual and appropriate permissions would run this query (via an Access fronted) and receive the error: “Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.40’ has been denied…”
The SQL provider is setup properly and ad hoc queries are enabled etc. but adding a DWORD registery key with a 0 value called “DisallowAdhocAccess” to the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0 got rid of this error. NOTE: MSSQLSERVER is my instance name.
The user was then presented with the error: “Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server…”
To fix this error, the executing user apparently must have access to the temp folder of the account running the instance on the SQL server. Let’s say my service account is called MYDOMAIN\MySvcSQL. On the SQL server, I granted the executing user’s group write access to “C:\Users\MySvcSQL\AppData\Local\Temp”.
And that solved hours of heartache!