Unapproved inactive or expired database accounts should not be found on the database.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15130	DG0074-SQLServer9	SV-24224r2_rule	IAAC-1	Medium

Description
Unused or expired DBMS accounts provide a means for undetected, unauthorized access to the database.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-20337r2_chk )
Review procedures and implementation for monitoring the DBMS accounts for expiration or inactivity. Note: SQL Server does not maintain login statistics within the DBMS so account inactivity has to be determined manually. Review login accounts defined for the instance: SELECT SUBSTRING(name, 1, 30) AS [LoginName], CASE is_disabled WHEN 1 THEN 'true' ELSE 'false' END AS [IsDisabled], CASE CAST(LoginProperty(name, 'IsExpired') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS [IsExpired], CASE CAST(LoginProperty(name, 'IsLocked') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS [IsLocked] FROM [master].sys.server_principals WHERE type = 'S' AND (is_disabled <> 1 AND CAST(LoginProperty(name, 'IsExpired') AS int) = 1 OR CAST(LoginProperty(name, 'IsLocked') AS int) = 1) ORDER BY name Review the output from the command above and compare the accounts against audit records to determine account activity. Verify all accounts listed that are expired or locked are documented in the System Security Plan and authorized to remain. If any listed accounts are not documented or authorized, this is a Finding. Listed expired/locked accounts that are documented and authorized are still reported at OPEN findings, but are Documentable in VMS.

Check Text ( C-20337r2_chk )

Review procedures and implementation for monitoring the DBMS accounts for expiration or inactivity.

Note: SQL Server does not maintain login statistics within the DBMS so account inactivity has to be determined manually.

Review login accounts defined for the instance:

SELECT SUBSTRING(name, 1, 30) AS [LoginName],
CASE is_disabled WHEN 1 THEN 'true' ELSE 'false' END AS [IsDisabled],
CASE CAST(LoginProperty(name, 'IsExpired') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS [IsExpired],
CASE CAST(LoginProperty(name, 'IsLocked') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS [IsLocked]
FROM [master].sys.server_principals
WHERE type = 'S'
AND (is_disabled <> 1
AND CAST(LoginProperty(name, 'IsExpired') AS int) = 1
OR CAST(LoginProperty(name, 'IsLocked') AS int) = 1)
ORDER BY name

Review the output from the command above and compare the accounts against audit records to determine account activity.

Verify all accounts listed that are expired or locked are documented in the System Security Plan and authorized to remain. If any listed accounts are not documented or authorized, this is a Finding.

Listed expired/locked accounts that are documented and authorized are still reported at OPEN findings, but are Documentable in VMS.

Fix Text (F-20162r1_fix)
Develop, document and implement procedures to monitor database accounts for inactivity and expiration. Investigate, document and authorize if appropriate any accounts that are expired or locked or have been inactive for more than 30 days. Where appropriate, protect authorized expired or inactive accounts by disabling them or applying some other similar protection: ALTER LOGIN [NAME] DISABLE Note: DBMS accounts using Windows Authentication or linked to certificates can be monitored or managed by the host or through Active Directory for domain accounts. Ensure DBA and SA coordinate host/domain account management and host/domain account management meets host/domain-level STIG requirements.

Fix Text (F-20162r1_fix)

Develop, document and implement procedures to monitor database accounts for inactivity and expiration.

Investigate, document and authorize if appropriate any accounts that are expired or locked or have been inactive for more than 30 days.

Where appropriate, protect authorized expired or inactive accounts by disabling them or applying some other similar protection:

ALTER LOGIN [NAME] DISABLE

Note: DBMS accounts using Windows Authentication or linked to certificates can be monitored or managed by the host or through Active Directory for domain accounts. Ensure DBA and SA coordinate host/domain account management and host/domain account management meets host/domain-level STIG requirements.