If you get error Msg 15281, Level 16 in SQL Server, chances are you’re trying to run an ad hoc distributed query, but you haven’t enabled ad hoc distributed queries.

This can be fixed easily by enabling ad hoc distributed queries.

Example of the Error

Here’s an example of code that produces the error.

SELECT * FROM OPENROWSET(
    'SQLNCLI', 
    'Server=Homer;Trusted_Connection=yes;', 
    'SELECT * FROM Music.dbo.vAlbums');

Result:

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.

As the error message states, a system administrator can enable ad hoc distributed queries by using the sp_configure system stored procedure.

Solution

Run the following code to fix the issue.

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

The first part enables the show advanced options, which displays the sp_configure system stored procedure advanced options. If this isn’t enabled, you’d get another error (probably error msg 15123), stating that the configuration option either doesn’t exist or that it may be an advanced option.

The second part uses sp_configure to enable the ad hoc distributed queries option.

Once you’ve run that code, you should be able to run ad hoc distributed queries.

Hide Advanced Options

Once you’ve enabled ad hoc distributed queries, you should probably hide the show advanced options again.

EXEC sp_configure 'show advanced options', 0;  
GO
RECONFIGURE;  
GO