If DBMS authentication, using passwords, is employed, SQL Server must enforce the DoD standards for password complexity and lifetime.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-213964	SQL6-D0-007900	SV-213964r508026_rule		Medium

Description
OS/enterprise authentication and identification must be used (SRG-APP-000023-DB-000001). Native DBMS authentication may be used only when circumstances make it unavoidable; and must be documented and AO-approved. The DoD standard for authentication is DoD-approved PKI certificates. Authentication based on User ID and Password may be used only when it is not possible to employ a PKI certificate, and requires AO approval. In such cases, the DoD standards for password complexity and lifetime must be implemented. DBMS products that can inherit the rules for these from the operating system or access control program (e.g., Microsoft Active Directory) must be configured to do so. For other DBMSs, the rules must be enforced using available configuration parameters or custom code.

Description

OS/enterprise authentication and identification must be used (SRG-APP-000023-DB-000001). Native DBMS authentication may be used only when circumstances make it unavoidable; and must be documented and AO-approved. The DoD standard for authentication is DoD-approved PKI certificates. Authentication based on User ID and Password may be used only when it is not possible to employ a PKI certificate, and requires AO approval. In such cases, the DoD standards for password complexity and lifetime must be implemented. DBMS products that can inherit the rules for these from the operating system or access control program (e.g., Microsoft Active Directory) must be configured to do so. For other DBMSs, the rules must be enforced using available configuration parameters or custom code.

STIG	Date
MS SQL Server 2016 Instance Security Technical Implementation Guide	2020-09-23

Details

Check Text ( C-15181r313675_chk )
Check for use of SQL Server Authentication: SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'SQL Server Authentication' END as [Authentication Mode] If the returned value in the “[Authentication Mode]” column is “Windows Authentication”, this is not a finding. SQL Server should be configured to inherit password complexity and password lifetime rules from the operating system. Review SQL Server to ensure logons are created with respect to the complexity settings and password lifetime rules by running the statement: SELECT [name], is_expiration_checked, is_policy_checked FROM sys.sql_logins Review any accounts returned by the query other than the disabled SA account, ##MS_PolicyTsqlExecutionLogin##, and ##MS_PolicyEventProcessingLogin##. If any account doesn't have both "is_expiration_checked" and "is_policy_checked" equal to “1”, this is a finding. Review the Operating System settings relating to password complexity. Determine whether the following rules are enforced. If any are not, this is a finding. Check the server operating system for password complexity: Navigate to Start >> All Programs >> Administrative Tools >> Local Security Policy and to review the local policies on the machine. Account Policy >> Password Policy: Ensure the DISA Windows Password Policy is set on the SQL Server member server.

Check Text ( C-15181r313675_chk )

Check for use of SQL Server Authentication:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'SQL Server Authentication' END as [Authentication Mode]

If the returned value in the “[Authentication Mode]” column is “Windows Authentication”, this is not a finding.

SQL Server should be configured to inherit password complexity and password lifetime rules from the operating system.

Review SQL Server to ensure logons are created with respect to the complexity settings and password lifetime rules by running the statement:

SELECT [name], is_expiration_checked, is_policy_checked
FROM sys.sql_logins

Review any accounts returned by the query other than the disabled SA account, ##MS_PolicyTsqlExecutionLogin##, and ##MS_PolicyEventProcessingLogin##.

If any account doesn't have both "is_expiration_checked" and "is_policy_checked" equal to “1”, this is a finding.

Review the Operating System settings relating to password complexity.

Determine whether the following rules are enforced. If any are not, this is a finding.

Check the server operating system for password complexity:

Navigate to Start >> All Programs >> Administrative Tools >> Local Security Policy and to review the local policies on the machine. Account Policy >> Password Policy:

Ensure the DISA Windows Password Policy is set on the SQL Server member server.

Fix Text (F-15179r313676_fix)
Configure the SQL Server operating system and SQL Server logins for compliance. 1. Ensure the password complexity requirements for the corresponding DISA Windows Server Security Technical Implementation Guide are met on the server where the SQL Server Instance is installed. 2. Ensure SQL Server is configured to inherit password complexity rules from the operating system for SQL logins. Ensure check of policy and expiration are enforced when SQL logins are created. CREATE LOGIN WITH PASSWORD= , CHECK_EXPIRATION = ON, CHECK_POLICY = ON;

Fix Text (F-15179r313676_fix)

Configure the SQL Server operating system and SQL Server logins for compliance.

1. Ensure the password complexity requirements for the corresponding DISA Windows Server Security Technical Implementation Guide are met on the server where the SQL Server Instance is installed.

2. Ensure SQL Server is configured to inherit password complexity rules from the operating system for SQL logins. Ensure check of policy and expiration are enforced when SQL logins are created.

CREATE LOGIN WITH PASSWORD= , CHECK_EXPIRATION = ON, CHECK_POLICY = ON;