Ad hoc access to OLE DB provider has been denied

Using post SP2 SQL 7 (+ 2000 etc) attempting to access an OLEDB data source using OPENROWSET can produce the slightly spurious error:

Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

In usual Microsoft style the message doesn't really mean what it says. From SQL 7 SP2 onwards MS by default blocked ad hoc query access with OLEDB. As the message suggests you could setup a linked server but that can be a real pain. Alternatively if you need ad hoc access server wide you could turn on ad hoc access for the SQL server you are using, explained in MS speak here:

http://support.microsoft.com/default.aspx?kbid=266008

Ah, but it's not that simple. A little more witchcraft is required. The following registry settings can be used to enable ad hoc access:

REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers] "DisallowAdhocAccess"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0] "DisallowAdhocAccess"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDAORA] "DisallowAdhocAccess"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL] "DisallowAdhocAccess"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB] "DisallowAdhocAccess"=dword:00000000

Still not working?!

• Go to Enterprise Manager > Security > Linked Servers


• Right-click select "New Linked Server..."


• Select an OLEDB provider.


• Click "Provider Options".


• Check "Disallow adhoc access".


• Click OK


• Enter a name for the linked server.


• Click OK


• Delete the new linked server.


• Right-click select "New Linked Server..."


• Select an OLEDB provider.


• Click "Provider Options".


• Unheck "Disallow adhoc access".


• Click OK


• Enter a name for the linked server.


• Click OK


• Delete the new linked server.

No-one said it would be easy...