I have DB Links access from Instance 1 -> Instance 2. I can enable/disable xp_cmdshell in Instance 1 by directly executing the SQL query. But when I try with Openquery() I cannot enable it. I have sysadmin privilege in both Instances.
I got no result for below query (Becuase of 'SELECT 1', the error is suppressed):
$Query = "select * from openquery(""INSTANCE2"",'SELECT 1; EXECUTE(''master..sp_configure ''''show advanced option'''', 1; RECONFIGURE; master..sp_configure ''''xp_cmdshell'''', 1; RECONFIGURE;'') ')"
$cmd = New-Object System.Data.SqlClient.SqlCommand($Query,$conn)
$results = $cmd.ExecuteReader()
$results.Close()
When I tried the below query :
$Query = "select * from openquery(""INSTANCE2"",'EXEC master.dbo.sp_configure ''show advanced option'', 1; RECONFIGURE WITH OVERRIDE; EXEC master.dbo.sp_configure ''xp_cmdshell'', 0; RECONFIGURE; ')"
I got error:
Exception calling "ExecuteReader" with "0" argument(s): "The metadata could not be determined because the statement 'select name,
convert(int, minimum) as minimum,
convert(int, maximum) as maximum,
convert' in procedure 'sp_configure' is not compatible with the statement 'select duplicate_options = name
from sys.configurations
where lower(name collate Latin1_Genera' in procedure 'sp_configure'."
At ...
+ $results = $cmd.ExecuteReader()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Can't I enable xp_cmdshell using openquery() ? How can I achieve the same?
