SQL Server Audit 101 Creating Basic Audit

Auditing doesn’t have to be scary. SQL Server Audit 101 – Creating Basic Audit is part of a blog series designed to help you audit changes to your database by using SQL Server Audit.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization.  

SQL Server Audit 101 – Creating Basic Audit

SQL Server Audit 101 – Creating Basic AuditOriginally published on ColleenMorrow.com.

SQL Audit was introduced in SQL 2008, and for the first time auditing was treated as a “first-class” object in SQL Server, meaning it could be managed by DDL statements. It was built on the extended events framework and what made it really neat was that the event was recorded when the permission-check for that event occurred. What this meant to us as auditors was that the event would be recorded even if it didn’t really happen because the user didn’t have permissions. Why is this good? Well, suppose you’re auditing the execution of a stored procedure that modifies some sensitive data, like salary information. Wouldn’t it be nice to know not only who is executing that stored procedure, but who is trying to execute it?

One good thing about SQL Audit is that it executes asynchronously, which means it’s not going to hold up user processes. Unfortunately, that means it also can’t access certain information, like the network login or client associated with a session. So, going back to the salary procedure example, if a user is using a generic login to execute that procedure, you might have a hard time tracing it back to a real person.

Create the Server Audit

The first step in creating a SQL Audit is to create the audit object.

CreateServerAudit1_thumb

If you’re familiar with creating an audit in SQL Server 2008, you’ll notice a few changes in SQL 2012. The first is the “On Audit Log Failure” selection. In SQL 2008, this was only be checkbox to shutdown server on audit log failure. In SQL 2012, we now have options to continue (the equivalent of not checking the old checkbox), shutdown (checking the old checkbox) or fail operation, which will fail any operation that should have been recorded but couldn’t. This is nice if you want to prevent audited activity from going unrecorded, but don’t want to impact everything.

We have the same options for output: a file, the Windows Application log, or the Windows Security log. Keep in mind that, if you want to write to the Security log, some configuration is required.

The next change we see is the option for maximum files or maximum rollover files. Maximum rollover files means that, when that number of files is reached, the oldest file will be overwritten. If you choose Maximum files, however, once that max is reached, subsequent writes fail.

But the change that got me most excited (at first) was the new Filter tab. My biggest beef with SQL Audit in SQL 2008 was the inability to filter out any unwanted activity or objects from the audit output. It made for a lot of clutter. But in SQL 2012, we now have the ability to enter a predicate to filter the audit on, i.e. “(database_name = ‘AdventureWorks2012′)” This string is limited to 3000 characters.

CreateServerAudit2_thumb

Create the Audit Specification

The audit object tells SQL Server where to write the audit records, and how to manage them, but doesn’t actually specify what events to audit. For that, we need to create an audit specification.

There are 2 types of audit specification: a server audit specification or a database audit specification. Generally speaking, a server audit specification is used to audit events that occur at the server level; things like database creations, logins, creating a linked server. A database audit specification will audit events that occur at the database level; things like executing a stored procedure, creating a user, dropping a table. There are, however, some audit groups and events that span both levels. You can, for example, audit the SCHEMA_OBJECT_CHANGE_GROUP at the server or the database level. If you do it at the database level, it will only audit DDL changes in that database. Auditing it at the server level, however, will track DDL changes in all databases. You can create Server level audit specifications in all editions of SQL Server, however database audit specifications are only supported in Enterprise, Developer, and Evaluation editions.

Let’s say I want to audit DDL changes in the AdventureWorks2012 database. I can create a database audit specification or I can create a server audit specification and use the new filtering functionality to limit my audit output to only AdventureWorks2012 changes. Let’s do that. What’s the advantage? In this case, not much. But let’s say you have 100 databases on this server, and you want to audit all but 5. You could create database audit specs in 95 databases, or you could create one server audit spec and filter out the 5 databases you don’t want. Up to you.

CreateServerAuditSpec_thumb

Activating the Audit

Once I’ve created the audit and the audit specification, I’m almost ready to go. Before SQL Server will audit anything, I need to enable both the audit and the audit specification. I can do this by right-clicking on each and selecting “Enable” or I can do it using an ALTER statement.

Test the Audit and the Filter

I have my AdventureWorks2012 database. That’s what I’m auditing. But I also have a NoAuditDB which I’m, obviously, not auditing. If I create a table in each database and check the Audit logs (which I do by right-clicking on the Audit and selecting “View Audit Logs”) I see only one entry, the one for AdventureWorks2012.

TestAuditandFiltering_thumb

About that Filter

That filtering feature seems pretty handy, but what if you have a number of databases/objects/logins/etc. that you want to include or exclude from your audit? Listing each one can become cumbersome to say the least. What if you had a table somewhere that contained all the objects to exclude from your audit, could you use a subquery in the Filter predicate? Unfortunately, no, SQL Audit doesn’t handle this. Bummer.

That’s ok, though. As we’ll see soon, there’s more than one way to skin a cat. In fact, there’s even more than one cat. SQL Server Audit 102 – Reading Audit Output is next.

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a leading Microsoft Gold Partner for organizations who rely on Microsoft’s Data Platforms, and its mission is to enable every leader to unlock data’s full potential.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *