So we have an interesting issue where users are receiving the following error when attempting to query a view in SQL Server 2008 R2:
Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider
'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this
provider through a linked server.
Researching the issue, I've found more than a few articles listing making changes to the OPENROWSET parameters, registry value changes, AdHoc DIstributed Queries, etc. (Links to articles below)
http://www.johnsoer.com/blog/?p=538
We're still receiving the error after following the steps in the links above.
The caveat here is that when we created a test account and gave it sa – it ran just fine. My colleagues with admin access are also able to run the query without any issues.
This query is being fed from an excel file local to the server hosting this instance.
Anyone know of any other fixes for 2008 ?
Best Answer
I have just had the same problem yesterday, and here is how I was able solve it:
Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio),
the Registry does not have the DisallowadHocAccess = 0 key, and for some reason it is required to have it there.
After I added the key to the registry, I was able to run the Query with a non-admin user as well.