MS SQL Server 2008 Audit with MS Log Parser 2.2

As you may already know, MS SQL Server starting with version 2008 has the ability to audit server and database level events. Furthermore, SQL-server  audit-induced events can be sent to Windows application or security log, making them available  to a general log parsing.
In this article, I offer you the step-by-step guide that may help system administrators set sql-related audit and get all necessary information from windows security log.
Let’s consider the situation when we’ve been tasked to track all executions of some stored procedure, for example, “MonitorsSP” (on my test sql server computer it does nothing more than lists all information from “Monitors” table, but it doesn’t matter). Our goal is to get an easy-readable report (in the form of a text file or a cmd window output) containing information regarding each MonitorsSP execution.

So let’s get started.

Step 1:

First of all, we must create an audit object in SQL Studio Management Studio:

Right-click Security\Audits and click “New Audit…”. It is the audit object which defines where triggered events should be sent to. Let’s name the audit object “AuditOBJECT1″, select “Security Log” and press OK:

Step 2:

Now when we have an audit object created we can set audit specification for server or database related actions. If we were to audit  sql server users’ logon/logoff we would create a new  server audit specification by right-clicking “Server Audit Specifications” and clicking “New Server Audit Specification…”. As far as our goal is to audit execution of a stored procedure we should create a database audit specification:

In the “Database Audit Specification Properties” window name the specification and assosiate it with the audit object “AuditOBJECT1″, then select Action Type = EXECUTE, Object Class = OBJECT, Object Schema = dbo, Object Name = MonitorsSP  (the name of the stored procedure) and Principal Name = SQL\DBuser2   (my sql server computer name is “SQL”, my test user name is “DBuser2″ – of course, your server/user-name can be different).

By default, newly-created audit object and database audit specification are disabled, so we must activate ones: right-click the corresponding object and click “Enable…”.

To be able to write to Windows security log sql service account must be granted SeAuditPrivilege (also known as “Generate Security Audit”) privilege and sql server computer should be rebooted, although the latter is not mentioned in MS documentation!!! If you won’t reboot your server you’ll be getting the error 33204  “SQL Server Audit could not write to the security log” in Windows application log.

 Step 3:

Now it’s time to run “MonitorsSP” stored procedure as SQL\DBuser2 user and generate the report based on the corresponding event (33205) written to Windows security log. My SQL server uses windows authentication scheme so I log on to Windows as SQL\Dbusers, run MS SQL Server Management Studio and run “MonitorsSP” in a query window (sql login DBuser2 corresponds to Resourses database user ‘DBuser2′)

Once the execution has completed we can search through the security log for 33205 event:

The process of finding a single event is quite straightforward. But what if security log contained tens of thousands events? In that case, we can make use of Microsoft Log Parser, the free utility for log parsing (http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=24659).

Here’s the code wich helps us produce a good-looking report irrespective of number of events containing in Windows security log:

LogParser -fullText:OFF -o:csv -tabs:ON “SELECT TimeGenerated, EventID, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 16, ‘:’), 0, ‘\u000a’) AS SQL-Login, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 19, ‘:’), 0, ‘\u000a’) AS DataBase-Login, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 26, ‘:’), 0, ‘\u000a’) AS ObjectName, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 27, ‘:’), 0, ‘\u000a’) AS SQL-Statement FROM SECURITY WHERE EventID = 33205 order by TimeGenerated DESC”

If you’d like to have a .txt report for future analysis you can add to the previous code the ‘INTO [file name]‘ statement :

LogParser -fullText:OFF -o:csv -tabs:ON “SELECT TimeGenerated, EventID, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 16, ‘:’), 0, ‘\u000a’) AS SQL-Login, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 19, ‘:’), 0, ‘\u000a’) AS DataBase-Login, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 26, ‘:’), 0, ‘\u000a’) AS ObjectName, EXTRACT_TOKEN(EXTRACT_TOKEN(Strings, 27, ‘:’), 0, ‘\u000a’) AS SQL-Statement INTO E:\SqlLogs\Log1.txt FROM SECURITY WHERE EventID = 33205 order by TimeGenerated DESC”

Note: for this code to work it should be typed or copied/pasted as a single line!

If you had already happended to use MS Log Parser for parsing Windows security log you might notice some changes in Extract_Token clause. First we use semicolon ‘:’ delimiter instead of  a pipe symbol ‘|’, second we should utilize Extract_Token clause once again to separate the resultant string lines using unicode line feed   character u000a.

Let’s remove the second EXTRACT_TOKEN clause and look at the resultant txt file:

LogParser -fullText:OFF -o:csv -tabs:ON “SELECT TimeGenerated, EventID, EXTRACT_TOKEN(Strings, 16, ‘:’) AS SQL-Login, EXTRACT_TOKEN(Strings, 19, ‘:’) AS DataBase-Login, EXTRACT_TOKEN(Strings, 26, ‘:’) AS ObjectName, EXTRACT_TOKEN(Strings, 27, ‘:’) AS SQL-Statement INTO E:\SqlLogs\Log1-1.txt FROM SECURITY WHERE EventID = 33205 order by TimeGenerated DESC”

If we open this file in any hex editor we’ll see th 0A character between (for example) ‘DBuser2′ and ‘server_principal_name’. So to present our report in an appropriate format we must once again use EXTRACT_TOKEN clause with ‘\u000a’ character as a delimiter.

You can see the differences between the formats of the ‘Strings’ field of 33205 event and (for example) 4624 event (An account was successfully logged on.) here:

LogParser -fullText:OFF -o:csv -tabs:ON “SELECT TimeGenerated, EventID, Strings INTO E:\SqlLogs\Strings33205.txt FROM SECURITY WHERE EventID = 33205 order by TimeGenerated DESC”

LogParser -fullText:OFF -o:csv -tabs:ON “SELECT TimeGenerated, EventID, Strings INTO E:\SqlLogs\Strings4624.txt FROM SECURITY WHERE EventID = 4624 order by TimeGenerated DESC”

I hope this article will help system administrators easily set an audit for variuos sql-related events and use MS Log Parser for analyzing them.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: