SQL SERVER – User Defined Audit with SQL Server

Talk to any financial institution or bank they will be super paranoid when it comes to security and auditing policies applied to their organizations. In a recent session to one of our customers, I had to enter their premises and I had to go through some screening. From the entrance, car parking, reception, elevators and everywhere there was someone asking us for some information. The more I think about it, the more frustrated I become. After returning home, I thought through the complete incident with my family. I was pleasantly surprised the calmness at which they were talking to me about an audit process.

Wow, it is a great way to learn patience and why this is important. Organizations have a reason, they need data secure, they want to have procedures so that there is no data loss, no theft of data and many more. In this competitive world, this is super critical. Hence these procedures are essential, essential for survival. And thus this blog is inspired by the Auditing capability with SQL Server 2012 and what I found interesting.

Creating our Audit

Creating an audit is as simple as going through a series of the wizard with some basic data. Go to SQL Server Management Studio -> Security -> Audits and create a new process. Go ahead and add the FilePath, in our example I have attached it as “C:\Audit”.

The TSQL equivalent for the same is:

CREATE SERVER AUDIT [Audit-20141115-213944] TO FILE
( FILEPATH = N'C:\Audit'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE)
GO

After creating the same, we will make sure this Audit is enabled. This ensures where our Auditing data will get into. In our example, it is configured to a File as shown above.

Once we enable the Audit node, the next step is to add to this operation. Here we are going to add the “User Defined Audit Group.”

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141115-214433]
FOR SERVER AUDIT [Audit-20141115-213944]
ADD (USER_DEFINED_AUDIT_GROUP)
GO

Creating User-defined Audit entries

This is a capability that was introduced with SQL Server 2012, and I personally feel this is quite powerful for a couple of reasons. Now, apart from standard auditing capability now applications can raise specific auditing into the audit log so that we can track or audit logically from an applications point of view.

The simplest way to raise an audit record from an application is using the stored procedure sp_audit_write command. A typical command looks like:

EXEC sys.sp_audit_write 1, 0, N'This is an audit from Pinal'

This command will now put an entry into my process, we just defined in the previous. To view the entry, select the “View Audit Log” option from the audits we just created. The Log viewer looks like below:

In the example above, we can see the details. Please note the Audit “Succeeded as False” because we sent the second parameter to sys.sp_audit_write as 0 hence it is False. If you want to enter it as True, pass the value as 0. Also, the statement that got us this information is available as part of “Statements” line item.

If you want to view all the entries into our audit file, we can use the sys.fn_get_audit_file function.

SELECT * FROM sys.fn_get_audit_file('c:\Audit\*', NULL, NULL)

I hope you got a flavor of how custom user-defined audits can be defined. This is a great way to use the auditing feature from your application’s point of view. Do let me know if you found this useful and if you are planning to use the same in your environments.

Connect with me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)