#
# (C) 2014 Tenable Network Security, Inc.
#
# This script is released under the Tenable Subscription License and
# may not be used from within scripts released under another license
# without authorization from Tenable Network Security, Inc.
#
# See the following licenses for details:
#
# http://static.tenable.com/prod_docs/Nessus_5_SLA_and_Subscription_Agreement.pdf
# http://static.tenable.com/prod_docs/Subscription_Agreement.pdf
#
# @PROFESSIONALFEED@
#
# $Revision: 1.2 $
# $Date: 2014/05/23 15:20:52 $
#
# Description	        : This .audit file is written again the Center for Internet Security benchmark for
#			Microsoft SQL Server 2012 Database, version 1.1.0.
#
#			https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf
#
# NOTE			: Some queries in this .audit require site-specific data to be known to the query in order to function properly.
#		 		Please note the following queries and edit their values accordingly.
#
#<ui_metadata>
#<display_name>CIS Microsoft SQL Server 2012 Database v1.1.0 - Level 1 DB</display_name>
#</ui_metadata>

<check_type: "Database" db_type:"SQLServer" version:"1">
<group_policy: "CIS MS SQL Server 2012 Database DB Level 1 Audit">

# 2 Surface Area Reduction

<custom_item>
 type           : SQL_POLICY
 description	: "2.1 Set the 'Ad Hoc Distributed Queries' Server Configuration Option to 0"
 info			: "Ad Hoc Distributed Queries Allow users to query data and execute statements on external data sources. This functionality should be disabled."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request    : "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'ad hoc distributed queries'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Ad Hoc Distributed Queries", "0", "0"
</custom_item>

<custom_item>
 type           : SQL_POLICY
 description	: "2.2 Set the 'CLR Enabled' Server Configuration Option to 0"
 info			: "The CLR enabled option specifies whether user assemblies can be run by SQL Server."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'clr enabled', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request    : "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'clr enabled'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "clr enabled", "0", "0"
</custom_item>

<custom_item>
 type           : SQL_POLICY
 description	: "2.3 Set the 'Cross DB Ownership Chaining' Server Configuration Option to 0"
 info			: "This option allows controlling cross-database ownership chaining across all databases."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'Cross db ownership chaining', 0;
RECONFIGURE;
GO"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request    : "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Cross db ownership chaining'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Cross db ownership chaining", "0", "0"
</custom_item>

<custom_item>
 type			: SQL_POLICY
 description	: "2.4 Set the 'Database Mail XPs' Server Configuration Option to 0"
 info			: "Enables the generation and transmission of email messages from SQL Server."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
GO EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Database Mail XPs'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Database Mail XPs", "0", "0"
</custom_item>

<custom_item>
 type			: SQL_POLICY
 description	: "2.5 Set the 'Ole Automation Procedures' Server Configuration Option to 0"
 info			: "Extended stored procedures that allow SQL Server users to execute functions external to SQL Server."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Ole Automation Procedures'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Ole Automation Procedures", "0", "0"
</custom_item>

<custom_item>
 type			: SQL_POLICY
 description	: "2.6 Set the 'Remote Access' Server Configuration Option to 0"
 info			: "Enables the execution of local stored procedures on remote servers or remote stored procedures on local server."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Remote access', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Remote access'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Remote access", "0", "0"
</custom_item>

<custom_item>
 type           : SQL_POLICY
 description	: "2.7 Set the 'Remote Admin Connections' Server Configuration Option to 0"
 info			: "This option defines whether the Dedicated Admin Connection (DAC) is listening on localhost only or on the SQL Server IP address."
 solution		: "Run the following T-SQL command on clustered installations:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Remote admin connections', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request    : "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Remote admin connections'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Remote admin connections", "0", "0"
</custom_item>

<custom_item>
 type           : SQL_POLICY
 description	: "2.8 Set the 'Scan For Startup Procs' Server Configuration Option to 0"
 info			: "This option causes SQL Server to scan for and automatically run all stored procedures that are set to execute upon service startup."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Scan for startup procs', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request    : "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Scan for startup procs'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Scan for startup procs", "0", "0"
</custom_item>

<custom_item>
 type           : SQL_POLICY
 description	: "2.9 Set the 'Trustworthy' Database Property To Off"
 info			: "Scored"
 info			: "Level 1"
 info			: "The TRUSTWORTHY option allows database objects to access objects in other database under certain circumstances."
 solution		: "Execute the following statement against the database:
ALTER DATABASE <dbname>
SET TRUSTWORTHY OFF;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request    : "SELECT name FROM sys.databases WHERE is_trustworthy_on = 1 AND name != 'msdb' AND state = 0"
 sql_types      : POLICY_VARCHAR
 sql_expect     : NULL
</custom_item>

<custom_item>
 type 			: SQL_POLICY
 description	: "2.15 Set the 'xp_cmdshell' option to disabled"
 info			: "The xp_cmdshell procedure allows an authenticated SQL Server user to execute operating-system command shell commands and return results as rows within the SQL client."
 solution		: "Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Xp_cmdshell', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "EXECUTE sp_configure 'show advanced options',1; RECONFIGURE WITH OVERRIDE; EXECUTE sp_configure 'xp_cmdshell';"
 sql_types		: POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER, POLICY_INTEGER, POLICY_INTEGER
 sql_expect		: "xp_cmdshell", "0", "1", "0", "0"
</custom_item>

# 3 Authentication and Authorization

<custom_item>
 type			: SQL_POLICY
 description 	: "3.1 Set The 'Server Authentication' Property To Windows Authentication mode"
 info			: "Uses Windows Authentication to validate attempted connections."
 solution		: "Perform the following steps:
1. Open SQL Server Management Studio.
2. Open the Object Explorer tab and connect to the target database instance.
3. Right click the instance name and select Properties.
4. Select the Security page from the left menu.
5. Set the Server authentication setting to Windows Authentication mode."
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request 	: "xp_loginconfig 'login mode'"
 sql_types   	: POLICY_VARCHAR, POLICY_VARCHAR
 sql_expect  	: "login mode","Windows NT Authentication"
</custom_item>

<custom_item>
 type           : SQL_POLICY
 description	: "3.2 Revoke CONNECT permissions on the 'guest user' within all SQL Server databases excluding the master, msdb and tempdb"
 info			: "Removes the right of guest users to connect to SQL Server user databases."
 solution		: "Remediation:
The following code snippet revokes CONNECT permissions from the guest user in a database:
REVOKE CONNECT FROM guest;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request    : "EXEC sp_msforeachdb 'use ? SELECT DB_Name = ''?'',name FROM sys.sysusers WHERE name = ''guest'' AND hasdbaccess != ''0'' AND ''?'' != ''master'' AND ''?'' != ''msdb'' AND ''?'' != ''tempdb''';"
 sql_types      : POLICY_VARCHAR, POLICY_VARCHAR
 sql_expect     : NULL, NULL
</custom_item>

# 4 Password Policy

<custom_item>
 type 			: SQL_POLICY
 description	: "4.2 Set the 'CHECK_EXPIRATION' Option to ON for All SQL Authenticated Logins Within the Sysadmin Role"
 info			: "Applies the same password expiration policy used in Windows to passwords used inside SQL Server."
 solution		: "Remediation:
ALTER LOGIN [login_name] WITH CHECK_EXPIRATION = ON;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "SELECT SQLLoginName = sp.name, PasswordExpirationEnforced = CAST(sl.is_expiration_checked AS BIT) FROM sys.server_principals sp JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id WHERE sp.type_desc = 'SQL_LOGIN' AND sl.is_expiration_checked = '0';"
 sql_types		: POLICY_VARCHAR, POLICY_VARCHAR
 sql_expect		: NULL, NULL
</custom_item>

<custom_item>
 type 			: SQL_POLICY
 description	: "4.3 Set the 'CHECK_POLICY' Option to ON for All SQL Authenticated Logins"
 info			: "Applies the same password complexity policy used in Windows to passwords used inside SQL Server."
 solution		: "Remediation:
ALTER LOGIN [login_name] WITH CHECK_POLICY = ON;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "SELECT SQLLoginName = sp.name, PasswordPolicyEnforced = CAST(sl.is_policy_checked AS BIT) FROM sys.server_principals sp JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id WHERE sp.type_desc = 'SQL_LOGIN' AND sl.is_policy_checked = '0';"
 sql_types		: POLICY_VARCHAR, POLICY_VARCHAR
 sql_expect		: NULL, NULL
</custom_item>

# 5 SQL Server Agent Service Account Rights

<custom_item>
 type			: SQL_POLICY
 description	: "5.2 Set the 'Default Trace Enabled' Server Configuration Option to 1"
 info			: "The default trace provides audit logging of database activity including account creations, privilege elevation and execution of DBCC commands."
 solution		: "Remediation:
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Default trace enabled', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Default trace enabled'"
 sql_types      : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
 sql_expect     : "Default trace enabled","1","1"
</custom_item>

<custom_item>
 type			: SQL_POLICY
 description	: "5.3 Set 'Login Auditing' to Both failed and successful logins"
 info			: "Setting logs both successful and failed login SQL Server authentication attempts."
 solution		: "Remediation:
Perform the following steps to set the level of auditing:
1. Open SQL Server Management Studio.
2. Right click the target instance and select Properties and navigate to the Security tab.
3. Select the option Both failed and successful logins under the 'Login Auditing' section and click OK.
4. Restart the SQL Server instance."
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "XP_loginconfig 'audit level'"
 sql_types      : POLICY_VARCHAR, POLICY_VARCHAR
 sql_expect     : "audit level", "all"
</custom_item>

# 6 Integration Services service account rights

<custom_item>
 type			: SQL_POLICY
 description	: "6.2 Set the 'CLR Assembly Permission Set' to SAFE_ACCESS for All CLR Assemblies"
 info			: "Setting CLR Assembly Permission Sets to SAFE_ACCESS will prevent assemblies from accessing external system resources such as files, the network, environment variables, or the registry."
 solution		: "Remediation:
ALTER ASSEMBLY assembly_name WITH PERMISSION_SET = SAFE;"
 see_also		: "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2012_Database_Engine_Benchmark_v1.1.0.pdf"
 reference		: "Level|1S"
 sql_request	: "SELECT name, permission_set_desc FROM sys.assemblies where is_user_defined = 1;"
 sql_types      : POLICY_VARCHAR, POLICY_VARCHAR
 sql_expect     : NULL, NULL
</custom_item>

</group_policy>
</check_type>