Help with SQL Server Error "ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'"

Question

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

http://www.remotedbaexperts.com/Blog/dba_tips/2010/09/ad-hoc-access-to-ole-db-provider-microsoft-ace-oledb-12-0-has-been-denied/

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 ?

Answer 1

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.

Answer 2

1. Determine if "Disallow adhoc access" is enabled for your provider. This can be found in SQL Management Studio via the following navigation path:

2. Server Objects/Linked Servers/Providers/Microsoft.ACE.OLEDB.12.0

3. Right click the "Microsoft.ACE.OLEDB.12.0" provider and select "Properties" from the context menu.

4. In the pop-up window, make sure that the "Disallow adhoc access" checkbox is cleared.

Alternatively, you can just set it to disabled by using the following SQL:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE

Next, verify that the Registry key is set. In Regedit, navigate as follows:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL<major version>_<minor version>.<instance name>\Providers\Microsoft.ACE.OLEDB.12.0]
"DisallowAdhocAccess"=dword:00000000

Note: You may not have an instance name if you're using the default instance.

Another Note: You can copy and paste that into a .reg file to set the key.

Key Point: From my experience, you only need the registry key if the user accessing the database is not an admin- or owner-type user. So, high security environments will need this registry key.

Answer 3

For me, make sure you have a registry key named "DisallowAdhocAccess" value =0 in [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL_.\Providers\Microsoft.ACE.OLEDB.12.0] If it is not exist, create it.