Collecting SQL server audit events with Operations Manager ACS


Another hot topic in forums and between customers of mine: how can I add audit events from SQL into ACS. Let’s start from a few technology key points:

  • ACS is able to collect any event recorded in the Security Event Log (technically it can be extended to collect events from any event log, but this is not supported so it’s out of play at the moment and anyway it doesn’t make any difference for SQL)
  • SQL server, at least since SQL 2000 has always been able to record logon and logoff events to the Application log, just system logon and logoff nothing more
  • At least since SQL 2000, SQL can generate very detailed auditing using Tracing, the audit trial can be saved to a table or to a file (http://technet.microsoft.com/en-us/library/dd277388.aspx)
  • Since SQL Server 2008 Enterprise (yes the enterprise version is needed) audit trial can be defined at the system and database level in a much more detailed, flexible and efficient way respect the tracing ones. More, they can be written in the security event log, too (http://technet.microsoft.com/en-us/library/cc280386.aspx)

The point is, with the exception of SQL 2008 and 2008 R2 Enterprise, being able to write the audit events to the security log in such a format that ACS is not only able to collect them but you can get useful reports as well. If the events are written to the Application log you need a hook there to parse and rewrite the events, if they’re written to a table you need an extended procedure that picks the events and writes them in the Security Log, analogously if they’re written to a file you need a hook there. I know Microsoft Services has a solution based on extended procedure and my company as a solution that translate events form any event log into the security log. I’m not pushing my own products here, but it’s indeed a solution that many customers are using to solve the SQL auditing stuff limited to system logon/logoff. (Progel Security Log Gateway)

SQL 2008 was promising (the reason of *was* in a few sentences). It promised to write any audit events in the security log with all the details you can imagine of. From the SQL documentation "When a server audit is configured with the TO APPLICATION_LOG or TO SECURITY_LOG option, all audit events are written to the relevant log. The events contain the same information as is returned by the fn_get_audit() table-valued function for audits to file. In the event log, this information is written to the event body in text format. … For users who intend to set up monitoring or alerting on event log entries, it is important to know what event log specific attributes will be assigned to SQL Server Audit entries. All SQL Server Audit events are logged with the SQL Server instance name as the event source. The event id is 33205. The description of the event is where distinguishing characteristics of events can be found. So, to implement a filtering or alerting on audit events, the Event log management tool used will need to support identification of events based on the description for example, regular expression searches)." (Auditing in SQL Server 2008) I highlighted where the problems start, clearly who designed this feature didn’t know events have properties and that properties must be used for variable parts while descriptive text is defined in the event itself. So what we have here is a big text blob in our security event, and just one event id for every events that can be generated. Bad, very bad.

This is a typical event:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

<System>

<Provider Name="MSSQLSERVER" />

<EventID Qualifiers="16384">33205</EventID>

<Level>0</Level>

<Task>4</Task>

<Keywords>0xa0000000000000</Keywords>

<TimeCreated SystemTime="2011-08-20T15:20:52.000Z" />

<EventRecordID>91446113</EventRecordID>

<Channel>Application</Channel>

<Computer>sql2k8.pre.lab</Computer>

<Security />

</System>

<EventData>

<Data>event_time:2011-08-20 15:20:52.4100000 sequence_number:1 action_id:LGIS succeeded:true permission_bitmask:0 is_column_permission:false session_id:67 server_principal_id:259 database_principal_id:0 target_server_principal_id:0 target_database_principal_id:0 object_id:0 class_type:LX session_server_principal_name:NT AUTHORITY\SYSTEM server_principal_name:NT AUTHORITY\SYSTEM server_principal_sid:010100000000000512000000 database_principal_name: target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:SQL2K8 database_name: schema_name: object_name: statement:– network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed additional_information:<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"&gt; <pooled_connection>1</pooled_connection> <client_options>0x28000020</client_options> <client_options1>0x0001f438</client_options1> <connect_options>0x00000000</connect_options> <packet_data_size>8000</packet_data_size> <address>omissis</address><is_dac>0</is_dac></action_info> </Data>

</EventData>

</Event>

To better understand the blob you need to get through some reading

and to get what the action_id and class_type fields means you have to query SQL:

  • action_id – select * from sys.dm_audit_actions order by action_id
  • class_type – select * from sys.dm_audit_class_type_map

My first try has been to collect the events "as is" with ACS, this is fairly automatic unless you set a filter on the ACS collector. I started collecting and then prepared the SQL statements needed for parsing and getting the right information in the reports, I knew performance would be awful, but it was worth a try. I even checked if ACS has some transform functions that could help in pre parsing the event to split the various token, but it has none (http://blogs.msdn.com/b/ericfitz/archive/2008/02/27/acs-event-transformation-demystified.aspx).

The raw query was like this:

select top 10 ProgelACSLookup.dbo.fn_getSQLToken(String01, 'action_id:', ' ',1 ) , *
from AdtServer.dvAll5 E
where E.EventId=1073775029
order by CreationTime

Please note the use of the extended event id

Just in case you want to play with this and you need it, here is the fn_getSQLToken

CREATE FUNCTION [dbo].[fn_GetSQLToken] 
(
    @data nvarchar(1024),
    @delimiter nvarchar(80),
    @enddelim nvarchar(80),
    @pos int = 1
)
RETURNS nvarchar(1024)
AS
BEGIN
declare @start int
declare @end int
declare @res nvarchar(1024)
declare @current int

Set @start = CHARINDEX(@delimiter, @data)
if @pos > 1
BEGIN
    Set @current = 1
    while @current < @pos AND @start > 0
    BEGIN
        Set @start = CHARINDEX(@delimiter, @data, @start+1)
        Set @current = @current + 1    
    END
    
END
if @start = 0 
    RETURN ''
Set @end = CHARINDEX(@enddelim, @data, @start+1)
if @end = 0 
    RETURN ''

Set @start = @start+Len(@delimiter)    
SET @res = SUBSTRING(@data, @start, @end-@start+1)


IF @@ERROR <>0
BEGIN
    SET @res=N''
END

RETURN @res
END

So you can think, ok it’s not optimum but it works… well no. In fact the text blob is parameter1 and ACS has a limit of 448 chars for any parameter it collects, the ffffine text the SQL team prepared for us is much more and it gets truncated more or less after server_principal_sid:

event_time:2011-08-09 14:37:51.3633333 sequence_number:1 action_id:LGIS succeeded:true permission_bitmask:0 is_column_permission:false session_id:65 server_principal_id:296 database_principal_id:0 target_server_principal_id:0 target_database_principal_id:0 object_id:0 class_type:LX session_server_principal_name:PRE\CONFMGR-RE1$ server_principal_name:PRE\CONFMGR-RE1$ server_principal_sid:010500000000000515000000449879f95b09aa6701cfd8e86a070000

So here we are again without a builtin solution to collect SQL server audits with ACS. Again I don’t want to push our own commercial solution, but this is what we’re using today, parsing the event ad the client side and preparing it for ACS.

– Daniele

This posting is provided "AS IS" with no warranties, and confers no rights.

Advertisements
  1. Leave a comment

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: