STIGQter STIGQter: STIG Summary: MS SQL Server 2014 Instance Security Technical Implementation Guide Version: 1 Release: 10 Benchmark Date: 24 Apr 2020:

SQL Server must produce Trace or Audit records containing sufficient information to establish the outcome (success or failure) of the events.

DISA Rule

SV-82269r2_rule

Vulnerability Number

V-67779

Group Title

SRG-APP-000099-DB-000043

Rule Version

SQL4-00-012200

Severity

CAT II

CCI(s)

Weight

10

Fix Recommendation

If Trace is in use for audit purposes, design and deploy a Trace that captures the NT User Name, NT Domain Name, Host Name, Login Name, DB User Name and Login SID (each where relevant) for all auditable events. The script provided in the supplemental file Trace.sql can be used to create a trace.

If SQL Server Audit is intended to be in use, design and deploy an Audit that captures all auditable events. The code provided in the supplemental file Audit.sql can be used as the basis for creating an Audit.

Check Contents

If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding.

If SQL Server Audit is in use, the Succeeded column is populated for all relevant events: this is not a finding.

If SQL Server Trace is in use for audit purposes, verify that for all events it captures the Success flag (successful use of permissions), State and Error number (each where relevant).
From the query prompt:

SELECT * FROM sys.traces;

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:

WITH
EC AS (SELECT eventid, columnid FROM sys.fn_trace_geteventinfo(#)),
E AS (SELECT DISTINCT eventid FROM EC)
SELECT
E.eventid,
CASE WHEN EC23.columnid IS NULL THEN 'Success (successful use of permissions) (23) missing' ELSE '23 OK' END AS field23,
CASE WHEN EC30.columnid IS NULL THEN 'State (30) missing' ELSE '30 OK' END AS field30,
CASE WHEN EC31.columnid IS NULL THEN 'Error (31) missing' ELSE '31 OK' END AS field31
FROM E E
LEFT OUTER JOIN EC EC23
ON EC23.eventid = E.eventid
AND EC23.columnid = 23
LEFT OUTER JOIN EC EC30
ON EC30.eventid = E.eventid
AND EC30.columnid = 30
LEFT OUTER JOIN EC EC31
ON EC31.eventid = E.eventid
AND EC31.columnid = 31
WHERE
EC23.columnid IS NULL OR EC30.columnid IS NULL OR EC31.columnid IS NULL;

If the resulting list indicates any field specifications are missing, this is a finding.

Vulnerability Number

V-67779

Documentable

False

Rule Version

SQL4-00-012200

Severity Override Guidance

If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding.

If SQL Server Audit is in use, the Succeeded column is populated for all relevant events: this is not a finding.

If SQL Server Trace is in use for audit purposes, verify that for all events it captures the Success flag (successful use of permissions), State and Error number (each where relevant).
From the query prompt:

SELECT * FROM sys.traces;

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:

WITH
EC AS (SELECT eventid, columnid FROM sys.fn_trace_geteventinfo(#)),
E AS (SELECT DISTINCT eventid FROM EC)
SELECT
E.eventid,
CASE WHEN EC23.columnid IS NULL THEN 'Success (successful use of permissions) (23) missing' ELSE '23 OK' END AS field23,
CASE WHEN EC30.columnid IS NULL THEN 'State (30) missing' ELSE '30 OK' END AS field30,
CASE WHEN EC31.columnid IS NULL THEN 'Error (31) missing' ELSE '31 OK' END AS field31
FROM E E
LEFT OUTER JOIN EC EC23
ON EC23.eventid = E.eventid
AND EC23.columnid = 23
LEFT OUTER JOIN EC EC30
ON EC30.eventid = E.eventid
AND EC30.columnid = 30
LEFT OUTER JOIN EC EC31
ON EC31.eventid = E.eventid
AND EC31.columnid = 31
WHERE
EC23.columnid IS NULL OR EC30.columnid IS NULL OR EC31.columnid IS NULL;

If the resulting list indicates any field specifications are missing, this is a finding.

Check Content Reference

M

Target Key

2639

Comments