LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon