SQL Server 2008 Auditing

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

• Create audit, define target – CREATE/ALTER SERVER AUDIT
• See https://msdn.microsoft.com/en-us/library/cc280448.aspx 
• Create audit spec, Enable the audit – CREATE/ALTER DATABASE AUDIT SPECIFICATION
• See https://msdn.microsoft.com/en-us/library/cc280767.aspx 
• Review audit results - sys.fn_get_audit_file()
• Audit records: not all actions populate all columns
• Limit 4000 characters of data per field (multiple records in that case, so you don’t miss anything)
• See https://msdn.microsoft.com/en-us/library/cc280765.aspx
• DMVs: sys.server_file_audits, See sys.database_audit_specifications

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:

• https://blogs.technet.com/josebda/archive/2009/04/01/sql-server-2008-security.aspx
• https://blogs.technet.com/josebda/archive/2009/04/01/sql-server-2008-encryption.aspx

White Paper

• Auditing in SQL Server 2008 - https://msdn.microsoft.com/en-us/library/dd392015.aspx

Comments

• Anonymous
  January 01, 2003
  Here are some notes on “SQL Server 2008 Security” I took while attending an advanced class on SQL Server
• Anonymous
  January 01, 2003
  I took some advanced SQL Server 2008 classes recently and shared my notes in a series of blog posts.