SQL Server 2012: Audit Filtering May 6th, 2013

Vinod Kumar

This is in continuation to blog on SQL Server 2012: User-defined Audit. In this blog post, let us look at the concept of filtering audits. Organizations want to audit based on specific condition – a typical example is, we want to audit all connections that are outside of application zone. This is to make sure that we are cognizant of the activities that happen from external sources. Audits are used to track what happens in our database and not used for stopping us from doing an operation – that must be achieved using GRANTS, REVOKE and DENY.

Coming back to our topic – Audit filtering. Let us filter based on a specific user. For this, let us first query the username we want to filter on.

image

In our example, we are going to use principal_id of 1 i.e. sa and we will audit anything other than connections made from sa.

Create Audit

First step is to create the Audit. Notice the last two lines – we are going to add the filter condition.

CREATE SERVER AUDIT [Audit-2013-05-001]

TO FILE

(    FILEPATH = N’C:\Audit\’

     ,MAXSIZE = 0 MB

     ,MAX_ROLLOVER_FILES = 2147483647

     ,RESERVE_DISK_SPACE = OFF

)

WITH

(    QUEUE_DELAY = 1000

     ,ON_FAILURE = CONTINUE

)

— change the server_principal_id as needed

WHERE ([server_principal_id]<>(1))

GO

The next task is to start our Audit. This can be done via the UI too.

ALTER SERVER AUDIT [Audit-2013-05-001] WITH (STATE = ON)

GO

Create Audit Specification

Previous step of creating Audit shows WHERE we are doing to audit, this step is to specify WHAT we are going to audit.

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-2013-05-001]

FOR SERVER AUDIT [Audit-2013-05-001]

ADD (USER_DEFINED_AUDIT_GROUP)

WITH (STATE = ON)

GO

Just like what we did in SQL Server 2012: User-defined Audit, we are going to use the User defined Audit. Now the Management Studio looks like:

image

Create Custom Audit

We are going to create two Audit, one using the current Windows Authentication and other using sa.

— Audit write using current Vinod Account

EXEC sys.sp_audit_write 1, 1, N’This is from Vinod’

 

— Change the connection to SA

— Audit write using current sa Account

EXEC sys.sp_audit_write 1, 1, N’This is from sa’

View Logs

Select the Audit and view the logs.

image

The output will contain 2 rows, one stating the service started and second is via the Windows Authentication. Since the second custom audit was written using SA account, it doesn’t get written into our audit.

image

Final words

This was one of the demo’s I did show to folks who attended TechEd India 2013. I am sure this will be interesting addition to Audit arsenal and do let me know if you will be using this technique in your environments.

Tags: , , , , , , , , ,

This entry was posted on Monday, May 6th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply