Auditing in SQL Server 2008

Auditing is a new feature introduced in SQL Server 2008. Auditing is a general word and its related to sql server to capture events that occurs in sql server, it’s similar to tracking the processes happening in sql server. This feature helps database administrators to implement a strategy to know who is performing what or to track events for a particular database. Auditing is spitted into two categories a.) Server Auditing specification and b.) Database Auditing specification. Lets look into those categories

The general process for creating and using an audit is as follows.

1.Create an audit and define the target.
2.Create either a server audit specification or database audit specification that maps to the audit. Enable the audit specification.
3.Enable the audit.
4.Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

Configuring Server audit is a simple work, just you need to provide minimal details. Server audit is placed under Security folder,

Connect to SQL 2008 server
Go to Security –> Audit
Right click on the audit folder and click on new audit

Audit_SQL2008_1

You will shown with new server audit creation window as shown below, provide necessary information to create the audit

Audit_SQL2008_2

 

Parameters
Description
Audit Name
Name of the Audit
Queue Delay
Time in millisecond, that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second), which is the default.
Shut down Server on audit log failure
Enabling this option will force SQL Server to shutdown if it was unable to write audit events to destination. (Ex. Space issue on drive where audit file is placed). This should be carefully enabled where security should not be compromised
Audit Destination
You can save captured events to any of the following destinations
*) File
*) Application_Log
*) Security_Log

Note: If File is selected then other parameters need to be provided. Writing to the Windows Security log requires the SQL Server service account to be added to the Generate security audits policy.

File Path
If File is choosen in audit destination then you need to provide the path where audit files need to be saved.
Maximum Rollover
Specify number audit files to be retained by file system
Maximum File Size
Specify the maximum audit file size, you can choose the option button to specify the size. Take care while choosing unlimited
Reserve Disk Space
Enabling this option will reserve the entire space in hard disk depends on the audit file size.

  • Once audit is configured you need to enable the audit to capture the events. You can right click on the audit and click on Enable Audit to start the audit.

Audit_SQL2008_3

Server Auditing Specification

The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are the atomic events exposed by the Database Engine. These actions are sent to the audit, which records them in the target. You can create one server audit specification per audit, because both are created at the SQL Server instance scope.

Configuring Server Audit

To configure server audit expand Security –> Server Audit Specifications in SSMS

Right click on the above folder and click on New Server Audit specification to start configuring database audit

Audit_SQL2008_10

Once this is done a new audit window will be opened as shown below, you need to provide Name of the audit and you need to select server audit and details of the events to be captured.

Note: To configure server audit you must have configured general audit then only you will be able configure db audit. All the server events captured in db audit will be stored in the target path given general audit.

Audit_SQL2008_11

 

  • Once you configured server audit, you need to enable the audit to start capturing the configured events.

If every one uses the same SQL login you wouldn’t be able to identify the exact users. In that case you can include the audit event “Successful_login_group” in the server audit to capture this info i.e it gives you the IP address in the additional information column from which you can use nslookup ipaddress to get the host name of the particular user as well.

Audit_SQL2008_12

Database Auditing Specification

The Database Audit Specification object also belongs to a SQL Server audit. You can create one database audit specification per SQL Server database per audit. The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification.

Configuring Database Audit

  • To configure database audit expand Databases –> Database Name –> Security –> Database Audit Specifications in SSMS
  • Right click on the above folder and click on New Database Audit specification to start configuring database audit

Audit_SQL2008_4

Once this is done a new audit window will be opened as shown below, you need to provide Name of the audit and you need to select server audit and details of the object or database to be monitored as shown below.
Note: To configure db audit you must have configured server audit then only you will be able configure db audit. All the database events captured in db audit will be stored in the target path given server audit.

Audit_SQL2008_5

Audit_SQL2008_6

Once you configured db audit, you need to enable the audit to start capturing the configured events.

Audit_SQL2008_7

Viewing Audit Logs

Here in this example Ive configure db audit to save to a target file, To view the audit logs you can right click on the configured server audit and then click on View Audit Logs. This log includes database audit log events too.

Audit_SQL2008_7a

Lets see an example, Ive set the db audit to capture SELECT statement issued on dbo.trace table in SAGAR database and assigned to public. i.e If any user with public privilege issues Select statement it should be captured.

Audit_SQL2008_8

Database audit has captured the event and its below

Audit_SQL2008_9

Conclusion:

Auditing is a nice feature in SQL Server 2008 which enables database administrators to capture the events without using SQL Profiler, DDL & DML triggers. I hope this feature will be light weight compared to other third party audit event collectors.


Posted

in

by

Comments

9 responses to “Auditing in SQL Server 2008”

  1. Elber avatar

    Hi,

    How can a enable database specification just for DML in ALL TABLES of my database?

    I need to do one by one table or already have a SPECIFICATION with this funcition?

    Best regards,

    Elber Portugal

    1. VidhyaSagar avatar
      VidhyaSagar

      @Elber — As far as I know there is no direct way to include all the tables in a single stretch, you need to create it for each and every table in Auditing. However you can use Change Data Capture or try http://AutoAudit.codeplex.com utility, these both are out of scope from Auditing feature.

    2. Ajay avatar

      @Elber
      You can do so by

      Steps:
      1. Disable user Database Audit Specification

      use
      ALTER DATABASE AUDIT SPECIFICATION WITH (STATE=OFF)

      2. Enable user Database Audit Specification

      ALTER DATABASE AUDIT SPECIFICATION FOR SERVER AUDIT
      ADD (SELECT ON DATABASE::aspectdb BY public)
      WITH (STATE=ON)

  2. Pann Matak avatar
    Pann Matak

    hi all,
    i have problem with auditing in sql server 2008.
    i want add client ip address to audit as default in sql server 2008
    can any one help me
    very thanks

  3. Pann Matak avatar
    Pann Matak

    In auditing in sql server 2008 i want:
    1. Auditing only back-end connection
    2.File server audit see only user sa but user sa can grant permission for use audit to anther sysadmin can use

    1. VidhyaSagar avatar

      1.) Backend connection? You mean connections from other server?

  4. Samuel avatar
    Samuel

    With this auditing, do we get client hostname?

    1. VidhyaSagar avatar

      @Samuel — On a nutshell this is to identify who did what at what time? Yes you can get the host name, user name etc

      1. Ranjith avatar
        Ranjith

        Hi VidhyaSagar,

        The auditing feature captures events asynchronously, outside the context of the session, and therefore wouldn’t capture the hostname (or the network login)

Leave a Reply to Elber Cancel reply

Your email address will not be published. Required fields are marked *