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