The Analysis Services ad hoc data mining queries configuration option should be disabled if not required.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15183	DM6085-SQLServer9	SV-25466r1_rule	DCFA-1	Medium

Description
SQL Server Ad Hoc distributed queries allow specific functions (OPENROWSET and OPENDATASOURCE) to connect to remote systems without those remote systems being defined within database. Access to unauthorized systems could lead to unauthorized activity in remote systems that could compromise the local database.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-13793r1_chk )
If Analysis Services is not deployed on the local host, this check is Not a Finding. Note: To detect deployment, view Windows Services. If SQL Server Analysis Services ([instance name]) is not listed, then Analysis Services is not installed on this host. From the SQL Server Management Studio GUI: 1. Connect to the Analysis Services instance 2. Right click on the Analysis Services instance 3. Select Properties 4. View the value listed for DataMining \ AllowAdHocOpenRowsetQueries If value = 'true', this is a Finding. The AllowAdHocOpenRowsetQueries value may also be viewed in the Analysis Services configuration file, msmdsrv.ini under XML tag: [AllowAdHocOpenRowsetQueries] The configuration file may be found in the [install dir] \ MSSQL.[#] \ OLAP \ Config directory.

Check Text ( C-13793r1_chk )

If Analysis Services is not deployed on the local host, this check is Not a Finding.

Note: To detect deployment, view Windows Services. If SQL Server Analysis Services ([instance name]) is not listed, then Analysis Services is not installed on this host.

From the SQL Server Management Studio GUI:

1. Connect to the Analysis Services instance
2. Right click on the Analysis Services instance
3. Select Properties
4. View the value listed for DataMining \ AllowAdHocOpenRowsetQueries

If value = 'true', this is a Finding.

The AllowAdHocOpenRowsetQueries value may also be viewed in the Analysis Services configuration file, msmdsrv.ini under XML tag:

[AllowAdHocOpenRowsetQueries]

The configuration file may be found in the [install dir] \ MSSQL.[#] \ OLAP \ Config directory.

Fix Text (F-14813r1_fix)
Set value for AllowAdHocOpenRowsetQueries to 'false' From the SQL Server Management Studio GUI: 1. Connect to the Analysis Services instance 2. Right click on the Analysis Services instance 3. Select Properties 4. View the value listed for DataMining \ AllowAdHocOpenRowsetQueries 5. Select value = 'false' 6. Click OK