On some servers when you try to load an Excel file using OpenRowset, you will get this delightful error:
Msg 15281, Level 16, State 1, Line 1
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’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
This scary looking error is actually due to a default setting in SQL Server for security reasons. This protects your server from any rogue activity.
However sometimes you really need to load a file using OPENROWSET as you may not have access to SSIS.
The error message was very helpful and the solution was found on Books Online, follow this link to see more information.
We need to enable on the server ‘ad hoc distributed queries’. This is accomplished using the commands below.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
The Microsoft Books Online has this important note: Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider. SQL Server administrators should enable this feature for providers that are safe to be accessed by any local login.
Now I try and run my load again and it succeeds.
I query the table and see all of the rows got loaded.
Some people recommend you change the settings back after you do the load (import), but if I am the only person that has access to the server I will leave them enabled.
The command below will disable the ability to run the OPENROWSET query.
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 0
RECONFIGURE
GO
Jim Salasek's SQL Server Blog 