SQL Server 2008 Auditing

  • Article

Here are some notes on “SQL Server 2008 Auditing” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Auditing in the past

  • Before SQL Server 2008 - common strategies
    • Triggers: No SELECT trigger, issues with nesting, firing order
    • SQL Trace/Profiler
    • Abstraction layer with stored procedures
    • Middle tier
  • SQL Server 2008: Automated system introduced

SQL Server Audit

  • Audit at the instance level, multiple audits per instance
  • Built on top of Extendend Events, more lightweight than trace
  • Event-agnostic engine
  • Consumer: Targets of the events
  • Actions: What to do when an event fires, runs independently
  • Filter: Events can be filtered using predicates
  • Enterprise Edition only
  • See https://msdn.microsoft.com/en-us/library/cc280386.aspx

Audit details

  • Server audit spec: per server per audit
  • Audit targets: send to file (including remote file share), security event log or app event log
  • Careful – Any authenticated user can read the app event log
  • Audits must be reviewed, archived on a regular basis

Process

Demo

  • Create database, Create schema, Create table, Insert data in table
  • Create login, Create user, Grant SELECT to user
  • CREATE SERVER AUDIT … TO FILE (FILEPATH=’folder’)
  • CREATE DATABASE AUDIT SPECIFICATION … FOR SERVER … ADD (SELECT ON table BY user)
  • ALTER SERVER AUDIT … WITH (STATE=ON)
  • ALTER DATABASE AUDIT SPECIFICATION … WITH (STATE=ON)
  • See sys.server_file_audits
  • See sys.database_audit_specifications
  • Issue ‘SELECT * FROM table’ as some other user
  • SELECT * FROM sys.fn_get_audit_file(‘folder*’, NULL, NULL)
  • Issue EXECUTE AS user; SELECT * FROM table;
  • SELECT * FROM sys.fn_get_audit_file(‘folder*’, NULL, NULL)
  • Managing using SSMS

Management issues

  • Careful - Performance impact on writes, obviously
  • Careful - You can configure failing audits to cause server to fail to start (-f option to override)
  • Match up audit spec GUIDs on mirrored servers
  • Issue with attached database and audit spec GUID, fix with CREATE SERVER AUDIT
  • Careful - Check for mismatched SIDs and orphan audit records

Related blog posts:

White Paper