SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ – Error 15281

By default, 'Ad Hoc Distributed Queries' setting is disabled in SQL Server. If you try to connect to remote data sources using OPENROWSET and OPENDATASOURCE functions without having 'Ad Hoc Distributed Queries' server setting enabled, you will get the following 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.

To check if 'Ad Hoc Distributed Queries' setting is enabled, run:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure
GO

ad_hoc_distributed_queries_disabled

To enable 'Ad Hoc Distributed Queries':

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

Now if you check again, 'Ad Hoc Distributed Queries' setting should be enabled:

ad_hoc_distributed_queries_enabled

To disable'Ad Hoc Distributed Queries':

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

If the post helped you, please share it:
Pin It

2 comments to SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ – Error 15281

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">