Mistake: leaving administrative activity unaudited. SQL Server Audit can write privileged actions to the Windows security log, helping establish accountability and preventing SQL Server administrators from tampering with the audit record. section 14.5
Make the logging path usable before creating the SQL Server audit:
- Enable application-generated audit events through auditpol. section 14.5
- Grant the SQL Server service account the Generate Security Audits user right. section 14.5
- Configure audit policy to record both successful and failed object-access events, then restart the server. section 14.5
Listing 14.6 Creating an audit object
USE master ;
GO
CREATE SERVER AUDIT AdminActivityAudit
TO SECURITY_LOG WITH (
ON_FAILURE = CONTINUE
) ;
Creates a SQL Server audit targeting SECURITY_LOG and defines behavior when writing fails. section 14.5
Listing 14.7 Creating an audit specification
CREATE SERVER AUDIT SPECIFICATION AdminActivitySpecification FOR SERVER AUDIT AdminActivityAudit ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (DBCC_GROUP), ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_OPERATION_GROUP), ADD (SERVER_STATE_CHANGE_GROUP), ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP) ;
Adds administrative action groups for role changes, audit changes, DBCC, permissions, impersonation, object changes, and server operations. section 14.5
