Native Auditing in MS SQL Server 2008

The SQL data being very critical, organizations need to ensure that it is accessed and used only by the authorized users. That is why SQL Server auditing becomes very important. Such an auditing is extremely important for meeting various security compliances mandated by regulatory agencies/governing bodies too.

From SQL Server 2008 Enterprise edition onwards, Microsoft provides somewhat comprehensive inbuilt auditing features in SQL Server.

What is meant by SQL Server Auditing?

SQL Server Auditing is simply finding and collecting information related to activities, changes, and modifications happening in SQL Server and database. Technically, native auditing in SQL Server is implemented with the help of SQL Server Extended Events. This auditing basically involves SQL Server Audit, Server Audit Specification, and Database Audit Specification.

SQL Server Audit

SQL Server Audit happens at the instance level. Here database or server-level actions are tracked and traced per instance (or group of instances). You can choose to perform multiple audits on a single instance.

Server Audit Specification

Here Auditing is done at the Server level. You need to create a Server Audit Specification. Only one specification can be created per server per audit. But for audit, you can add audit action groups that comprises of many instance level actions.

Database Audit Specification

Here Auditing is done at the Database level. One specification can be created per database per audit. But for audit, you can add audit actions groups or audit events.

Storing the audit data

The SQL Server Audit data can be saved in a file, or in Security or Application event log of Windows. It is suggested to archive the logs from time to time to ensure that it has enough space to add new data.

Positives of SQL Server native auditing

The native auditing feature is a simple and secure way to audit the SQL Server activities without affecting the normal working of the Server or its performance. It offers the convenience of saving audit logs in a file, or Windows Application/Security log. Also, it is easy to fetch data from audit logs. Auditing can be done using SQL Server Management Studio or using Transact-SQL DDL statements.

Doing audit using SQL Server Management Studio

In order to perform SQL audit one can use SQL Server Management Studio. The auditing process involves four major steps:

1. Create an audit object

2. Create Database Audit Specification and Server Audit Specification

3. Enable audit object and audit specifications to start auditing.

4. Finally, open the audit logs to view the reports.

Follow the steps below for auditing:

1. Open and log in to SQL Server Management Studio.

2. Expand the Security, right-click on Audit, select New Audit.

    

3. Enter the details in Create Audit dialogue box (you can use or edit the default values).

    

4. Now proceed to create Server Audit Specification and Database Audit Specification (steps are similar for both).

5. To create Server Audit Specification, right-click on Database Audit Specifications folder in the object-explorer, and click New Database Audit Specification.

    

6. Now enter the details (select DATABASE_OBJECT_CHANGE_GROUP in the Audit Action Type dropdown) in the Create Database Audi t Specification dialogue box.

    

7. In a similar way, create Server Audit Specification too.

8. Finally, enable the audit object, Database Audit Specification, and Sever Audit from the object explorer (using the right-click menu).

    

9. 1.Now the auditing starts. The tool records the audit information in the audit logs. You can view them by selecting ‘View Audit Logs’ from the right click menu.

    

LepideAuditor Suite – A Professional SQL Server Auditing Tool

Professional SQL Server auditing tools have many advantages over native tools. They are more comprehensive and user-friendly. That is why LepideAuditor Suite is used for auditing SQL Server activities. Also, it supports almost all versions of MS SQL Server.