# (C) 2015-2016 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_6_SLA_and_Subscription_Agreement.pdf
#
# @PROFESSIONALFEED@
# $Revision: 1.6 $
# $Date: Wed Aug 10 17:09:25 2016 -0400 $
#
# Description	: This .audit is designed against the CIS Security Configuration Benchmark For
#				Microsoft SQL Server 2014 Version 1.1.0 10-09-2015.
#
# Ref			: https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_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.
#
#				1.1 Install the Latest SQL Server Service Packs and Hotfixes
#				2.13 Disable the 'sa' Login Account
#
#<ui_metadata>
#<display_name>CIS SQL Server 2014 Database L1 DB v1.1.0</display_name>
#<spec>
#  <type>CIS</type>
#  <name>SQL Server 2014 Database L1 DB</name>
#  <version>1.1.0</version>
#  <link>https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf</link>
#</spec>
#<labels>database,cis,microsoft,mssql,mssql_2014</labels>
#<variables>
#<variable>
#<name>SERVICE_PACK</name>
#<default>SP1</default>
#<description>Service Pack Level</description>
#<info>This is the required service pack level.</info>
#</variable>
#<variable>
#<name>VERSION</name>
#<default>12.0.4050.0</default>
#<description>SQL server Version</description>
#<info>This is the required SQL Server version.</info>
#</variable>
#<variable>
#<name>SA_ACCOUNT</name>
#<default>sa</default>
#<description>sa Account</description>
#<info>This is the name of the 'sa' account.</info>
#</variable>
#</variables>
#</ui_metadata>

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

  <custom_item>
   type         : SQL_POLICY
   description  : "2.1 Set the 'Ad Hoc Distributed Queries' Server Configuration Option to 0"
   info         : "This feature can be used to remotely access and exploit vulnerabilities on remote SQL Server instances and to run unsafe Visual Basic for Application functions."
   reference    : "800-53|CM-7,CIP|007-6-R1,CSCv6|9.1,PCI-DSSv3.1|2.2.2,PCI-DSSv3.1|2.2.3,800-171|3.4.6,800-171|3.4.7,CSF|PR.IP-1,CSF|PR.PT-3,800-53|CM-2,800-53|CM-6,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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;"
   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         : "Enabling use of CLR assemblies widens the attack surface of SQL Server and puts it at risk from both inadvertent and malicious assemblies."
   reference    : "800-53|CM-7,CIP|007-6-R1,CSCv6|9.1,PCI-DSSv3.1|2.2.2,PCI-DSSv3.1|2.2.3,800-171|3.4.6,800-171|3.4.7,CSF|PR.IP-1,CSF|PR.PT-3,800-53|CM-2,800-53|CM-6,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Run the following T-SQL command:
EXECUTE sp_configure 'clr enabled', 0;
RECONFIGURE;"
   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         : "When enabled, 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 an unnecessary information disclosure. When required, cross-database ownership chaining should only be enabled for the specific databases requiring it instead of at the instance level for all databases by using the ALTER DATABASE &lt;dbname&gt; SET DB_CHAINING ON command. This database option may not be changed on the master, model, or tempdb system databases."
   reference    : "800-53|CM-7,CIP|007-6-R1,CSCv6|9.1,PCI-DSSv3.1|2.2.2,PCI-DSSv3.1|2.2.3,800-171|3.4.6,800-171|3.4.7,CSF|PR.IP-1,CSF|PR.PT-3,800-53|CM-2,800-53|CM-6,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Run the following T-SQL command:
EXECUTE sp_configure 'Cross db ownership chaining', 0;
RECONFIGURE;
GO"
   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         : "Disabling Database Mail reduces the SQL Server surface, eliminates a DOS attack vector and channel to exfiltrate data from the database server to a remote host."
   reference    : "800-53|CM-6,CIP|007-6-R1,CSCv6|3.1,PCI-DSSv3.1|2.2.4,800-171|3.4.2,CSF|PR.IP-1,800-53|CM-2,800-53|CM-7,CSF|PR.PT-3,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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;"
   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         : "Enabling this option will increase the attack surface of SQL Server and allow users to execute functions in the security context of SQL Server."
   reference    : "800-53|CM-6,CIP|007-6-R1,CSCv6|3.1,PCI-DSSv3.1|2.2.4,800-171|3.4.2,CSF|PR.IP-1,800-53|CM-2,800-53|CM-7,CSF|PR.PT-3,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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;"
   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         : "Functionality can be abused to launch a Denial-of-Service (DoS) attack on remote servers by off-loading query processing to a target."
   reference    : "800-53|AC-17,CIP|005-5-R2,800-171|3.1.1,800-171|3.1.2,CSF|PR.AC-3,CSF|PR.PT-4,800-53|CM-2,800-53|CM-6,800-53|CM-7,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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;"
   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>

  <if>
    <condition type : "AND">
      <custom_item>
       type         : SQL_POLICY
       description  : "2.7 Set the 'Remote Admin Connections' Server Configuration Option to 0"
       sql_request  : "SELECT SERVERPROPERTY('IsClustered') AS [isClustered]"
       sql_types    : POLICY_INTEGER
       sql_expect   : "0"
      </custom_item>
    </condition>
    <then>
      <custom_item>
       type         : SQL_POLICY
       description  : "2.7 Set the 'Remote Admin Connections' Server Configuration Option to 0"
       info         : "The Dedicated Administrator Connection (DAC) lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. In a cluster scenario the administrator may not actually be logged on to the same node that is currently hosting the SQL Server instance and thus is considered 'remote'. Therefore this setting should usually be enabled (1) for SQL Server failover clusters; otherwise it should be disabled (0) which is the default."
       reference    : "800-53|AC-17,CIP|005-5-R2,800-171|3.1.1,800-171|3.1.2,CSF|PR.AC-3,CSF|PR.PT-4,LEVEL|1S"
       see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
       solution     : "Run the following T-SQL command on non-clustered installations:
    EXECUTE sp_configure 'Remote admin connections', 0;
    RECONFIGURE;
    GO"
       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' AND SERVERPROPERTY('IsClustered') = 0"
       sql_types    : POLICY_VARCHAR, POLICY_INTEGER, POLICY_INTEGER
       sql_expect   : "Remote admin connections", "0", "0"
      </custom_item>
    </then>
    <else>
      <report type  : "PASSED" >
       description  : "2.7 Set the 'Remote Admin Connections' Server Configuration Option to 0"
       info         : "The Dedicated Administrator Connection (DAC) lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. In a cluster scenario the administrator may not actually be logged on to the same node that is currently hosting the SQL Server instance and thus is considered 'remote'. Therefore this setting should usually be enabled (1) for SQL Server failover clusters; otherwise it should be disabled (0) which is the default."
       reference    : "LEVEL|1S"
       see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
       solution     : "Run the following T-SQL command on non-clustered installations:
EXECUTE sp_configure 'Remote admin connections', 0;
RECONFIGURE;
GO"
      </report>
    </else>
  </if>

  <custom_item>
   type         : SQL_POLICY
   description  : "2.8 Set the 'Scan For Startup Procs' Server Configuration Option to 0"
   info         : "Enforcing this control reduces the threat of an entity leveraging these facilities for malicious purposes."
   reference    : "800-53|CM-7,CSCv6|8.4,800-171|3.4.7,CSF|PR.IP-1,CSF|PR.PT-3,800-53|CM-2,800-53|CM-6,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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;"
   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         : "Provides protection from malicious CLR assemblies or extended procedures."
   reference    : "800-53|CM-6,CSCv6|3.1,PCI-DSSv3.1|2.2.4,800-171|3.4.2,CSF|PR.IP-1,800-53|CM-2,800-53|CM-7,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Execute the following statement against the database:
ALTER DATABASE <dbname>
SET TRUSTWORTHY OFF;"
   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' Server Configuration Option to 0"
   info         : "xp_cmdshell is commonly used by attackers to read or write data to/from the underlying Operating System of a database server."
   reference    : "800-53|CM-7,CIP|007-6-R1,CSCv6|9.1,PCI-DSSv3.1|2.2.2,PCI-DSSv3.1|2.2.3,800-171|3.4.6,800-171|3.4.7,CSF|PR.IP-1,CSF|PR.PT-3,800-53|CM-2,800-53|CM-6,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.5,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
 solution       : "Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXECUTE sp_configure 'xp_cmdshell', 0;
RECONFIGURE WITH OVERRIDE;"
   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>

  <custom_item>
   type         : SQL_POLICY
   description  : "2.16 Set AUTO_CLOSE OFF on contained databases"
   info         : "Opening contained databases to authenticate a user consumes additional server resources and may contribute to a denial of service."
   reference    : "800-53|SC-5,CSF|DE.CM-1,CSF|PR.DS-4,LEVEL|1S"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Perform the following to establish the prescribed state for a given contained database:
ALTER DATABASE <database_name>; SET AUTO_CLOSE OFF;"
   sql_request  : "SELECT name, containment, is_auto_close_on FROM sys.databases WHERE containment <> 0 and is_auto_close_on = 1;"
   sql_types    : POLICY_VARCHAR, POLICY_VARCHAR, POLICY_VARCHAR
   sql_expect   : NULL, NULL, NULL
  </custom_item>

  <custom_item>
   type         : SQL_POLICY
   description  : "2.17 Verify No Login Has the Name 'sa'"
   info         : "The 'sa' login (e.g. principal) is a widely known and often widely used SQL Server account. Therefore, there should not be a login called 'sa' when the original 'sa' login (sid 0x01) has been renamed. Enforcing this control reduces the probability of an attacker executing brute force attacks against a well-known principal."
   reference    : "800-53|AC-2,800-171|3.1.1,CSF|DE.CM-1,CSF|DE.CM-3,CSF|PR.AC-1,CSF|PR.AC-4,LEVEL|1S"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "DROP LOGIN sa;"
   sql_request  : "SELECT sid, name FROM sys.server_principals WHERE name = 'sa' AND sid <> 0x01;"
   sql_types    : POLICY_VARCHAR, POLICY_VARCHAR
   sql_expect   : NULL, NULL
  </custom_item>


  <custom_item>
   type         : SQL_POLICY
   description  : "3.1 Set The 'Server Authentication' Property To Windows Authentication mode"
   info         : "Windows provides a more robust authentication mechanism than SQL Server authentication."
   reference    : "800-53|IA-2,800-171|3.5.1,CSF|PR.AC-1,800-53|AC-2,HIPAA|164.308(a)(5)(ii)(D),HIPAA|164.312(a)(2)(i),HIPAA|164.312(d),LEVEL|1S,PCI-DSS|8.1.1,SANS-CSC|16-12"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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."
   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         : "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 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 able to access database information without explicit access to do so."
   reference    : "800-53|AC-6,PCI-DSSv3.1|7.1.2,800-171|3.1.5,CSF|PR.AC-4,CSF|PR.DS-5,800-53|AC-17,800-53|AC-22,800-53|AC-4,CSF|PR.AC-1,HIPAA|164.308(a)(3)(i),HIPAA|164.312(a)(1),LEVEL|1S,PCI-DSS|8.5,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Remediation:
The following code snippet revokes CONNECT permissions from the guest user in a database:
USE [database_name];
GO
REVOKE CONNECT FROM guest;"
   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>

  <custom_item>
   type         : SQL_POLICY
   description  : "3.3 Drop Orphaned Users From SQL Server Databases"
   info         : "Orphan users should be removed to avoid potential misuse of those broken users in any way."
   reference    : "800-53|AC-2,CIP|004-6-R5,CSCv6|16.1,CSCv6|16.6,PCI-DSSv3.1|8.1.4,CSF|PR.AC-1,CSF|PR.AC-4,ISO/IEC-27001|A.9.2.6,HIPAA|164.308(a)(5)(ii)(D),HIPAA|164.312(a)(2)(i),HIPAA|164.312(d),LEVEL|1S,PCI-DSS|8.1.4,SANS-CSC|16-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Run the following T-SQL query to remove an orphan user:
DROP USER <username>;"
   sql_request  : "EXEC sp_change_users_login @Action='Report';"
   sql_types    : POLICY_VARCHAR, POLICY_VARCHAR
   sql_expect   : NULL, NULL
  </custom_item>

  <custom_item>
   type         : SQL_POLICY
   description  : "3.4 Do not use SQL Authentication in contained databases"
   info         : "The absence of an enforced password policy may increase the likelihood of a weak credential being established in a contained database."
   reference    : "800-53|AC-2,800-171|3.1.1,CSF|DE.CM-1,CSF|DE.CM-3,CSF|PR.AC-1,CSF|PR.AC-4,LEVEL|1S"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Leverage Windows Authentication."
   sql_request  : "SELECT name as DBUser FROM sys.database_principals WHERE name NOT IN ('dbo','Information Schema','sys','guest') AND type IN ('U','S','G') AND authentication_type = 2"
   sql_types    : POLICY_VARCHAR
   sql_expect   : NULL
  </custom_item>

<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         : "Ensuring SQL logins comply with the secure password policy applied by the Windows Server Benchmark will ensure the passwords for SQL logins with Sysadmin privileges are changed on a frequent basis to help prevent compromise via a brute force attack."
   reference    : "800-53|IA-5,CIP|007-6-R5,HIPAA|164.308(a)(5)(ii)(D),PCI-DSSv3.1|8.2.4,800-171|3.5.10,800-171|3.5.7,800-171|3.5.8,800-171|3.5.9,CSF|PR.AC-1,ISO/IEC-27001|A.9.4.3,800-53|AC-2,LEVEL|1S,PCI-DSS|8.2.4,SANS-CSC|16-8"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Remediation:
ALTER LOGIN [login_name] WITH CHECK_EXPIRATION = ON;"
   sql_request  : "SELECT l.[name], 'sysadmin membership' AS 'Access_Method' FROM sys.sql_logins AS l WHERE IS_SRVROLEMEMBER('sysadmin',name) = 1 AND l.is_expiration_checked <> 1 UNION ALL SELECT l.[name], 'CONTROL SERVER' AS 'Access_Method' FROM sys.sql_logins AS l JOIN sys.server_permissions AS p ON l.principal_id = p.grantee_principal_id WHERE p.type = 'CL' AND p.state IN ('G', 'W') AND l.is_expiration_checked <> 1;"
   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         : "Ensuring SQL logins comply with the secure password policy applied by the Windows Server Benchmark will ensure SQL logins are not blank and cannot be easily compromised via brute force attack."
   reference    : "800-53|IA-5,CIP|007-6-R5,HIPAA|164.308(a)(5)(ii)(D),PCI-DSSv3.1|8.2.3,800-171|3.5.10,800-171|3.5.7,800-171|3.5.8,800-171|3.5.9,CSF|PR.AC-1,ISO/IEC-27001|A.9.4.3,800-53|AC-2,LEVEL|1S,PCI-DSS|8.2,SANS-CSC|16-8"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Remediation:
ALTER LOGIN [login_name] WITH CHECK_POLICY = ON;"
   sql_request  : "SELECT name, is_disabled FROM sys.sql_logins WHERE is_policy_checked = 0;"
   sql_types    : POLICY_VARCHAR, POLICY_VARCHAR
   sql_expect   : NULL, NULL
  </custom_item>

  <custom_item>
   type         : SQL_POLICY
   description  : "5.2 Set the 'Default Trace Enabled' Server Configuration Option to 1"
   info         : "Default trace provides valuable audit information regarding security-related activities on the server."
   reference    : "800-53|AU-12,800-171|3.3.1,800-171|3.3.2,CSF|DE.CM-1,CSF|DE.CM-3,CSF|DE.CM-7,CSF|PR.PT-1,ISO/IEC-27001|A.12.4.1,800-53|AU-14,800-53|AU-2,HIPAA|164.312(b),LEVEL|1S,PCI-DSS|10.2.5,SANS-CSC|14-2"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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;"
   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         : "Logging successful and failed logins provides key information that can be used to detect\confirm password guessing attacks. Further, logging successful login attempts can be used to confirm server access during forensic investigations."
   reference    : "800-53|AU-12,800-171|3.3.1,800-171|3.3.2,CSF|DE.CM-1,CSF|DE.CM-3,CSF|DE.CM-7,CSF|PR.PT-1,ISO/IEC-27001|A.12.4.1,800-53|AC-7,800-53|AU-2,HIPAA|164.312(b),LEVEL|1S,PCI-DSS|10.2.4,SANS-CSC|14-2"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   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."
   sql_request  : "XP_loginconfig 'audit level'"
   sql_types    : POLICY_VARCHAR, POLICY_VARCHAR
   sql_expect   : "audit level", "all"
  </custom_item>



  <custom_item>
   type         : SQL_POLICY
   description  : "5.4 Use SQL Server Audit to capture both failed and successful logins - AUDIT_CHANGE_GROUP"
   info         : "SQL Server Audit is capable of capturing both failed and successful logins and writing them
to one of three places: the application event log, the security event log, or the file system.
We will use it to capture any login attempt to SQL Server, as well as any attempts to change
audit policy. This will also serve to be a second source to record failed login attempts.
Rationale:
By utilizing Audit instead of the traditional setting under the Security tab to capture
successful logins, we reduce the noise in the ERRORLOG. This keeps it smaller and easier to
read for DBAs who are attempting to troubleshoot issues with the SQL Server. Also, the
Audit object can write to the security event log, though this requires operating system
configuration. This gives an additional option for where to store login events, especially in
conjunction with an SIEM."
   reference    : "800-53|AU-12,800-171|3.3.1,800-171|3.3.2,CSF|DE.CM-1,CSF|DE.CM-3,CSF|DE.CM-7,CSF|PR.PT-1,ISO/IEC-27001|A.12.4.1,LEVEL|1NS"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Via the SSMS GUI Interface:
1. Expand the SQL Server in Object Explorer.
2. Expand the Secuirty Folder
3. Right-click on the Audits folder and choose New Audit...
4. Specify a name for the Server Audit.
5. Specify the audit destination details and then click OK to save the Server Audit.
6. Right-click on Server Audit Specifications and choose New Server Audit
Specification...
7. Name the Server Audit Specification
8. Select the just created Server Audit in the Audit drop-down selection.
9. Click the drop down under Audit Action Type and select AUDIT_CHANGE_GROUP.
10. Click the new drop down under Audit Action Type and select
FAILED_LOGIN_GROUP.
11. Click the new drop down under Audit Action Type and select
SUCCESSFUL_LOGIN_GROUP.
12. Click OK to save the Server Audit Specification.
13. Right-click on the new Server Audit Specification and select Enable Server Audit
Specification.
14. Right-click on the new Server Audit and select Enable Server Audit."
   sql_request  : "SELECT S.name AS 'Audit Name' , CASE S.is_state_enabled WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END AS 'Audit Enabled' , S.type_desc AS 'Write Location' , SA.name AS 'Audit Specification Name' , CASE SA.is_state_enabled WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END AS 'Audit Specification Enabled' , SAD.audit_action_name , SAD.audited_result FROM sys.server_audit_specification_details AS SAD JOIN sys.server_audit_specifications AS SA ON SAD.server_specification_id = SA.server_specification_id JOIN sys.server_audits AS S ON SA.audit_guid = S.audit_guid WHERE SAD.audit_action_id IN ('CNAU');"
   sql_types    : POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR
   sql_expect   : regex:".+","Y",regex:".+",regex:".+","Y", "AUDIT_CHANGE_GROUP","SUCCESS AND FAILURE"
  </custom_item>


  <custom_item>
   type         : SQL_POLICY
   description  : "5.4 Use SQL Server Audit to capture both failed and successful logins - FAILED_LOGIN_GROUP"
   info         : "SQL Server Audit is capable of capturing both failed and successful logins and writing them
to one of three places: the application event log, the security event log, or the file system.
We will use it to capture any login attempt to SQL Server, as well as any attempts to change
audit policy. This will also serve to be a second source to record failed login attempts.
Rationale:
By utilizing Audit instead of the traditional setting under the Security tab to capture
successful logins, we reduce the noise in the ERRORLOG. This keeps it smaller and easier to
read for DBAs who are attempting to troubleshoot issues with the SQL Server. Also, the
Audit object can write to the security event log, though this requires operating system
configuration. This gives an additional option for where to store login events, especially in
conjunction with an SIEM."
   reference    : "800-53|AU-12,800-171|3.3.1,800-171|3.3.2,CSF|DE.CM-1,CSF|DE.CM-3,CSF|DE.CM-7,CSF|PR.PT-1,ISO/IEC-27001|A.12.4.1,LEVEL|1NS"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Via the SSMS GUI Interface:
1. Expand the SQL Server in Object Explorer.
2. Expand the Secuirty Folder
3. Right-click on the Audits folder and choose New Audit...
4. Specify a name for the Server Audit.
5. Specify the audit destination details and then click OK to save the Server Audit.
6. Right-click on Server Audit Specifications and choose New Server Audit
Specification...
7. Name the Server Audit Specification
8. Select the just created Server Audit in the Audit drop-down selection.
9. Click the drop down under Audit Action Type and select AUDIT_CHANGE_GROUP.
10. Click the new drop down under Audit Action Type and select
FAILED_LOGIN_GROUP.
11. Click the new drop down under Audit Action Type and select
SUCCESSFUL_LOGIN_GROUP.
12. Click OK to save the Server Audit Specification.
13. Right-click on the new Server Audit Specification and select Enable Server Audit
Specification.
14. Right-click on the new Server Audit and select Enable Server Audit."
   sql_request  : "SELECT S.name AS 'Audit Name' , CASE S.is_state_enabled WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END AS 'Audit Enabled' , S.type_desc AS 'Write Location' , SA.name AS 'Audit Specification Name' , CASE SA.is_state_enabled WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END AS 'Audit Specification Enabled' , SAD.audit_action_name , SAD.audited_result FROM sys.server_audit_specification_details AS SAD JOIN sys.server_audit_specifications AS SA ON SAD.server_specification_id = SA.server_specification_id JOIN sys.server_audits AS S ON SA.audit_guid = S.audit_guid WHERE SAD.audit_action_id IN ('LGFL');"
   sql_types    : POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR
   sql_expect   : regex:".+","Y",regex:".+",regex:".+","Y", "FAILED_LOGIN_GROUP","SUCCESS AND FAILURE"
  </custom_item>

  <custom_item>
   type         : SQL_POLICY
   description  : "5.4 Use SQL Server Audit to capture both failed and successful logins - SUCCESSFUL_LOGIN_GROUP"
   info         : "SQL Server Audit is capable of capturing both failed and successful logins and writing them
to one of three places: the application event log, the security event log, or the file system.
We will use it to capture any login attempt to SQL Server, as well as any attempts to change
audit policy. This will also serve to be a second source to record failed login attempts.
Rationale:
By utilizing Audit instead of the traditional setting under the Security tab to capture
successful logins, we reduce the noise in the ERRORLOG. This keeps it smaller and easier to
read for DBAs who are attempting to troubleshoot issues with the SQL Server. Also, the
Audit object can write to the security event log, though this requires operating system
configuration. This gives an additional option for where to store login events, especially in
conjunction with an SIEM."
   reference    : "800-53|AU-12,800-171|3.3.1,800-171|3.3.2,CSF|DE.CM-1,CSF|DE.CM-3,CSF|DE.CM-7,CSF|PR.PT-1,ISO/IEC-27001|A.12.4.1,LEVEL|1NS"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Via the SSMS GUI Interface:
1. Expand the SQL Server in Object Explorer.
2. Expand the Secuirty Folder
3. Right-click on the Audits folder and choose New Audit...
4. Specify a name for the Server Audit.
5. Specify the audit destination details and then click OK to save the Server Audit.
6. Right-click on Server Audit Specifications and choose New Server Audit
Specification...
7. Name the Server Audit Specification
8. Select the just created Server Audit in the Audit drop-down selection.
9. Click the drop down under Audit Action Type and select AUDIT_CHANGE_GROUP.
10. Click the new drop down under Audit Action Type and select
FAILED_LOGIN_GROUP.
11. Click the new drop down under Audit Action Type and select
SUCCESSFUL_LOGIN_GROUP.
12. Click OK to save the Server Audit Specification.
13. Right-click on the new Server Audit Specification and select Enable Server Audit
Specification.
14. Right-click on the new Server Audit and select Enable Server Audit."
   sql_request  : "SELECT S.name AS 'Audit Name' , CASE S.is_state_enabled WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END AS 'Audit Enabled' , S.type_desc AS 'Write Location' , SA.name AS 'Audit Specification Name' , CASE SA.is_state_enabled WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END AS 'Audit Specification Enabled' , SAD.audit_action_name , SAD.audited_result FROM sys.server_audit_specification_details AS SAD JOIN sys.server_audit_specifications AS SA ON SAD.server_specification_id = SA.server_specification_id JOIN sys.server_audits AS S ON SA.audit_guid = S.audit_guid WHERE SAD.audit_action_id IN ('LGSD');"
   sql_types    : POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR,POLICY_VARCHAR
   sql_expect   : regex:".+","Y",regex:".+",regex:".+","Y", "SUCCESSFUL_LOGIN_GROUP","SUCCESS AND FAILURE"
  </custom_item>

  <custom_item>
   type         : SQL_POLICY
   description  : "6.2 Set the 'CLR Assembly Permission Set' to SAFE_ACCESS for All CLR Assemblies"
   info         : "Assemblies with EXTERNAL_ACCESS or UNSAFE permission sets can be used to access sensitive areas of the operating system, steal and/or transmit data and alter the state and other protection measures of the underlying Windows Operating System."
   reference    : "800-53|CM-6,CSCv6|3.1,PCI-DSSv3.1|2.2.4,800-171|3.4.2,CSF|PR.IP-1,800-53|CM-2,800-53|CM-7,HIPAA|164.308(a)(1)(i)(ii)(B),LEVEL|1S,PCI-DSS|2.2.3,SANS-CSC|3-1"
   see_also     : "https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2014_Benchmark_v1.1.0.pdf"
   solution     : "Remediation:
ALTER ASSEMBLY assembly_name WITH PERMISSION_SET = SAFE;"
   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>