Menu
There are 3 basic security concerns with Ad Hoc Distributed Queries, and each has it's own level of risk: If the allowed provider on the remote server has a bug in it (such as a buffer overflow) that compromises your security. This is probably the largest risk to your SQL Server environment.
What Does Ad Hoc Query Mean? In SQL, an ad hoc query is a loosely typed command/query whose value depends upon some variable. Each time the command is executed, the result is different, depending on the value of the variable. It cannot be predetermined and usually comes under dynamic programming SQL query.
Step 1: First check the config_value of the "allow updates" configuration option. If its set to 1, change this value to 0, which is the default value. Step 2: As you can see SQL Server is not running with the default value.
The following command may help you..
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
You may check the following command
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO --Added
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks2012.HumanResources.Department
ORDER BY GroupName, Name') AS a;
GO
Or this documentation link
If ad hoc updates to system catalog is "not supported", or if you get a "Msg 5808" then you will need to configure with override like this:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE with override
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With