Count off the major applications in your business: financial, HR, CRM, ERP, collaboration, project tracking, e-commerce… Which application doesn’t store its information in a relational database – many, if not most, being SQL Server? SQL Server is home to every kind of information in your organization – even SharePoint content like documents and lists.

Who can afford not to know what’s happening to their SQL Server? Things like:

  • Intrusion attempts at the database level
  • Data exports of tables with highly confidential data
  • Permission changes
  • Privileged account added
  • Security policy changes
  • Modification to databases outside of the application

Until a few years ago, you had to purchase 3rd party auditing solutions to monitor events like that. But with SQL Server 2008, SQL Server gained a true, enterprise audit capability. Previous capabilities like SQL-TRACE and C2 Auditing were all or nothing propositions: audit everything or nothing. The new audit feature SQL Server 2008 and later versions allows you to audit exactly which objects, actions and users are important. This flexibility makes database auditing possible for everyone – even those with heavily loaded servers. (See the SQL Server section at UltimateWindowsSecurity.com to learn more about SQL Auditing)

The challenge in getting SQL Server audit activity to your SIEM.

Can your SIEM, log management or big data security analytics solution directly collect SQL Server audit events? The short answer is yes, but there are significant risks to performance, security and stability with the “direct” route which is why we built LOGbinder for SQL Server. SQL Server allows you to output audit events in 2 different formats: the Windows event log and a binary file format readable through a custom SQL applications.

Risks

At first glance it seems an obvious choice to select the Windows event log, since most log management and SIEM solutions have built-in support for Windows event log collection. But there are at least 3 risks to this approach which is why Microsoft recommends against using the Windows event log.

  1. Performance

    For best performance, Microsoft recommends outputting audit logs in the alternative binary format because this replaces the overhead of the Windows event API with very fast file I/O. This is especially important on busy and loaded production databases.
  2. Security

    Commonly accepted security best practice mandates that security logs be moved off the system where they are generated as quickly as possible to prevent intruders or even malicious administrators from tampering with the audit trail. SQL Server's binary audit log format allows you to write audit logs to a shared folder on a completely different server; this is the absolute fastest and most secure way of protecting SQL audit logs.
  3. Stability

    Any organization serious about audit logging for security and compliance, requires their central log management system to collect, alert, report and archive their audit logs - including those from SQL Server. But to efficiently collect large Windows event logs from busy systems we invariably see customers ultimately installing a local agent to collect and forward events - even with so-called agent-optional log management solutions. However, database administrators tend to be extremely resistant to installing additional software on their servers because of understandable concerns for stability. On the other hand, SQL's binary audit log option allows you to write audit logs to a share folder on some other server where it can subsequently be read and processed by a collector like LOGbinder for SQL Server with zero impact to the database server.

For best performance, security and stability the recommended choice for SQL audit logs is the binary file format and optionally to specify a shared folder on a different server as the destination rather than a location on the SQL Server's local file system.

One cryptic message format for all 350 events

Aside from the risks, there is another significant cost to sending SQL Audit events to the Windows event log. There are approximately 350 different actions you can audit in SQL Server; all of those are represented by one event ID (33025) and one set of fields. Here’s an example event:

event_time:2010-09-16 12:35:30.0787755
sequence_number:1
action_id:APRL
succeeded:true
permission_bitmask:0
is_column_permission:false
session_id:54
server_principal_id:260
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:7
class_type:RL 
session_server_principal_name: ACMESP\Administrator
server_principal_name: ACMESP\Administrator
server_principal_sid:0
database_principal_name: dbo
target_server_principal_name: ACMESP\Administrator
target_server_principal_sid: 0
target_database_principal_name: John Smith
server_instance_name: SPDEV\SQL08ENT
database_name: AuditTest
schema_name:
object_name: Human Resources
statement: EXEC sp_addrolemember N'Human Resources', N'John Smith'
additional_information:
file_name=c:\sql audits\AuditAll_12633920-
FB34-4FAA-8F96-E9F8FED158A9_0_ 129276798828120000.sqlaudit
audit_file_offset=1536
        

Here's what SQL Server is trying to tell you in this case:

Add member to database role succeeded
A principal was successfully added to a database role
Action Group: DATABASE_ROLE_MEMBER_CHANGE_GROUP
Occurred: 7/21/2012 7:45:57.0000000 PM
Session ID: 54
User: SP2010\administrator
Server: SP2010-SQL8
Database: AuditTest
Member
    Name: John Smith
    Domain name: ACMESP\jsmith
Role
    ID: 7
    Name: Human Resources
Statement: EXEC sp_addrolemember N'Human Resources', N'John Smith'
    

A little more readable? That’s the same event rendered by LOGbinder for SQL Server as event ID 24020.

The Solution.

The Solution: LOGbinder for SQL Server - Connecting the SQL Server audit log to your SIEM

LOGbinder for SQL Server eliminates all 3 of these risks and solves the problem of cryptic audit messages.

LOGbinder for SQL Server lowers the impact of auditing to near zero for heavily loaded SQL Servers and translates audit events from their cryptic one-size-fits-all format into 350 different easy-to-understand audit messages. LOGbinder for SQL Server sends these enriched audit messages to your SIEM, log management or BDSA solution using the best format for the target technology.

Learn more about LOGbinder for SQL Server