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 where the events occurred.

DISA Rule

SV-82265r2_rule

Vulnerability Number

V-67775

Group Title

SRG-APP-000097-DB-000041

Rule Version

SQL4-00-012000

Severity

CAT II

CCI(s)

• CCI-000132 - The information system generates audit records containing information that establishes where the event occurred.

Weight

10

Fix Recommendation

Design and deploy a SQL Server Audit or Trace that captures the server name, database name, object type, object name and object owner (each where relevant) for all auditable events.

The script provided in the supplemental file Trace.sql can be used to create a trace.

The script provided in the supplemental file Audit.sql can be used to create 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 server instance, database, schema, and object names are each automatically captured when applicable; this is not a finding.

If SQL Server Trace is in use for audit purposes, verify that for all events it captures the server name, database name, object type, object name and object owner (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 EC26.columnid IS NULL THEN 'Server Name (26) missing' ELSE '26 OK' END AS field26,
CASE WHEN EC35.columnid IS NULL THEN 'Database Name (35) missing' ELSE '35 OK' END AS field35,
CASE WHEN EC28.columnid IS NULL THEN 'Object Type (28) missing' ELSE '28 OK' END AS field28,
CASE WHEN EC34.columnid IS NULL THEN 'Object Name (34) missing' ELSE '34 OK' END AS field34,
CASE WHEN EC37.columnid IS NULL THEN 'Object Owner (37) missing' ELSE '34 OK' END AS field37
FROM E E
LEFT OUTER JOIN EC EC26
ON EC26.eventid = E.eventid
AND EC26.columnid = 26
LEFT OUTER JOIN EC EC35
ON EC35.eventid = E.eventid
AND EC35.columnid = 35
LEFT OUTER JOIN EC EC28
ON EC28.eventid = E.eventid
AND EC28.columnid = 28
LEFT OUTER JOIN EC EC34
ON EC34.eventid = E.eventid
AND EC34.columnid = 34
LEFT OUTER JOIN EC EC37
ON EC37.eventid = E.eventid
AND EC37.columnid = 37
WHERE
EC26.columnid IS NULL OR EC35.columnid IS NULL OR EC28.columnid IS NULL OR EC34.columnid IS NULL OR EC37.columnid IS NULL;

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

Vulnerability Number

V-67775

Documentable

False

Rule Version

SQL4-00-012000

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 server instance, database, schema, and object names are each automatically captured when applicable; this is not a finding.

If SQL Server Trace is in use for audit purposes, verify that for all events it captures the server name, database name, object type, object name and object owner (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 EC26.columnid IS NULL THEN 'Server Name (26) missing' ELSE '26 OK' END AS field26,
CASE WHEN EC35.columnid IS NULL THEN 'Database Name (35) missing' ELSE '35 OK' END AS field35,
CASE WHEN EC28.columnid IS NULL THEN 'Object Type (28) missing' ELSE '28 OK' END AS field28,
CASE WHEN EC34.columnid IS NULL THEN 'Object Name (34) missing' ELSE '34 OK' END AS field34,
CASE WHEN EC37.columnid IS NULL THEN 'Object Owner (37) missing' ELSE '34 OK' END AS field37
FROM E E
LEFT OUTER JOIN EC EC26
ON EC26.eventid = E.eventid
AND EC26.columnid = 26
LEFT OUTER JOIN EC EC35
ON EC35.eventid = E.eventid
AND EC35.columnid = 35
LEFT OUTER JOIN EC EC28
ON EC28.eventid = E.eventid
AND EC28.columnid = 28
LEFT OUTER JOIN EC EC34
ON EC34.eventid = E.eventid
AND EC34.columnid = 34
LEFT OUTER JOIN EC EC37
ON EC37.eventid = E.eventid
AND EC37.columnid = 37
WHERE
EC26.columnid IS NULL OR EC35.columnid IS NULL OR EC28.columnid IS NULL OR EC34.columnid IS NULL OR EC37.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