Home » Get the Log Data » How to enable MS SQL Audit Logs

How to enable MS SQL Audit Logs


For analyzing MS SQL audit logs by EventLog Analyzer, you need to initially enable auditing in MS SQL server.

To enable auditing in MS SQL server you need to,

  1. Create a SQL Server Audit Object that can be used for auditing

  2. Create a Server audit specification

  3. Enabling the Audit Object

Once the SQL Server Audit Object and Server audit specification is created an configured, EventLog Analyzer will start collecting and analyzing the MS SQL audit application logs.

MS SQL Audit Logging is supported for MS SQL Enterprise and Datacenter Editions alone

 

Note: Ensure that you have added the MS SQL Server as a host to EventLog Analyzer server. If not, add MS SQL Server as a host.

 

Create a SQL Server Audit Object

To create a SQL Server Audit Object, go to Object Explorer in MS SQL Server Management Studio

Object Explorer in MSSQL Server

  • In the object explorer, recursively expand the 'Security' node  to 'Audits'

  • Right click on the 'Audit' and select 'New Audit'. This opens 'Create New Audit' page

    Create Audit Page

  1. In the 'Audit Name' field, specify the name for the Audit Object

  2. In the 'Audit destination' field, select 'Application Log' type

Click on OK to accept the other default settings and save the new audit specification.

Creating Server Level Audit Specification

In the object explorer, right click on 'Server Audit Specification' and click on 'New Server Audit Specification'. This opens Create New Server Audit Specification window

Server Audit specification

  1. In the Name field, specify the name for the Server audit

  2. Select the your Audit object from the 'Audit' field drop down menu

  3. In the Actions table, select the following Audit Action type from the list

    • FAILED_LOGIN_GROUP
    • SUCCESSFUL_LOGIN_GROUP
    • DATABASE_OBJECT_CHANGE_GROUP
    • DATABASE_PRINCIPAL_CHANGE_GROUP
    • SCHEMA_OBJECT_CHANGE_GROUP
    • SERVER_PRINCIPAL_CHANGE_GROUP
    • LOGIN_CHANGE_PASSWORD_GROUP
    • SERVER_STATE_CHANGE_GROUP

Click on OK to save the server audit specification.

Enabling Audit Object

Now you need to enable the audit object created. Click on Audits node in Object Explorer and right click on the audit object created, and then click on Enable Audit. This will start the audit. EventLog Analyzer will now collect these audit logs from the MS SQL server that is added as a host to the EventLog Analyzer Server.

 

 
 
Copyright © 2013, ZOHO Corp. All Rights Reserved.
ManageEngine