http://www.sqlservercentral.com/blogs/cleveland-dba/2012/07/31/sql-audit-201-creating-an-audit-solution/

Printed 2014/10/22 04:25PM

SQL Audit 201 – Creating an Audit Solution

2012/07/31

Welcome back, folks!

Ok so, now that we’ve covered the basics of creating a SQL Audit and viewing the output, let’s put the pieces together to form a complete solution. As with any implementation, the key to success lies in careful planning. Before we take off running, we need to take a moment and figure out exactly where we want to go.

My requirements

Design

Now, there are a few ways I could do this, depending on what version of SQL Server I’m using. If I’m running SQL 2008, unless I want to audit all my databases, I’m limited to creating a Database Audit Specification in AdventureWorks to track the DDL changes. I would also have no way to eliminate index maintenance events from the audit, so those would need to be processed out later. However, if I’m running SQL 2012, I have the option of creating a Server Audit Specification and using a filter on the audit to limit my auditing to AdventureWorks DDL events and exclude any index maintenance commands. Like this:

CREATE SERVER AUDIT [DDLAudit]
TO FILE
(	FILEPATH = N'D:\SQL2012\Audits\'
	,MAXSIZE = 100 MB
	,MAX_ROLLOVER_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 0
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = 'd50cf1ad-2927-44c7-afd0-0c31d302ca35'
)
WHERE ([database_name]='AdventureWorks' AND NOT [statement] like 'ALTER INDEX%REBUILD%' AND NOT [statement] like 'ALTER INDEX%REORGANIZE%')
GO

In order to maintain backward compatibility to SQL 2008, I’m going to avoid using the filter for this demo.

 CREATE SERVER AUDIT [DDLAudit]
TO FILE
(	FILEPATH = N'D:\SQL2012\Audits\'
	,MAXSIZE = 100 MB
	,MAX_ROLLOVER_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 0
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = 'd50cf1ad-2927-44c7-afd0-0c31d302ca35'
)
GO
USE AdventureWorks
GO
CREATE DATABASE AUDIT SPECIFICATION [AdventureWorks_DB_DDLAudit]
FOR SERVER AUDIT [DDLAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO 

Ok, so that takes care of my first two requirements. The next item on my wish list was to have my audit records stored to a centralized audit database. Right now, that isn’t happening, all my audit info is being written to .sqlaudit files on my D drive. So my next order of business is to build a database repository to hold them.

I’ll create a centralized SQLAudit database. Ideally would be isolated from whatever instance I’m auditing, but in this case it’s located on the same instance. In this audit database I’ll create a handful of tables.

 USE [SQLAudit]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditRecord]') AND type in (N'U'))
DROP TABLE [dbo].[AuditRecord]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditStage]') AND type in (N'U'))
DROP TABLE [dbo].[AuditStage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLocator]') AND type in (N'U'))
DROP TABLE [dbo].[AuditLocator]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLoadLog]') AND type in (N'U'))
DROP TABLE [dbo].[AuditLoadLog]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditExclude]') AND type in (N'U'))
DROP TABLE [dbo].[AuditExclude]
GO

CREATE TABLE [dbo].[AuditExclude](
	[InstanceName] [nvarchar](128) NULL,
	[DatabaseName] [varchar](50) NULL,
	[SchemaName] [sysname] NOT NULL,
	[ObjectName] [varchar](50) NULL,
	[ObjectType] [varchar](50) NULL,
	[Reason] [varchar](100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditRecord](
	[audit_name] [varchar](128) NOT NULL,
	[event_time] [datetime2](7) NOT NULL,
	[sequence_number] [int] NOT NULL,
	[action_id] [varchar](4) NULL,
	[succeeded] [bit] NOT NULL,
	[permission_bitmask] [bigint] NOT NULL,
	[is_column_permission] [bit] NOT NULL,
	[session_id] [smallint] NOT NULL,
	[server_principal_id] [int] NOT NULL,
	[database_principal_id] [int] NOT NULL,
	[target_server_principal_id] [int] NOT NULL,
	[target_database_principal_id] [int] NOT NULL,
	[object_id] [int] NOT NULL,
	[class_type] [varchar](2) NULL,
	[session_server_principal_name] [nvarchar](128) NULL,
	[server_principal_name] [nvarchar](128) NULL,
	[server_principal_sid] [varbinary](85) NULL,
	[database_principal_name] [nvarchar](128) NULL,
	[target_server_principal_name] [nvarchar](128) NULL,
	[target_server_principal_sid] [varbinary](85) NULL,
	[target_database_principal_name] [nvarchar](128) NULL,
	[server_instance_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[schema_name] [nvarchar](128) NULL,
	[object_name] [nvarchar](128) NULL,
	[statement] [nvarchar](4000) NULL,
	[additional_information] [nvarchar](4000) NULL,
	[file_name] [nvarchar](260) NOT NULL,
	[audit_file_offset] [bigint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditStage](
	[audit_name] [varchar](128) NOT NULL,
	[event_time] [datetime2](7) NOT NULL,
	[sequence_number] [int] NOT NULL,
	[action_id] [varchar](4) NULL,
	[succeeded] [bit] NOT NULL,
	[permission_bitmask] [bigint] NOT NULL,
	[is_column_permission] [bit] NOT NULL,
	[session_id] [smallint] NOT NULL,
	[server_principal_id] [int] NOT NULL,
	[database_principal_id] [int] NOT NULL,
	[target_server_principal_id] [int] NOT NULL,
	[target_database_principal_id] [int] NOT NULL,
	[object_id] [int] NOT NULL,
	[class_type] [varchar](2) NULL,
	[session_server_principal_name] [nvarchar](128) NULL,
	[server_principal_name] [nvarchar](128) NULL,
	[server_principal_sid] [varbinary](85) NULL,
	[database_principal_name] [nvarchar](128) NULL,
	[target_server_principal_name] [nvarchar](128) NULL,
	[target_server_principal_sid] [varbinary](85) NULL,
	[target_database_principal_name] [nvarchar](128) NULL,
	[server_instance_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[schema_name] [nvarchar](128) NULL,
	[object_name] [nvarchar](128) NULL,
	[statement] [nvarchar](4000) NULL,
	[additional_information] [nvarchar](4000) NULL,
	[file_name] [nvarchar](260) NOT NULL,
	[audit_file_offset] [bigint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditLocator](
	[audit_name] [varchar](128) NULL,
	[file_name] [nvarchar](260) NOT NULL,
	[audit_file_offset] [bigint] NOT NULL,
	[file_pattern] [nvarchar](260) NULL,
	[locator_id] int identity(1,1) not null,
	[active] char(1) default 'Y'
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditLoadLog](
	[audit_name] [varchar](128) NULL,
	[staged_count] [int] NOT NULL,
	[saved_count] [int] NOT NULL,
	[run_date] datetime DEFAULT getdate()
) ON [PRIMARY]
GO 

Now, since I already have an audit in place that I’d like to load, I’ll seed the AuditLocator table with that information.

USE master
GO
declare @initfile nvarchar(260)
select @initfile=log_file_path+ log_file_name from sys.server_file_audits where name = 'DDLAudit'
set @initfile = STUFF(@initfile,len(@initfile)-charindex('.',reverse(@initfile)), 1, '*')
Insert into SQLAudit.dbo.AuditLocator
SELECT top 1 'DDLAudit', file_name, audit_file_offset, @initfile FROM fn_get_audit_file (@initfile, default,  default) order by event_time asc 

Now that I’ve got someplace to store my audit data permanently, I need a way to get it out of external file(s) and into my database. So I’ll also be creating a stored procedure to read my audit files, massage the data, and save it into my AuditRecord table. That would be the LoadAuditData procedure. Let’s take a look.

USE SQLAudit
GO
 create procedure LoadAuditData
as
begin
	declare @audit varchar(128),
			@file nvarchar(260),
			@offset bigint,
			@pattern nvarchar(260),
			@staged int,
			@saved int

	set nocount on

	declare cAudits cursor for
		select audit_name, file_name, audit_file_offset, file_pattern
		from AuditLocator
		where active = 'Y'
	FOR UPDATE

The first thing I’m going to do is find all the active audits in my AuditLocator table and grab the audit file location, the current file and the current offset. I decided that, if I discontinue or move an audit for some reason, rather then deleting it from the AuditLocator table, I would just mark it inactive. That way I’ll have a historical record for my reference.

	open cAudits
	fetch cAudits into @audit, @file, @offset, @pattern
	while @@fetch_status = 0
	begin

		set @staged = 0
		set @saved = 0

		insert into AuditStage
		SELECT @audit, * FROM fn_get_audit_file (@pattern, @file,  @offset)

		set @staged = @@rowcount

		insert into AuditRecord
		SELECT * from AuditStage a
		WHERE NOT EXISTS (SELECT 1 FROM dbo.AuditExclude ae WHERE
				a.server_instance_name = ae.InstanceName and
				a.database_name = ae.DatabaseName and
				a.schema_name = ae.SchemaName and
				a.object_name = ae.ObjectName)
		and statement not like '%STATISTICS%'
		and statement NOT LIKE 'ALTER INDEX%REBUILD%'
		and statement NOT LIKE 'ALTER INDEX%REORGANIZE%'

		set @saved = @@rowcount

For each of my active audits, I read in all audit records, starting with the file and offset I pulled out of AuditLocator. All audit records are loaded into AuditStage, then I pull only the records I care about into AuditRecord. In this case, I’m eliminating any statistics or index maintenance statements, and anything dealing with my excluded objects.

		select top 1 @file=file_name, @offset=audit_file_offset from AuditStage order by event_time desc

		update AuditLocator set file_name = @file, audit_file_offset = @offset
		where current of cAudits

The next step is to grab the most current record from the AuditStage table, and that’s going to be my jumping off point for the next run. This was really the major purpose of the staging table. I could easily have loaded the audit records directly from my audit file(s) into AuditRecord, filtering at the same time. But what if none of the records met my criteria? I wouldn’t have any way to update my AuditLocator record and I’d end up re-processing the same records next time. This way, I know I processed this set of records and I know none of them met my criteria, so I can move on to the next set.

		insert into AuditLoadLog (audit_name, staged_count, saved_count) values (@audit, @staged, @saved)
		DELETE AuditStage
		fetch cAudits into @audit, @file, @offset, @pattern
	end
	close cAudits
	deallocate cAudits
end 

Finally I log my record counts into the AuditLoadLog table and clear out my staging table for the next run.

Reviewing my requirements

Considerations

This audit solution has 2 things working against it: the lack of filtering in the audit and the use of rollover files.  Let’s say I size my audit files really small and I set a low limit for my maximum rollover files, say 3 files.  I’ve made a few DDL changes and they get logged to File 1.  Then let’s say I kick off a big index maintenance or update stats job, and the audit records from that job fills up the rest of File 1, File 2, and File 3.  Once File 3 is full, File 1 will be deleted and File 4 will be created.  If I haven’t processed my audit files in a while, it’s possible that valid audit records in File 1 could be lost. So it’s important to size your files appropriately for the amount of audit data you expect, keep a good number of rollover files, and, most importantly, process your files often.

What’s next?

What if I want to deploy SQLAudit to a number of instances/databases? I could point and click my way through it, but that’s asking for a typo or configuration mistake. I could script it in T-SQL, but that’s so last year. So how about we use PowerShell? Next time we’ll go over ways to create, drop, and manage SQL Audit via PowerShell and we’ll use a master audit as a template for deploying audits to the instance/database of our choosing.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.