Wednesday, 14 April 2010

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

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.

No comments: