SQL SERVER – User Defined Audit with SQL Server

SQL SERVER - User Defined Audit with SQL Server help-icon 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”.

SQL SERVER - User Defined Audit with SQL Server userdefined-audit-01

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


Solarwinds

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.

SQL SERVER - User Defined Audit with SQL Server userdefined-audit-02

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.”

SQL SERVER - User Defined Audit with SQL Server userdefined-audit-01

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:

SQL SERVER - User Defined Audit with SQL Server userdefined-audit-04

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.

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

Solarwinds
, , ,
Previous Post
SQL SERVER – Filtering CPU Bound Execution Plans with Extended Events
Next Post
SQL SERVER – Fix – Msg 230, Level 14, State 1 – The SELECT permission was denied on the column of the object , database , schema

Related Posts

6 Comments. Leave new

  • Nicky Gianadda (@gianadda)
    March 27, 2015 9:28 pm

    I have setup Audit on my SQL2014 instance and I’m not seeing my params for my Stored Procedures being logged. Is this configurable? (NOTE: I’m doing the data access from .NET)

    Reply
  • Data Sunrise
    May 17, 2018 4:53 pm

    The work of dynamic data masking is to protect personally identifiable data. Dynamic data masking does not require any additional server resources.

    Reply
  • I’d like to suggest expanding upon this tutorial by explaining how to capture SQL Server Audit Logs in Azure’s OMS Log Analytics portal! Thanks!

    Reply
  • we configured user audit in our Always on availability cluster databases. and .sqlaudit file has the following configurations

    USE [master] GO ALTER SERVER AUDIT [UserAudit] TO FILE (MAX_FILES = 0 ) WITH (QUEUE_DELAY = 1003 ) GO

    Audit file Maximum limit: Maximum rollover files –>selected unlimited option.

    And Maximum File Size: 200 MB

    we configured F:/Audit location –>every day we have multiple files like this

    example file:

    UserAudit_9A092703-DCEC-425A-97C1-4B5D7933B0C3_0_132008916348280000.SQL audit

    we need to Zip it when the file size reaches 1 GB to avoid disk space issues. kindly give us any solution or pointers

    Regards

    stephen

    Reply
  • I want to audit SQL Agent job status (job disabled/enabled along with user name who has done that).
    Is it possible? if yes , how ?

    Reply

Leave a Reply

Menu