Msg 15281: Ad Hoc Distributed Queries Blocked in SQL Server

Msg 15281 means SQL Server blocked your OPENROWSET query because a component is turned off. A reader hit it after following my earlier post about reading Excel data from SQL Server. It looks complicated, and the fix is short.

The Error

Here is the full message:

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.

What the Reader Ran

The reader emailed me after running a query like this one to read a sheet from an Excel file on a file share. The server, share, file and sheet names here are placeholders.

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
,'Excel 12.0;Database=\\YourServer\YourShare\YourFile.xlsx;HDR=YES;IMEX=1'
,'SELECT * FROM [Sheet1$]')

Why It Happens

The message says it plainly: the component is turned off as part of the server’s security configuration. OPENROWSET and OPENDATASOURCE both go through it, so both stop until an administrator turns it on. Nothing is wrong with your query.

How to Check

This read-only query shows the current setting. A value of 0 means the component is off.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = N'Ad Hoc Distributed Queries';

The Fix

This T-SQL turns the component on. The first call makes the advanced options visible, and the second enables the component.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO

Confirm It Worked

Run the check query again and look for a value of 1, then run your OPENROWSET query. After this change, the reader was able to pull the Excel data into SQL Server.

How to Prevent It

Treat this as a decision, not a nuisance. The component is off as a security measure, so turn it on only on the servers that need it. If you only need it for one import, set it back to 0 with the same sp_configure call when you finish.

I’ve covered this error before in an earlier post on Error 15281. If you’re choosing between approaches for regular access, read OPENROWSET vs Linked Servers: Querying Another Server Safely. If you have another way around this error, please share it in the comments.

Msg 15281 is not a broken query, it is a locked gate with a known latch.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.