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 UltimateItSecurity.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.
-
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.
-
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.
-
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.