LOGbinder Blog

Updates, Tips and News   RSS Feed  

Comparison: SQL Server Audit vs. SQL Trace Audit for security analysts

Thu, 25 Sep 2014 12:50:05 GMT

Security analysts must have meaningful, relevant audit data from the mission critical applications such as SQL Server. Database admins must have no disruptions nor degradation to the performance of the mission critical instances of SQL Server. Beginning with SQL Server 2008,versions of Microsoft SQL Server offer a new, superior SQL audit capability custom-built to meet demands from both parties.

Many, if not most, organizations have gotten comfortable with SQL Trace. They have satisfied themselves with its inefficiencies, and cobbled together custom routines to reduce its voluminous output. Outweighed by whatever problems that may exist with SQL Trace is one simple fact: it doesn’t hurt the database(s) to keep it going. Nobody wants to run the risk of disrupting the current process. It may not be great, but it’s what is comfortable.

Here’s the problem: SQL Trace leaves big gaps that compromise organizations’ InfoSec and compliance policies.

So, many organizations are taking a hard look at the risks vs. rewards of moving away from SQL Trace and implementing SQL Server Audit as part of the application security intelligence SIEM deployment. To help inform the professionals charged with this decision, our founder Randy Franklin Smith, and Tamas Lengyel, one of our software engineers, have collaborated in writing a white paper, Comparison: SQL Server Audit and SQL Trace Audit. This detailed resource will help both security analysts and database admins to get a better understanding of the superior SQL Server Audit function. The white paper presents the options available to both audit logs and then provides specific benefits that come with SQL Server Audit:

  • Easy administration and predefined activities
  • Granularity, Specificity
  • Performance improvements
  • Better (and more) output options, centralized storage of audit logs
  • Audit trail integrity

The short story is that SQL Server Audit hits the sweet spot for both database admins and security analysts: it’s a low impact process that yields better results.

Get the full story, download the whitepaper. It may also be helpful to read why LOGbinder solves a critical problem in SQL Server security intelligence at logbinder.com.

SQL Server Audit Support in Different Editions and Versions

Sat, 23 Nov 2013 14:02:05 GMT
***This article was has been updated with the release of SQL Server 2017.  The updated article is located here.

SQL Server 2012 made SQL Server Audit partially available to all editions. Until SQL Server 2012, this true, native auditing feature (introduced in SQL Server 2008) was only available in Enterprise and Datacenter editions. Starting with SQL Server 2012,the server-level auditing portion of SQL Server Audit was made available to all editions, leaving only the more granular database-level auditing still exclusive to the Enterprise edition.

SQL Server Audit is based on actions and action groups. The audit can contain server-level audit specification and database-level audit specifications:
  • Server-level auditing consists of server-level audit action groups, which include server operations, such as security operations involving logins, roles and permissions, logon and logoff operations, database backup and restore,manipulation of certain database-, server-, and schema objects.
  • Database-level auditing is auditing at the database scope, and it is set on each database individually. This feature is not available in all editions of SQL Server, only in Enterprise editions. Database-level auditing utilizes database-level audit action groups, and database-level audit actions.
    • The database-level audit action groups cover some similar areas as the server-level audit groups, if applicable, but at the database level.
    • Additionally to auditing action groups,database-level auditing also enables auditing certain individual actions, such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES. These database-level audit actions can be restricted to a specific database, an object (such as table, view, stored procedure), or a schema.
Here is a summary of the SQL Server Audit support in the different editions:

 Edition  SQL Server 2008 and 2008 R2  SQL Server 2012 and 2014
 Server- and database-level Server- and database-level
 Evaluation  Server- and database-level Server- and database-level
 Developer  Server- and database-level Server- and database-level
 Datacenter  Server- and database-level N/A
 Business Intelligence  None  Server-level
 Standard  None  Server-level
 Web  None  Server-level
 Express  None  Server-level

So where does LOGbinder SQL fit into the SQL audit equation? LOGbinder SQL can be installed on any Windows server running SQL Server 2008 or later regardless of the edition, including Express. It does not need to be installed on Enterprise edition. The requirement is that the SQL Server that is being audited is:

  1. Set to produce audit events.
  2. Set to output these audit events to a location accessible to LOGbinder SQL.
The audit file can then be accessed and processed by LOGbinder SQL and made available for your SIEM / log management solution.

To summarize, audit logs could move the following way:

LOGbinder SP use of SQL Privileges

Thu, 10 Oct 2013 09:33:13 GMT

***This blog post is still important but outdated.  Please see this post for updated least privilege changes.***


In the blog on www.logbinder.com (Workaround if LOGbinder SP is having SQL database issues), a suggested workaround for insufficient privileges to SharePoint’s SQL databases is to add the LOGbinder service account as a database administrator (DBO). The question arises: How does LOGbinder SP use these elevated privileges?

Access to SharePoint databases

First, it must be understood that LOGbinder SP does not access SharePoint’s SQL databases directly. All access to SharePoint data is through the SharePoint Server Object Model (see http://msdn.microsoft.com/en-us/library/jj164060.aspx). LOGbinder SP does not execute any Transact-SQL commands directly, nor does LOGbinder SP access the SQL database directly to adjust database structure, privileges, and so forth.

The workaround suggested in the above blog is recommended based on troubleshooting in our labs, to address what apparently is a defect in the SharePoint Server Object Model. LOGbinder SP does not then use these elevated privileges to perform other activity.

LOGbinder SP’s use of SharePoint data

Even though LOGbinder SP accesses SharePoint through its object model, a secondary question may be: What activity does LOGbinder SP perform in SharePoint? LOGbinder SP’s main activity is to read SharePoint audit logs, as well as to read metadata about SharePoint site collection, lists, libraries, users, groups, and similar entities.

Through the SharePoint Server Object Model, LOGbinder SP does make some changes to SharePoint (the customer specifies these changes in the LOGbinder Control Panel). The changes LOGbinder SP will make to SharePoint include: adding/removing site collection administrators, adjusting audit policy settings for a site collection, adjusting the audit log trimming setting for a site collection, and deleting audit log records. (The documentation for LOGbinder SP contains details on these actions.) So, other than purging old log data and setting audit policy according to configuration settings by the administrator, there is nothing that LOGbinder does that modifies or could corrupt SharePoint content or the SQL database.

Audit Myth Busters: SharePoint, SQL Server, Exchange

Wed, 02 Oct 2013 08:58:56 GMT

ArcSight Connector for SQL Server Audit and LOGbinder SQL

Tue, 05 Mar 2013 17:31:46 GMT
ArcSight is an excellent tool, and together with ArcSight Connector, you can collect and process data from a variety of sources. 

The following paper looks at how you can significantly improve your experience with ArcSight when processing logs from SQL Server Audit. In this brief comparison, we examine how you will benefit by replacing ArcSight Connector for SQL Server Audit with LOGbinder SQL, our CEF certified product. It also highlights the potential impacts you will avoid by doing so.

previous | next

powered by Bloget™