# # (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