How to Audit SQL Audit

While I was preparing my session for my next TechNet Live Meeting – Who did what and when on my database?, I was curious how you could audit the SQL audit. Actually, my goal was to be notified if my audit object layout was changed, disabled or dropped.

First thing that you can do is configuring the action group AUDIT_CHANGE_GROUP in the server and database audit specification.  This action group will log an event whenever and audit is created, modified or deleted.  OK, fine, nice feature, however… If somebody changes or disables the audit, I will not be notified about it.  It will be in just the audit log… Unless I verify the audit logs, I will never be aware about the changes that have been made.

So I was thinking of a way how I could be alerted in a fast way if somebody or something modifies the server or database audits. I know that there are other tools that are made for monitoring and alerting. For example, Microsoft System Center.  A possible solution would be writing the audit events to the application log or security log of the SQL Server.  The System Center Operations Manager is dedicated to monitoring, reporting and alerting on Windows event logs. So, problem solved! However, not every small company has those tools available…

I was looking for a solution within SQL Server to send me an alert if my audit object has been changed.

” … to send me an alert…”. SQL Server Agent can handle alerts!

Let’s see how we can configure this.  I have the following configuration

Server audit with name “Server Audit”
Server audit specification with name “Server audit specification”
Database audit specification with name “Database audit specification”

I’ve created 2 alerts, one that will capture all the modifications on my server audit objects and one that will capture the changes on my database audit object. I have used the event classes Audit Database Object Management Event Class and Audit Server Object Management Event Class for my WMI query.  Be aware when you use the where_condition that you can only use the = operand together with DatabaseName, SchemaName and ObjectName.  Other expressions cannot be used with these event properties.

image
Audit Server Alert

image
Audit Database Alert

The response of each alert is the execution of a job.  The job is going to gather some information about the event as will send an E-mail.  You can find the script below to create the job.

USE [msdb]
GO

/****** Object:  Job [Send Alert]    Script Date: 28/03/2012 14:56:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 28/03/2012 14:56:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Send Alert’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Mail]    Script Date: 28/03/2012 14:56:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Mail’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’declare @Msg varchar(8000)
declare @sub varchar(128)
declare @State smallint
declare @Statename varchar(128)
declare @StartTime varchar(30)
declare @Description varchar(512)

SELECT @Sub = ”Server Audit State of $(ESCAPE_NONE(WMI(ServerName))) changed!”
set @StartTime=SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,0,5) + ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,5,2)
+ ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,7,2)
+ ” ” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,9,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,11,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,13,2)
SELECT @Msg = ”ObjectName: $(ESCAPE_NONE(WMI(ObjectName)))”
+ CHAR(13)
+”Hostname: $(ESCAPE_NONE(WMI(Hostname)))”
+ CHAR(13)
+”Loginname: $(ESCAPE_NONE(WMI(Loginname)))”
+ CHAR(13)
+”NTDomainname: $(ESCAPE_NONE(WMI(NTDomainname)))”
+ CHAR(13)
+”NTUsername: $(ESCAPE_NONE(WMI(NTUsername)))”
+ CHAR(13)
+”StartTime: ” + @StartTime
+ CHAR(13)
+ ”Executed Statement:”
+ CHAR(13)
+ ”$(ESCAPE_NONE(WMI(TextData)))”
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”Pieter”,
@recipients = ”pieter.vanhove@kohera.be”,
@body = @Msg,
@subject = @Sub’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Remark: Make sure that Database mail is configured correctly and that the setting “Replace tokens for all job responses to alerts” is checked.  You can find this setting by selecting the properties of the SQL Server Agent (SSMS –> Right click on SQL Server Agent –> Properties) and go to the Alert system tab.

image

I know that there are still some gaps in this solution, like “What will happen if somebody disables the alert or stops the SQL Server Agent”, but at least this blog can be considered as a start…

Happy auditing!

Move Cluster Group is back!

I recently installed a Windows Server 8 beta cluster on my laptop. While I was exploring some of the new features, I noticed that Microsoft has put back the possibility to move the “cluster group” in the Failover Cluster Manager.

Since Windows Server 2008, it was not possible anymore to move the “cluster group” with the GUI of the Failover Cluster Manager. If you wanted to move it, you had to use the cluster.exe command.

cluster group “Cluster Group” /move

When I checked the Failover Cluster Manager I found the following option “Move Core Cluster Resources” on the “Actions” tab of the cluster itself.

image_thumb[1]

You even now have the possibility to choose to which node you’re going to failover in case you have more than 2 nodes, which is also a nice improvement.

Looks like Microsoft missed this feature after all and I think they will make a lot of people happy with this!