#
# (C) 2012-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.5 $
# $Date: 2014/07/30 21:18:18 $
#
# Description : This .audit file is written again the Center for Internet
# Security benchmark for Microsoft SQL Server 2008 R2 Database, version 1.2.0.
# https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.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.
#
#
#
#CIS Microsoft SQL Server 2008 R2 Database Level 1 Database Audit
#
#
# 2 Surface Area Reduction
type : SQL_POLICY
description : "2.1 Set the 'Ad Hoc Distributed Queries' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "Ad Hoc Distributed Queries Allow users to query data and execute statements on external data sources. This functionality should be"
info : "disabled."
info : "This feature can be used to remotely access and exploit vulnerabilities on remote SQL Server instances and to run unsafe Visual Basic for"
info : "Application functions."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 11"
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"
type : SQL_POLICY
description : "2.2 Set the 'CLR Enabled' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "The CLR enabled option specifies whether user assemblies can be run by SQL Server."
info : "Enabling use of CLR assemblies widens the attack surface of SQL Server and puts it at risk from both inadvertent and malicious assemblies."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 12"
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"
type : SQL_POLICY
description : "2.3 Set the 'Cross DB Ownership Chaining' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "This option allows controlling cross-database ownership chaining across all databases."
info : "This option allows a member of the db_owner role in a database to gain access to objects owned by a login in any other database, causing"
info : "an unnecessary information disclosure."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 13"
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"
type : SQL_POLICY
description : "2.4 Set the 'Database Mail XPs' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "Enables the generation and transmission of email messages from SQL Server."
info : "Disabling Database Mail reduces the SQL Server surface, eliminates a DOS attack vector and channel to exfiltrate data from the database"
info : "server to a remote host."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 14"
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"
type : SQL_POLICY
description : "2.5 Set the 'Ole Automation Procedures' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "Extended stored procedures that allow SQL Server users to execute functions external to SQL Server."
info : "Enabling this option will increase the attack surface of SQL Server and allow users to execute functions in the security context of SQL"
info : "Server."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 15"
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"
type : SQL_POLICY
description : "2.6 Set the 'Remote Access' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "Enables the execution of local stored procedures on remote servers or remote stored procedures on local server."
info : "Functionality can be abused to launch a Denial-of-Service (DoS) attack on remote servers by off-loading query processing to a target."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 16"
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"
type : SQL_POLICY
description : "2.7 Set the 'Remote Admin Connections' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "This option defines whether the Dedicated Admin Connection (DAC) is listening on localhost only or on the SQL Server IP address. If it's"
info : "a clustered installation, it must be enabled as a clustered SQL Server cannot bind to localhost and DAC will be unavailable otherwise."
info : "Enable it for clustered installations. Disable it for standalone installations where not required."
info : "The Dedicated Admin Connection is a powerful database feature that allows connected users to circumvent the SQL Server abstraction layer"
info : "and have direct access to several system tables which can be used to conduct malicious activities. This feature should be restricted to"
info : "local administration only to reduce exposure."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 17"
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"
type : SQL_POLICY
description : "2.8 Set the 'Scan For Startup Procs' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "This option causes SQL Server to scan for and automatically run all stored procedures that are set to execute upon service startup."
info : "This is a defense in depth measure to reduce the threat of an entity leveraging these facilities for malicious purposes."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 18"
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"
type : SQL_POLICY
description : "2.9 Set the 'SQL Mail XPs' Server Configuration Option to 0"
info : "Scored"
info : "Level 1"
info : "SQL Mail provides a mechanism to send, receive, delete, and process e-mail messages using SQL Server."
info : "SQL Mail, which is deprecated in favor of Database Mail and if disabled reduces the SQL Server surface, eliminates a DOS attack vector and"
info : "channel to exfiltrate data from the database server to a remote host."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 19"
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 = 'SQL Mail XPs'"
sql_types : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
sql_expect : "SQL Mail XPs", "0", "0"
type : SQL_POLICY
description : "2.10 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."
info : "Provides protection from malicious CLR assemblies or extended procedures."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 20"
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
type : SQL_POLICY
description : "2.16 Set the 'xp_cmdshell' option to disabled"
info : "Scored"
info : "Level 1"
info : "The xp_cmdshell procedure allows an authenticated SQL Server user to execute operating-system command shell commands and return results"
info : "as rows within the SQL client."
info : "xp_cmdshell is commonly used by attackers to read or write data to/from the underlying Operating System of a database server."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 27"
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"
# 3 Authentication and Authorization
type : SQL_POLICY
description : "3.1 Set The 'Server Authentication' Property To Windows Authentication mode"
info : "Not Scored"
info : "Level 1"
info : "Uses Windows Authentication to validate attempted connections."
info : "Windows provides a more robust authentication mechanism than SQL Server authentication."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 38"
sql_request : "xp_loginconfig 'login mode'"
sql_types : POLICY_VARCHAR, POLICY_VARCHAR
sql_expect : "login mode","Windows NT Authentication"
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 : "Scored"
info : "Level 1"
info : "Removes the right of guest users to connect to SQL Server user databases."
info : "A login assumes the identity of the guest user when a login has access to SQL Server but does not have access to a database through its"
info : "own account and the database has a guest user account. Revoking the connect permission for the guest user will ensure that a login is not"
info : "able to access database information without explicit access to do so."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 39"
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
# 4 Password Policy
type : SQL_POLICY
description : "4.2 Set the 'CHECK_EXPIRATION' Option to ON For All SQL Authenticated Logins Within the Sysadmin Role"
info : "Not Scored"
info : "Level 1"
info : "Applies the same password expiration policy used in Windows to passwords used inside SQL Server."
info : "Ensuring SQL logins comply with the secure password policy applied by the Windows Server Benchmark will ensure the passwords for SQL"
info : "logins with Sysadmin privileges are changed on a frequent basis to help prevent compromise via a brute force attack."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 42"
sql_request : "SELECT SQLLoginName = sp.name, PasswordExpirationEnforced = CAST(sl.is_expiration_checked as int) 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_INTEGER
sql_expect : NULL, NULL
type : SQL_POLICY
description : "4.3 Set the 'CHECK_POLICY' Option To ON For All SQL Authenticated Logins"
info : "Not Scored"
info : "Level 1"
info : "Applies the same password complexity policy used in Windows to passwords used inside SQL Server."
info : "Ensuring SQL logins comply with the secure password policy applied by the the Windows Server Benchmark will ensure SQL logins are not"
info : "blank and cannot be easily compromised via brute force attack."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 43"
sql_request : "SELECT SQLLoginName = sp.name, PasswordPolicyEnforced = CAST(sl.is_policy_checked as int) 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_INTEGER
sql_expect : NULL, NULL
# 5 SQL Server Agent Service Account Rights
type : SQL_POLICY
description : "5.2 Set the 'Default Trace Enabled' Server Configuration Option to 1"
info : "Scored"
info : "Level 1"
info : "The default trace provides audit logging of database activity including account creations, privilege elevation and execution of DBCC"
info : "commands."
info : "Default trace provides valuable audit information regarding security-related activities on the server."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 45"
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"
type : SQL_POLICY
description : "5.3 Set 'Login Auditing' to Both failed and successful logins"
info : "Not Scored"
info : "Level 1"
info : "Setting logs both successful and failed login SQL Server authentication attempts."
info : "Logging successful and failed logins provides key information that can be used to detect\confirm password guessing attacks. Further,"
info : "logging successful login attempts can be used to confirm server access during a forensic investigations."
info : "ref: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.2.0.pdf pg. 46"
sql_request : "XP_loginconfig 'audit level'"
sql_types : POLICY_VARCHAR, POLICY_VARCHAR
sql_expect : "audit level", "all"
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.0.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