Friday, 27 February 2009

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...

2 comments:

SQL 2005/2008 Ad hoc access to OLE DB provider has been denied « Banshee Technologies said...

[...] have a previous post on enabling adhoc access for SQL 2000 (http://blog.bansheetechnologies.co.uk/2009/02/ad-hoc-access-to-ole-db-provider-has.html), SQL 2005 and 2008 are a little [...]

Albervan said...

Muito obrigado pela ajuda.

Thank's for help me.