Detections
Analytics

SQL4-00-034000 - SQL Server must produce Trace or Audit records of its enforcement of access restrictions associated with changes to the configuration of the DBMS or database(s) - SERVER_ROLE_MEMBER_CHANGE_GROUP

Information

Without auditing the enforcement of access restrictions against changes to configuration, it would be difficult to identify attempted attacks and an audit trail would not be available for forensic investigation for after-the-fact actions.

Enforcement actions are the methods or mechanisms used to prevent unauthorized changes to configuration settings. Enforcement action methods may be as simple as denying access to a file based on the application of file permissions (access restriction). Audit items may consist of lists of actions blocked by access restrictions or changes identified after the fact.

Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.

Solution

Design and deploy a SQL Server Audit or Trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to create a trace.

Where SQL Server Audit is in use, design and deploy a SQL Server Audit that captures all auditable events. The script provided in the supplemental file Audit.sql can be used for this.

Alternatively, to add the necessary data capture to an existing server audit specification, run the script:
USE [master];
GO
ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF);
GO
ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name>
 ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
 ADD (AUDIT_CHANGE_GROUP),
 ADD (BACKUP_RESTORE_GROUP),
 ADD (DATABASE_CHANGE_GROUP),
 ADD (DATABASE_OBJECT_ACCESS_GROUP),
 ADD (DATABASE_OBJECT_CHANGE_GROUP),
 ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
 ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
 ADD (DATABASE_OPERATION_GROUP),
 ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
 ADD (DATABASE_PERMISSION_CHANGE_GROUP),
 ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
 ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
 ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
 ADD (DBCC_GROUP),
 ADD (LOGIN_CHANGE_PASSWORD_GROUP),
 ADD (SCHEMA_OBJECT_CHANGE_GROUP),
 ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
 ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
 ADD (SERVER_OBJECT_CHANGE_GROUP),
 ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
 ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
 ADD (SERVER_OPERATION_GROUP),
 ADD (SERVER_PERMISSION_CHANGE_GROUP),
 ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
 ADD (SERVER_STATE_CHANGE_GROUP),
 ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
 ADD (TRACE_CHANGE_GROUP)
;
GO
ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON);
GO

See Also

https://dl.dod.cyber.mil/wp-content/uploads/stigs/zip/U_MS_SQL_Server_2014_Y24M07_STIG.zip

Item Details

Category: CONFIGURATION MANAGEMENT

References: 800-53|CM-5(1), CAT|II, CCI|CCI-001814, Rule-ID|SV-213874r981958_rule, STIG-ID|SQL4-00-034000, STIG-Legacy|SV-82393, STIG-Legacy|V-67903, Vuln-ID|V-213874

Plugin: MS_SQLDB

Control ID: b1df5fc0434b9a88388f776d7627abb92aea8faeae2badec6be99f50b0b9fffe