backport SQL Server 2012 audit filtering to SQL Server 2008 R2

backport SQL Server 2012 audit filtering to SQL Server 2008 R2 by AlvaroFernandez

Resolved

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	771288
Opened:	11/15/2012 12:47:16 PM
Access Restriction:	Public

Workaround(s)

view

Hello,

It would be really nice to backport SQL Server 2012 capability to filter-out unwanted combinations of audit events, to SQL Server 2008 R2.

Many of our customers will wait several maintenance cycles to adopt SQL 2012 due to various reasons, and they'll probably stay on SQL 2008 R2 for several years.

And due to increasing legislation requirements, they demand us a flexible and performance-wise tool to comply auditing requirements.

I struggled and cannot find a way to exclude from the provided SQL 2008 R2 LOGIN/LOGOUT , the logon/logoff activity related to Connection Pooling. In a production environment, the volume of audit files due solely to this (purely internal) events forces us to don't recommend customers to activate this action group. And as a consequence, we can't properly audit the source of the user connections, as the login events records that information in the extended info field.

Auditing logon/logoff via SQL Audit is a must, but as said, due to the inability to filter out that kind of internal connections, that action group seems useless.

Please, do try to backport SQL Server 2012 selective audit filtering to SQL 2008 R2.

Regards,

Details (expand)

English

SQL Engine

backport SQL Server 2012 selective audit filtering to SQL 2008 R2.

Improved Security

0 attachments

Hi Il-Sung

Neither ones are options for us. We can post-filter the audit files (in fact that's what I do in a lab environment), but the sheer volume of the native SQL Audit files generated (due to this inability to filter out that unwanted connections from the pool), just means we can't recommend to our customers to include the login/logout action group for a production server (1500 concurrent client connections and more).

Even more, it's a real pity, because the login action group seems to be the only event in SQL 2008 R2 containing the connection IP address and other parameters related to the connection network source. That info is normally required to be carried on client activity's audit trail, by many standard audit record formats (check for example the guidelines for the European Payment Council at http://www.europeanpaymentscouncil.eu/knowledge_bank_list.cfm?documents_category=1&key=68&language=&sort=documents_date_publication+DESC), and as said, we can postfilter and hack conforming audit record including that info -- but only if we use the noisy login action group.

Using SQLTrace would be in my opinion a ugly workaround.

Many thanks anyway for your clarification regarding the chance of a backport.

Regards,
Alvaro