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?!
No-one said it would be easy...
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...
Comments
Thank's for help me.