LOGbinder Blog

Updates, Tips and News   RSS Feed  

«  Support for Exchange 2016... | Using Site-linked GPOs fo... »

Updated - SQL Server Audit Support in Different Editions and Versions

Mon, 26 Mar 2018 20:00:12 GMT
When we originally posted this blog, we were working with SQL Server 2012.  A few years later and SQL Server continues to evolve and offer a more robust audit function.  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.

Now, with the release of SQL Server 2017, not only is SQL Server Audit available across all editions of SQL but now it's available across different OS platforms.  With SQL Server 2017 came the introduction and full support from Microsoft for SQL Server 2017 on Linux.  SQL Server as well as auditing is available on various flavors of Linux like Red Hat Enterprise, Ubuntu, SUSE and Docker images.  There's even an Azure template of Red Hat running SQL Server 2017 that allows you to have a full SQL Server up and running and auditable in under 10 minutes  Yes, 10 minutes verified and tested by myself.  It's true, at the time of writing, Azure SQL DB's only provide a .xel file readable with SQL Server Management Studio 2017.  So yes the data is there albeit not very SIEM friendly.  This isn't so with Linux and SQL though.  With Linux you have the option, when setting up your audit specs, to output the audit data to a binary SQL audit file.  Actually, this is the only option.  Inconvenient?  Not if you are using LOGbinder for SQL since this is the supported output to get your audit data from SQL to the SIEM.

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
 SQL Server 2017 on Windows and Linux
 Server- and database-level Server- and database-level Server- and database-level
 Evaluation  Server- and database-level Server- and database-level Not a valid edition
 Developer Server- and database-level  Server- and database-level Server- and database-level
 Datacenter  Server- and database-level Not a valid edition  Not a valid edition
 Business Intelligence  None  Server-level Not a valid edition
 Standard  None  Server-level Server- and database-level
 Web  None  Server-level Server- and database-level
 Express  None  Server-level Server- and database-level

So where does LOGbinder for SQL Server fit into the SQL audit equation? LOGbinder for SQL Server 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 for SQL Server.
The audit file can then be accessed and processed by LOGbinder for SQL Server and made available for your SIEM / log management solution.

To summarize, audit logs could move the following way:

Comments disabled

powered by Bloget™