SQL Server must notify appropriate individuals when accounts are modified.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41305	SQL2-00-023300	SV-53787r4_rule		Medium

Description
Once an attacker establishes initial access to a system, they often attempt to create a persistent method of re-establishing access. One way to accomplish this is for the attacker to modify an existing account for later use. Notification of account creation is one method and best practice for mitigating this risk. A comprehensive account management process will ensure an audit trail which documents the creation of application user accounts and notifies administrators and/or application owners exist. Such a process greatly reduces the risk that accounts will be surreptitiously created and provides logging that can be used for forensic purposes. To address the multitude of policy based access requirements, many application developers choose to integrate their applications with enterprise level authentication/access mechanisms that meet or exceed access control policy requirements. Such integration allows the application developer to off-load those access control functions and focus on core application features and functionality.

Description

Once an attacker establishes initial access to a system, they often attempt to create a persistent method of re-establishing access. One way to accomplish this is for the attacker to modify an existing account for later use. Notification of account creation is one method and best practice for mitigating this risk. A comprehensive account management process will ensure an audit trail which documents the creation of application user accounts and notifies administrators and/or application owners exist. Such a process greatly reduces the risk that accounts will be surreptitiously created and provides logging that can be used for forensic purposes. To address the multitude of policy based access requirements, many application developers choose to integrate their applications with enterprise level authentication/access mechanisms that meet or exceed access control policy requirements. Such integration allows the application developer to off-load those access control functions and focus on core application features and functionality.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2018-02-27

Details

Check Text ( C-47874r4_chk )
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.

Check Text ( C-47874r4_chk )

Check to see that all required events are being audited.
From the query prompt:
SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0);
All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding.

Determine the trace(s) being used for the auditing requirement.
In the following, replace # with a trace ID being used for the auditing requirements.
From the query prompt:
SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#);
The following required event IDs should be listed:
14, 15, 18, 20,
102, 103, 104, 105, 106, 107, 108, 109, 110,
111, 112, 113, 115, 116, 117, 118,
128, 129, 130,
131, 132, 133, 134, 135,
152, 153,
170, 171, 172, 173, 175, 176, 177, 178.
If any of the audit event IDs required above is not listed, this is a finding.

Notes:
1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner.
2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account.
3. It is acceptable to trace additional event IDs. This is the minimum list.
4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.)
Use the following query to obtain a list of all event IDs, and their meaning:
SELECT * FROM sys.trace_events;
5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements.
6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.

Fix Text (F-46696r5_fix)
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.