SQL Server: Agent alerts and security audit to the Application log
SQL Server Agent alerts on critical errors and severities 16 to 25, notifications, and a server audit to the Application log.
This note covers a set of SQL Server Agent alerts on critical errors (corruption, I/O, memory, availability groups) and on severity levels 16 to 25, followed by a server audit written to the Windows Application log, which a log collection agent (for example NXLog) can then pick up.
Alerts on error numbers
Every alert follows the same pattern. Example for error 601:
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 00601',
@message_id=601,
@severity=0,
@enabled=1,
@delay_between_responses=600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
The original script creates such an alert for each of the following numbers (@enabled=1 unless stated otherwise):
| Area | Error numbers |
|---|---|
| Corruption and I/O | 601, 674, 708, 806, 823, 824, 825, 833 |
| Backup / restore / log | 3401, 3410, 3414, 3422, 3452, 3619, 3620, 3959 |
| Files and databases | 5029, 5144, 5145, 5182, 9001 |
| Service Broker | 8539, 8540, 9642, 9691, 9692, 9693 |
| Replication | 14157, 14161, 20572, 20574 |
| Scheduler / memory / startup | 17173, 17883, 17884, 17887, 17888, 17890, 17891, 18401 |
| Miscellaneous | 26023, 26024, 26070, 28034, 28036, 28047, 28048, 28080, 28091 |
| Availability groups | 35204, 35207, 35254, 35273, 35274, 35275, 35276, 35279, 41048, 41142, 41144 |
Disabled (@enabled=0) |
1205, 1480, 35201, 35202, 35206, 35262, 41049 to 41055 |
Alerts on severity levels
For severities 16 to 25, the alert uses @message_id=0 and @severity:
EXEC msdb.dbo.sp_add_alert @name=N'Severity level 16',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
The same call is repeated with @severity from 17 to 25 (names Severity level 17 ... Severity level 25).
Notifications
An alert notifies nobody without a notification. Example linking alerts to the DBA operator by e-mail (@notification_method = 1):
USE [msdb]
go
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 9602', @operator_name=N'DBA', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 17300', @operator_name=N'DBA', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 17312', @operator_name=N'DBA', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 18054', @operator_name=N'DBA', @notification_method = 1
The named alert must already exist (created with sp_add_alert), and so must the operator.
Security audit to the Application log
The script first removes any existing configuration, then creates a server audit targeting APPLICATION_LOG and an audit specification.
USE [master]
-- Clean configuration
ALTER SERVER AUDIT [Audit_nxlog] WITH (STATE = OFF)
go
drop SERVER AUDIT [Audit_nxlog]
go
USE [master]
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]
WITH (STATE = OFF)
USE [master]
DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]
-- create configuration
go
CREATE SERVER AUDIT [Audit_nxlog]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit_nxlog] WITH (STATE = OFF)
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]
FOR SERVER AUDIT [Audit_nxlog]
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
-- enable audit
ALTER SERVER AUDIT [Audit_nxlog] WITH (STATE = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]
FOR SERVER AUDIT [Audit_nxlog]
WITH (STATE=ON);
Notes
- Auditing
SUCCESSFUL_LOGIN_GROUPcan generate a large volume of events. - The installation path and configuration file of the collection agent depend on the environment and are not reproduced here.