Auditing in SQL Server 2008

Introduction

Auditing is the monitoring and recording all user actions on
a database. You can base auditing on individual actions, such as database
backup, change of user logins, insert etc. or on combination of factors.

Why Audit?

Though auditing has become a popular buzzword, many DBAs and
developers still do not appreciate the requirement for auditing. Properly
implemented, auditing can achieve the below:

1.       Ensure
accountability for all actions –  in particular server, database, schema, and
table operations.

2.       Discourage
users from inappropriate actions due to their accountability.

3.       Assist
the investigation of  suspicious activity.

4.       Notify
an auditor of the actions of an unauthorized user.

5. Monitor
and gather data about specific database activities.

6. Detect
problems with an authorization or access control implementation.

7. Ensure
compliance with legal requirements, such as:

·
Sarbanes-Oxley Act

·
Health Insurance Portability and Accountability Act (HIPAA)

·
International Convergence of Capital Measurement and Standards: a
Revised Frame work

·
Japan Privacy Law

·
European Union Directive of Privacy and Electronic Communications

·
Data Protect Act (UK)

Auditing in SQL Server

Auditing is not a new a feature in SQL Server 2008. Though
the path is different in SQL Server 2008, there are options to implement
auditing in previous SQL Server versions. In addition to using DML and DDL
triggers, there is also a C2 level of Auditing. You can also use SQL Server
Profiler to gather  auditing information as well as using replication for data
auditing.

Although there are several methods to assist in auditing,
prior to SQL Server 2008 there was no unified tool that shipped with SQL
Server. SQL Server 2008 introduces a new Auditing feature which is a server
object that  users can configure.

Auditing in SQL Server 2008

Firstly, create a new Audit and configure the audit file
location. You can configure this in two ways, by using SQL Server Management
Studio (SSMS) or  by using T-SQL.

Audit Configuration Using SSMS:

There is a new Audit  node in SSMS.

By right clicking Audit and selecting New Audit, you can
create a new audit as below:

The default name is Audit plus date and time                                                                                                                    

Queue delay specifies the amount of time in
milliseconds that can elapse before audit actions are forced to be processed. A
value of 0 indicates synchronous delivery. The default minimum value is 1000
which is equal to 1 second.

Shut down server on audit log failure will shut down
the SQL Server. This feature is valuable auditing is essential on an operating
SQL Server.

Audit is where the audit is created. There are three
options for the destination; File, Security Log and Application Log.
Unfortunately, you can select only one of them, you cannot set multiple
options. If you want to have multiple options you will need to create multiple audits.

The rest of the settings apply only when you select the File
option as the Audit destination and these options  are self-explanatory.

After creating the audit, you will need to enable it. Just
right click it select Enable.

Audit Configuration Using T-SQL:

USE
[master]

GO

/******
Object:  Audit
[Audit-20091125-113200]   
Script Date: 11/25/2009 11:54:33 ******/

IF  EXISTS (SELECT *
FROM sys.server_audits WHERE
name =
N’Audit-20091125-113200′)

DROP
SERVER AUDIT [Audit-20091125-113200]

GO

USE
[master]

GO 

/******
Object:  Audit
[Audit-20091125-113200]   
Script Date: 11/25/2009 11:54:33 ******/

CREATE
SERVER AUDIT [Audit-20091125-113200]

TO
SECURITY_LOG

WITH

(     QUEUE_DELAY
= 1000

      ,ON_FAILURE
= SHUTDOWN

)

GO

The above script demonstrates how to create an Audit to
Security Log with the shut down option  on and the below code creates an audit
with a binary file.

USE
[master]

GO

/******
Object:  Audit
[Audit-20091125-113200]   
Script Date: 11/25/2009 11:57:07 ******/

IF  EXISTS (SELECT *
FROM sys.server_audits WHERE
name =
N’Audit-20091125-113200′)

DROP
SERVER AUDIT [Audit-20091125-113200]

GO

USE
[master]

GO

/******
Object:  Audit
[Audit-20091125-113200]   
Script Date: 11/25/2009 11:57:07 ******/

CREATE
SERVER AUDIT [Audit-20091125-113200]

TO
FILE

(     FILEPATH
= N’C:Audit’

      ,MAXSIZE
= 2048 MB

      ,MAX_ROLLOVER_FILES
= 2147483647

      ,RESERVE_DISK_SPACE
= OFF

)

WITH

(     QUEUE_DELAY
= 1000

      ,ON_FAILURE
= SHUTDOWN

)

GO

The audit information in the above scripts are saved in the sys.server_audits
system table.

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |