SQL PowerShell to check and repair Ad-hoc distributed queries Vulnerability

Introduction

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. As the name suggests, ad-hoc distributed queries should only be used with infrequent access to data sources. For the datasources that has to be accessed frequently Linked server would be a better option.

An ad-hoc distributed query would look like this.

SELECT a.*  
FROM OPENROWSET('SQLNCLI', 'Server=SQLinfo1;Trusted_Connection=yes;',  
     'SELECT Department, Name, DepartmentID  
      FROM AdventureWorks2012.HumanResources.Department  
      ') AS a;  
GO 

By default, Microsoft turns off this option. However, we can enable the same using the below query.

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

The argument 1 at the end of the queries means ‘ON’, if you want to disable the ad-hoc distributed queries simply change the argument to 0.

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

In a strict and secured environment, we could see ad-hoc distributed queries referred to as a vulnerability and often turned off. However, some stored procedures may use this ability to reference OLEDB and pull data. Turning it off would cause the SP to fail.

SQL Statement to find the SPs using ad-hoc feature

The below T-SQL code can be used to check whether any stored procedures are using OPENROWSET,OPENDATASOURCE functions.

DECLARE @sql nvarchar(max);

SET @sql = '
select 
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
      Text  
    from syscomments 
    where text like ''%%OPENROWSET%%'' or text like ''%OPENDATASOURCE%''
';
CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL
);
DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)');

INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

SELECT *
FROM #results;

The output will show the SPs using the ad-hoc distributed queries option and we might have to look at the code and change it before disabling the ad-hoc distributed queries.

Using PowerShell to get data from multiple servers

By taking the help of PowerShell, we can run this query on multiple instances and return the output.

##Get the list of servers
$Servers =  Get-content -path " your file path "
## for each loop to execute the code in given set of servers
$Servers|%{
$_
## T-SQL command 
  $cmd = "
                                 
DECLARE @sql nvarchar(max);

SET @sql = '
select 
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
     Text  
    from syscomments 
    where text like ''%%OPENROWSET%%'' or text like ''%OPENDATASOURCE%''
';


CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL
);


DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)');


INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

SELECT @@servername Servername,databasename,count(Object_ID) count,[StoredProc]
FROM #results
group by DatabaseName,Object_ID,[StoredProc]
having Object_ID>0
"                               

Invoke-Sqlcmd -ServerInstance $_ -Query $cmd | select * |Format-Table -AutoSize

}

The output of the code should be like:

Summary

Ad-hoc distributed queries can be termed as a critical vulnerability in secured environments. Consider other ways such as linked servers to get the data because they are a lot safer and robust.