Archive
SQL Error – SQL Server blocked access to STATEMENT ‘OpenRowset/ OpenDatasource’ of component ‘Ad Hoc Distributed Queries’
Today while executing a Stored Procedure which internally executes a remote query via Linked Server, I got following error:
Msg 50000, Level 16, State 127, Procedure spExecureRemoteQuery, Line 50
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.
The above clearly means that the “Ad Hoc Distributed Queries” option is disabled on the database instance.
–> Run below Query to check if this property is disabled, if will show you 0 value under Config & Run value columns:
sp_configure 'show advanced options', 1 GO EXEC sp_configure GO
–> To enable it run the below Query:
sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE WITH OverRide GO EXEC sp_configure GO
…the value under Config & Run value columns shows 1, means that now the Property is enabled and you can execute your Remote/Linked-Server queries.
- Spark: The Definitive Guide
- Learning Spark 2Ed
SQL Server - Certification exam books:
- TSQL: 70-761 , 70-762
- DBA: 70-764 , 70-765
- DW/BI: 70-767
SQL Tags
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |
Categories
- Big Data (14)
- Apache Spark (5)
- Databricks (3)
- Hadoop (5)
- DBA Stuff (34)
- Interview Q (8)
- Microsoft Azure (25)
- MS BI (8)
- Analysis Services (1)
- DW and BI (1)
- Integration Services (1)
- Power BI (4)
- Reporting Services (1)
- Tabular Model (1)
- Others (196)
- Blockchain (1)
- Certifications (9)
- Cloud Computing (1)
- Cpp (32)
- Cpp Graphics (21)
- Excel (10)
- Informatica (5)
- Java (56)
- Linux (8)
- Microsoft (14)
- MS.net (2)
- Oracle (1)
- Powershell (3)
- Python (1)
- Reviews (5)
- Security (1)
- Spark SQL (2)
- SQL Server (7)
- SQLwithManoj (11)
- Uncategorized (7)
- VBA Macro (1)
- Visual Studio (1)
- Windows (6)
- SQL Server Conferences (1)
- SQL Server Internals (85)
- Datatypes (5)
- DB Concepts (28)
- Indexes (11)
- JOINS (4)
- ML Python (2)
- SQL DB Engine (8)
- SQL Server Migration (1)
- SQL Server Upgrade (1)
- Stored Procedures (11)
- tempdb (3)
- XML (16)
- SQL Server Questions (41)
- SQL Server Versions (135)
- SQL Azure (4)
- SQL Server 2005 (7)
- SQL Server 2008 (5)
- SQL Server 2012 (33)
- SQL Server 2014 (18)
- SQL Server 2016 (49)
- SQL Server 2017 (20)
- SQL Server 2019 (2)
- SQL Server Express (1)
- T SQL (182)
- Differences (31)
- JSON (6)
- Misconception (7)
- Optimization Performance (13)
- SQL Basics (15)
- SQL Built-in Functions (6)
- SQL Errors (44)
- SQL Tips (67)
- SQL Trivia (9)
Archives
Top Posts
- SQL Error - The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE
- Getting started with SQL Server 2014 | Download and Install Free (Express) or Full version
- SQL DBA - Windows could not start the SQL Server... refer to service-specific error code 17051 - SQL Server Evaluation period has expired
- Reading JSON string with Nested array of elements | SQL Server 2016 - Part 3
- SQL Server 2016 RTM full and final version available - Download it now !!!
- SQL Error - SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs'
- SQL Error - The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
- SQL Error - Windows could not start the SQL Server (MSSQLSERVER) on Local Computer (moved Master DB)
- Download & Install SQL Server Management Studio (SSMS) 2016 (decoupled from SQL Server engine setup)
- What is Lambda Architecture? and what Azure offers with its new Cosmos DB?
Blog Stats, since Aug 2010
- 4,446,929 hits
StatCounter …since April 2012

Leisure blog: Creek & Trails
- I got full refund of my flight tickets during COVID lockdown (AirIndia via MakeMyTrip)
- YouTube – Your Google Ads account was cancelled due to no spend
- YouTube latest update on its YPP (YouTube Partner Program) which may affect your channel
- Starting your own blog !!!
- How to file ITR (Income Tax Return) online AY 2017-18 (for simple salaried)
Disclaimer
This is my personal blog site.
The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet.
My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.