Skip to main content

SQL 2005/2008 Ad hoc access to OLE DB provider has been denied

By
I 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 different.
SQL 2005/2008 returns the following:
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
To enable OpenRowset and OpenDatasource:
  • Launch the "SQL Server 2005 Surface Area Configuration" tool.
  • Click "Surface Area Configuration for Features"
  • Select "Server Name > Database Engine > Ad Hox Remote Queries"
  • Tick "Enable OPENROWSET and OPENDATASOURCE" support.
  • Click OK

After completing the above steps you might see the following error when you run the query again:

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

To resolve this:

  • Open regedit
  • Expand "HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers" – depending on how many instances you have installed you may have MSSQL.1, MSSQL.2 etc – the instance name is shown as the (Default) value under each branch – check this to ensure you are editing the settings for the correct instance.
  • If it isn't already present add a new DWord setting under MSDASQL with a name of DisallowAdhocAccess and value of 0.
Labels:

Comments

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 Website …
2 comments
Read more

John Hamblin Furniture Restorer - New Website

By
I was asked to produce a new website for local furniture restorer and cabinet maker John Hamblin. Having struggled with another provider who failed to deliver a finished website I was asked to take the website on and produce a simple site that communicates their values of high quality workmanship and excellent customer service.

The new website is live and can be viewed here:
http://www.johnhamblinfurniturerestorer.co.uk/



Post a comment
Read more

Compress and de-compress strings in C#

By
A collection of resources on compressing and encrypting strings in C#.
Microsoft documentation on the System.IO.Compression.GZipStream class:
http://msdn.microsoft.com/en-us/library/system.io.compression.gzipstream.aspx
Helpful example class utilising GZip:
http://www.csharphelp.com/2007/09/compress-and-decompress-strings-in-c/
The GZipStream class is predominantly used for file compression but can be used efficiently for compressing strings of 300-400 characters or more. Below 300 characters there isn’t any measurable gain from the compression and for particularly short strings the compressed version may in fact be larger.
Class provided by C Sharp Help:
using System.IO.Compression;
using System.Text;
using System.IO;
public static string Compress(string text)
{
    byte[] buffer = Encoding.UTF8.GetBytes(text);
    MemoryStream ms = new MemoryStream();
    using (GZipStream zip = new GZipStream(ms, CompressionMode.Compress, true))����…
Post a comment
Read more