Skip to main content

Ad hoc access to OLE DB provider has been denied

By
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...
Labels:

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 [...]
13 June 2010 at 00:03
Albervan said…
Muito obrigado pela ajuda.

Thank's for help me.
24 July 2013 at 15:51
Post a Comment

Popular posts from this blog

Take website screenshot using ASP.NET

By
Utilising a hidden web browser control it is possible to take a screenshot of any website. The code shown below is based on an article at plentyofcode.com  (sorry the site now appears to be offline May 2012) but I have translated it from VB.NET to C# and will work in .NET so theoretically for any Windows or ASP.NET web project. using System; using System.Drawing; using System.Drawing.Imaging; using System.Windows.Forms; using System.Diagnostics; namespace WebsiteScreenshot { public class GetImage { private int s_Height; private int s_Width; private int f_Height; private int f_Width; private string myURL; public int ScreenHeight { get { return s_Height; } set { s_Height = value; } } public int ScreenWidth { get { return s_Width; } set { s_Width = value; } } public int ImageWidth { get { return f_Width; } set { f_Width = value; } } public int ImageHeight { get { return f_Height; } set { f_Height = value; } } public string Websit
2 comments
Read more

Recover from corrupt SQL LDF transaction log file

By
Another favourite this month. A fault on one of our client's servers caused it to restart once every 10 minutes for 2 hours - the result was a corrupt LDF transaction log file for the main application database. It is surprisingly simple to recover from this situation: 1. Stop the SQL Server service 2. Copy the affected database (both LDF and MDF files) out of the main data folder. 3. Restart the SQL Server service 4. Create a new database of the same name and location as the database affected in step 2 - it is critical the filenames and paths are identical. 5. Stop the SQL Server service. 6. Copy the original MDF file (copied in step 2) in to replace the new MDF file created in step 4 7. Start the SQL Server service - the database will show as being suspect. 8. Now you need to recover the database, working from Query Analyser or SQL Management Studio: Use master go sp_configure 'allow updates', 1 reconfigure with override go select status from sysdatabases where name = '
2 comments
Read more