CREATE AUDIT

In order to create audit use MS SQL Management Studio or TSQL.

Audit is just object which define destination for audited data. It itself does not collect any data and is disabled after creation.

Multiple Server Audit objects can be defined with each object being specified and operational independent from one another (that can be useful if retention differs between audited databases).

USE [master]

GO

CREATE SERVER AUDIT [myAudit]

TO FILE

( FILEPATH = N'E:\SQLData'

,MAXSIZE = 10 MB

,MAX_ROLLOVER_FILES = 30

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

When the active audit file reaches the maximum size specified, it automatically rolls over to a new file. This process continues up to the maximum number of rollover files specified. When or if the maximum number of rollover files is reached, SQL Server begins deleting an existing audit file for every new one created, starting with the oldest first. Note that if the delete operation fails for whatever reason, the audit silently continues.

After audit object is created you need to enable it but that can be done at any time. Best time to enable is when all remaining audit specifications have been created, for example when definition of DML audit is completed. Once audit is enabled new file (*..sqlaudit) is created in folder used during definition.

ALTER SERVER AUDIT [myAudit]

WITH (STATE=ON)

If Audit is configured to continue after the error then a failure to write the Audit event does not trigger the SQL Server instance to shut down but Audit events are buffered in memory until they can be flushed to the target. If the records fill the memory buffer and cannot be written to the Audit log, the server blocks any new activity that would result in an audit event being written until the buffer space is freed up or the audit is disabled. The size of the memory buffer varies, but it is around 4 MB per audit in the default case, which can accommodate at least 170 audit events (the exact number depends upon the amount of data contained in each event). If the problem is not correct before the operating system returns an error, such as a disk write failure, the Audit session is taken offline with a corresponding error written to the server’s error log; all buffered and new audit events are discarded. Upon correction of the problem, the audit object will need to be restarted in order for auditing to resume. If lost audit records are unacceptable, the audit should be configured to shut down rather than continue upon write failure.

To ensure tight synchronicity between the events captured in the audit log and the activity on the server, SQL Server Audit can be configured to write the audit entries to the log in a synchronous fashion, meaning that transactions are blocked until the event is written to its destination. The tradeoff here is obviously that performance may be affected adversely. In most situations, asynchronous Audit log writing is recommended.

Based on https://msdn.microsoft.com/en-us/library/dd392015.aspx

DML auditing

• CREATE AUDIT

• Use MS SQL Management Studio or TSQL to create database audit specification...

USE [myDatabase]

GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification_DML]

FOR SERVER AUDIT [myAudit]

ADD (DELETE ON DATABASE::[myDatabase] BY [public]), --<---- use [public] if you want to audit DELETE of all users

ADD (INSERT ON DATABASE::[myDatabase] BY [public]),

ADD (SELECT ON DATABASE::[myDatabase] BY [public]),

ADD (UPDATE ON DATABASE::[myDatabase] BY [public])

WITH (STATE = OFF) --<---- disabled by default

• Enable database audit specification (right click and select 'Enable Database Audit Specification')

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification_DML]

WITH (STATE = ON)

• Make sure that audit object is enabled - see CREATE AUDIT above

Login Auditing

There are three methods for Login Auditing

1. Using audit

2. Using server settings

3. Using SQL trace

Login Auditing (using Audit)

Use following procedure to audit failed and/or successful login attempts

• CREATE AUDIT (if it does not exist)

• Use MS SQL Management Studio or TSQL to create database audit specification

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [myServerAuditSpecification]

FOR SERVER AUDIT [myLoginAudit]

ADD (FAILED_LOGIN_GROUP),

ADD (SUCCESSFUL_LOGIN_GROUP)

WITH (STATE = OFF)

GO

Enable server audit specification (right click and select 'Enable Server Audit Specification')

ALTER DATABASE AUDIT SPECIFICATION [myServerAuditSpecification]

WITH (STATE = ON)

Make sure that audit object is enabled - see CREATE AUDIT

Login Auditing (using server settings)

There are four options for Login Auditing

• None

• Failed logins only

• Successful logins only

• Both failed and successful logins

The setting can be changed in SSMS from the Server Properties Security page.

Check

execute xp_loginconfig 'audit level';

You can also read from the registry using...

DECLARE @auditlevel int

EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', @AuditLevel output

SELECT @auditlevel

NOTE: For a named instance you should use this instead...

DECLARE @auditlevel int

EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\<InstanceName>\MSSQLServer',N'AuditLevel', @AuditLevel output

SELECT @auditlevel

View Audit Trail

The audit events are recorded in the SQL Server Log

Bibliography

View audit data

Use MS SQL Management Studio or TSQL to view auditing data.

SELECT

event_time ,

session_server_principal_name AS UserName ,

server_instance_name ,

database_name ,

object_name ,

statement

FROM sys.fn_get_audit_file('E:\SQLData\*.sqlaudit', DEFAULT, DEFAULT)

SELECT * FROM sys.fn_get_audit_file(

'E:\SQLData\MyAudit-_C26128D1-F97B-4B82-9E47-B6A296045B05_*.sqlaudit',

default, default)

File name is automatically generated by SQL Server. The file name pattern is:

<audit_name>_<audit_guid>_nn_<timestamp_as_bigint>.sqlaudit

Alternatives

"Change Data Capture can be used as an asynchronous SQL Server Audit solution, to track and audit the table’s DML changes, such as INSERT, UPDATE or DELETE operations, with no option to track the SELECT statements." (1)

Bibliography & References