How to Configure Auditing in SQL Server 2012/2016

In this post, we will explain how to set up and configure auditing in SQL Server.

---

Auditing in SQL Server 2012/2016

SQL Server introduces new auditing capabilities that provide track database usage audit and I think it’s invaluable to all database administrators.

Auditing in SQL Server can be implemented at

• The server level.
• The database level, and can be enabled on individual database objects.

Additionally, it can be saved to different formats, such as

• Binary files or
• Windows Application log.

Set up Auditing in SQL Server

Before we getting started to configure Auditing in SQL Server, it’s first preferred to identify the main objects which describe audits.

• Server Audit.
• Server Audit Specification.
• Database Audit Specifications

What’s the Server Audit in SQL Server?

The Server Audit object describes the target for audit data, as well as some top-level configuration settings.

Note: the Server Audit object contains no information about what is being audited, it’s just where the audit data is going.

What’s the SQL Server Audit Specification?

The Server Audit Specification object describes

• What to audit?
• Where the audit data is written?

It’s a one-to-one relationship between the Server Audit Specification object and the Server Audit object.

What’s the Database Audit Specification in SQL Server?

The Database Audit Specification describes also

• What to audit for a specific database?
• Where the audit data is written?

Each database audit specification can be associated with only one server audit. A server audit can be associated with only one database audit specification per database.

---

Configure SQL Server Auditing Steps

1. Specify the SQL Server Instance.
2. Specify Audit type (Server Audit – Database Audit).
3. Enable the SQL Server audit.
4. View the audited data.

Create Server Audit in SQL

Below is a Server Audit Creation example called MCIT_AUDIT.

In this example, the audit will be written to “F:\AuditLogs\” without delay, and shutdown on failure.

CREATE SERVER AUDIT PCI_Audit
TO FILE (FILEPATH = 'F:\AuditLogs\', MAXSIZE = 1GB, MAX_ROLLOVER_FILES = 80)
WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN)

Modify Server Audit in SQL

After creating a Server Audit, you can modify it using “ALTER SERVER AUDIT” statement as shown below:

The below example shows how to change the destination to the Windows Application log and continues on failure.

ALTER SERVER AUDIT PCI_Audit
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)

Enable Server Audit State in SQL

By default, the Server Audit is disabled, and you can use the “ALTER SERVER AUDIT” statement to enable or disable a server audit as shown below.

ALTER SERVER AUDIT PCI_Audit
WITH (STATE = ON)

You can also enable Server Audit from SQL Management Studio as shown below:

Where Server Audit stored in SQL Server?

In SQL Server Management Studio, server audits are created and managed in the “Audits” folder, which is under the “Security” server level.

View and Read Audit Log in SQL Server

Simply, you can read the Audit Log file from SQL Server Management Studio as shown below:

• On the server level, expand the “Security” folder.
• Expand the “Audit” folder.
• Right-click on the Audit file, and select “View Audit Logs“.

Create Server Audit Specification in SQL Server

• On the server level, expand the “Security” folder.
• Expand the “Audit” folder.
• Right-click on “Server Audit Specification“, and select “New Server Audit Specification“.

• Provide an appropriate name for “Server Audit Specification“.
• Specify Audit Action Types as you prefer.

What’s Audit Action Types?

Audits can have the following action levels:

• Server-level.
• Database-level.
• Audit-level.

Server-level

These actions include server operations, such as management changes and logon and logoff operations.

Database-level

These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.

Audit-level

These actions include actions in the auditing process.

Read more about AUDIT ACTION TYPE at SQL Server Audit Action Groups and Actions.

Create SQL Server Audit Specification on database level

• On the database level, expand the “Security” folder.
• Expand the “Server Audit Specification” folder.
• Provide the name and select the audit name.
• Specify which database you would like to enable Auditing.
• Go back to action type where you can find insert – delete – update and so on.

Modify Audit Action Types

If you would like to add, remove or change any action type you must disable the server audit specification firstly.

---

SQL Server Audit Limitations

Although the SQL Server Audit has many awesome features, however, it also has some limitations that should be considered:

• It uses SQL Server resources and based on the more detailed your auditing, the more SQL Server resources that are consumed.
• The SQL Server Audit is instance-based, you will not able to manage all SQL Server instances from a centralized location. Meanwhile, you can do it if you have created your own script as a workaround.
• There are no built-in Audit reports, you have to create your own reports using SSRS.
• Difficulty in managing logs files that stored either in a file system or OS event logs.

---

Conclusion

In conclusion, we have explained How to Configure Auditing in SQL Server by doing the the following:

• Set up and Configure Auditing in SQL Server.
• What’s the Server Audit in SQL Server?
• What’s the Server Audit Specification in SQL Server?
• Configure SQL Server Auditing Steps.
• Create Server Audit in SQL.
• Modify Server Audit in SQL.
• Enable Server Audit State in SQL.
• Where Server Audit stored in SQL Server?
• View and Read Audit Log in SQL Server
• Create Server Audit Specification in SQL Server.
• Create Server Audit Specification on database level in SQL Server.
• What’s Audit Action Types?
• Modify Audit Action Types.
• SQL Server Audit Limitations.

Applies To

• SQL Server 2012.
• SQL Server 2014.
• SQL Server 2016.

You may also like to read

• SQL Server: Get the Detailed Information Via SERVERPROPERTY.
• SQL Server: How to get the current installed update level.
• Create a LOG_BACKUP Maintenance Plan in SQL Server.

Have a Question?

If you have any related questions, please don’t hesitate to Ask it at deBUG.to Community.