How to Configure Audit in SQL Server 2008

Auditing gone into different level with more advanced features and robust set in SQL Server 2008 compare to sql server 2000 and 2005. It grouped into 22 classes together with 81 securable set like login, server, certificates, tables, indexes, keys, role, schema and so on.

We can audit DML command with all valuable details like Insert, update and delete on table, user login and database details.

 

SQL Server Database Audit

Auditing in SQL Server 2008, first thing we have to create Audit Object which will act as container for audit specification related to server and database.
Audit will work on single server instance but not allow on multiple servers. We can gather audit information in the application log, security log and file system (local system drive and shared network location).
* SQL Service account should have all privileges to write audit data in application log, security log and file system(Read, write and modify permission)
* By default Security Audit user right given to local service and network service, same right should given to service account to write data into security log.

How to Audit database Object in SQL Server 2008 ?

Two ways to Audit database Object in SQL Server

One by using SSMS as per below steps

  • Using Object Explorer of SQL Server Management Studio (SSMS) ->Click on Server Instance -> Security -> Audit Node (Right click on Audit Node and select new Audit that will open audit window).
  • In Queue Delay default value is 1000 millisecond that means audit data will get capture within 1000 millisecond (that is 16 seconds) to the destination. If value is 0 that means processing is done synchronously and transaction will block until the data written to the destination.
  • SQL Server Shutdown on Audit log Failure this means if audit unable to write data to the destination then it will shutdown SQL Server services. Audit log failures like unable to write data in application log or security log due to insufficient permissions or unable to find directory path due to network issue or if someone deleted the folder then it will fail to record event data.
  • Don’t implement before testing it on test server.
  • Give the name of audit file and choose audit destination this could be application log, security log and file system path. Normally if we choose file system then location would be share on network. We can also reserved space for the Audit file by clicking on option ->Reserve Disk Space�.

Configure Auditing by using T-SQL

USE MASTER

CREATE SERVER AUDIT SrvAudit
TO FILE
(
FILEPATH = N’D:\SANDEEP\SQL_AUDIT\’,
MAXSIZE = 10MB,
MAX_ROLLOVER_FILES=100,
RESERVE_DISK_SPACE = ON
)
WITH
(
QUEUE_DELAY = 1000,
ON_FAILURE=SHUTDOWN
)

T-SQL provide more setting while creating audit like GUID and setting rollover files up to 16,777,215 TB. Based on disk space we can specific UNLIMITED setting for this audit.
GUID will help in failover setup lets say we have implemented Audit on failover server if failover happen then we want this audit to be shifted on secondary failover server for that we are using GUID common for both system.

USE MASTER

DECLARE @guid = UNIQUEINDENTIFIER = NEWid()

CREATE SERVER AUDIT SrvAudit
TO FILE
(
FILEPATH = N’D:\SANDEEP\SQL_AUDIT\’,
MAXSIZE = 10MB,
MAX_ROLLOVER_FILES=100,
RESERVE_DISK_SPACE = ON
)
WITH
(
QUEUE_DELAY = 1000,
ON_FAILURE=SHUTDOWN,
AUDIT_GUID = @guid
)

Audit specification

As we already discussed that audit act as container for the audit specification, specification is nothing but the information about what type of action audit should be capture in audit file.

Server Audit Specification

As name suggest it is server level audit action group together to determine what actions are audited. Server level audit information is stored in master database of the instance.

USE master

CREATE SERVER audit specification serverauditlogin
FOR SERVER audit srvAudit
ADD(FAILED_LOGIN_GROUP),
ADD(LOGIN_CHANGE_PASSWORD_GROUP)
WITH(STATE=ON)

While creating server audit specification that needs to map with server audit, then add the action event type to be capture in audit data like FAILED_LOGIN_GROUP and LOGIN_CHANGE_PASSWORD_GROUP. All these command must be executed in master database otherwise we will get below error.

Msg 33074, Level 16, State 4, Line 3
Cannot create a server audit specification from a user database.

This operation must be performed in the master database.
To enable the server audit specification set state to ON. If we want any modification to be done in server audit specification then we have first disable it before modification.
To disable the server audit specification, right click on it and select disable option from the context. to alter server audit specification by using T-SQL.

ALTER SERVER AUDIT SPECIFICATION serverauditlogin
WITH (STATE=OFF)

Lets say we want to drop one of the audit type in server audit specification then we have alter the server audit specification by below command.

ALTER SERVER AUDIT SPECIFICATION
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)

ALTER SERVER AUDIT SPECIFICATION
DROP (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)

Server Audit specification deals with server related events and failure of the same instance.

 

Know more about database auditing in sql server 2014

http://www.sqlserver-query.com/sql-server-database-audit-feature-in-sql-server-2014/

Leave a Reply

  

  

  


9 − = 5