How To Create SQL Server Audit

 

We need to create an Audit to record transactions made by people and dbas in SQL Server. In this article, we will examine the process of creating an audit.

We can define two types of audit.

 

Database Level Audit:

First, right-click Audits in the Security section of SSMS and click on the new audit.

We give a name to the AuditName section. I prefer to give the name of the database I created(AuditDBName). Because I will create the audit database-based.

We are not making any changes on “queue delay” and  “On Audit Log Failure” sections.

If you select “shut down server” from the “On Audit Log Failure” section, sql server service will stop if the audit doesnt work. You can select this option if you do not want the database to work when the audit does not work. This option can be selected in applications where security is very high.

Audit Destination will remain as File.

In the File Path section, we specify the path to write the Audit File.

 

I want to give you some critical information here. You can save Audit to the local server where your instance is running. But in order to keep the DBA’s own security and keep all DBAs under control, it is better to write the audit to a share on a remote server.

On the share on the remote server, the sql server service account must have only the insert and modify authority. There should not be any rights to drop and delete. In this way, a malicious dba will not be able to modify or delete the audit file.

 

We do not make any changes on the “Audit File Maximum Limit” section.

I usually set Maximum File Size specifically.

If the audit size does not exceed 100 MB in 1 month, I set it to 100 MB.

If the audit size will exceed 100 MB in a week, I set it to 1000 MB.

We do not choose Reserve disk space because it unnecessarily allocates disk space before writing audit.

You can also filter the audit from Filter option.

You can see these operations below.

 

Right-click on the “Database Audit Specification” in the Security tab of the database, and click “New Database Audit Specification”. We give a name from the Name field. We choose the audit we created from Audit section.

Then, in the “Audit Action Type” section, we select the sql statement that we want to audit, “DATABASE” from the object Class section, database name from the Object Name section, and the user from the Principal Name section.

As a critical knowledge; To watch sysadmin on Instance, you must select dbo in the Principal Name section.

As you can see below, we will audit the selects, updates and deletes performed by sysadmin  and updates and deletes performed by denemeUser.

Server Level Audit:

We do a new audit file configuration by repeating the first operations we performed in the “Database Level Audit” section.

Then right click on Server Audit Spesification from Security Tab and click on new server audit specification.

We give a name from the Name section and select the audit we created.

And we choose action from action type and click ok. Here you can follow a lot of things on server level.

For example, failed logins, changes on schemas, changes on audits.

I’m using almost all server-level actions.

In this way, we record all the work done by the database administrators at the server level.

 

For example, if a malicious dba stops the select audit in that database to take a select from a database that should not be read, you can create another audit for logging it.

Or, you can create an audit when a login with a select right is created in a database that should not be read.

In this way, you can follow every step taken by everyone and every breath they take. This includes you. Of course, a dba provides his/her own security by creating an audit system.

Below is a link to the details of server level audit actions.

Remember to enable audits and audit specifications.

https://msdn.microsoft.com/en-us/library/cc280663.aspx