How to Set Up SQL Server Auditing

SQL Server auditing requires two objects: a server audit, and either a server or a database audit specification. It is best to have one server audit for the server audit specification, and a second audit that contains all of the database audit specifications for that server. This gives the DBA an extra level of control over auditing, since the DBA can deactivate all database auditing by deactivating the audit instead of all the individual specifications (Note: auditing will record the starting or stopping of auditing and who did it).

Prerequisites

As previously mentioned, SQL Server 2008 to SQL Server 2016 (before SP1) require Enterprise/Developer/Evaluation edition. Since we choose to write to the Security Log, we need to set some policies before installing auditing.

• Setting Audit Policies on the server (A machine admin must perform these on the SQL Server)
  1. Open a command prompt with administrative permissions.
  2. Execute the following statement to enable auditing from SQL Server.
     auditpol /set /subcategory:"application generated" /success:enable /failure:enable
  3. Close the command prompt window.
        
• Set Security Policy
  1. Open a run prompt (Windows Key + R)
  2. Type msc and then click OK. If the User Access Control dialog box appears, click Continue.
  3. In the Local Security Policy tool, expand Security Settings, expand Local Policies, and then click User Rights Assignment. You must be a machine admin on the server to do this.
  4. In the results pane, double-click Generate security audits.
  5. On the Local Security Setting tab, click Add User or Group.
  6. In the Select Users, Computers, or Groups dialog box, either type the name of the domain account on which SQL Server runs, and then click OK, or click Advanced and search for the account.
  7. Click OK.
  8. Close the Security Policy tool.
  9. Restart SQL Server to enable this setting.

There are additional procedures for SQL Server 2008. Jonathan Kehayias has written an excellent article hereto help you with prerequisites for that version of SQL Server. Since the 2008 version is getting pretty old, I figured that it would be better to leave the steps out of this article to save space.

Creating the Server Audit

The first thing we need to do in order to install SQL server auditing is to create an audit. The create Server audit T – SQL statement looks like this:

use [Master]
go
--Create Server Audit
CREATE SERVER AUDIT SAMPLE_server_audit
TO
    SECURITY_LOG;
go
--Enable Server Audit
ALTER SERVER AUDIT SAMPLE_server_audit WITH (STATE = ON);
Go

All of the options that are available for this statement are at this Microsoft link.

Server Audit Specifications

Once we have a server audit in place, we need to create a server audit specification. This audit will capture operations such as database creation and drops, the creation, alteration or drop of logins, DBCC statements and other events that affect the overall security of the server. The T SQL that we used to create a server audit specification looks like this:

-- Create Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION SAMPLE_server_audit_spec
FOR SERVER AUDIT SAMPLE_server_audit
     ADD ( AUDIT_CHANGE_GROUP )
     ,ADD ( BACKUP_RESTORE_GROUP )
     ,ADD ( DATABASE_CHANGE_GROUP )
     ,ADD ( DATABASE_OWNERSHIP_CHANGE_GROUP )
     ,ADD ( BROKER_LOGIN_GROUP)
     ,ADD ( DBCC_GROUP )
     ,ADD ( LOGIN_CHANGE_PASSWORD_GROUP )
     ,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP )
     ,ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
     ,ADD (DATABASE_PERMISSION_CHANGE_GROUP)
     ,ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
go

Individual options are grouped into "action groups." Microsoft has a write-up on this statement at this link. Microsoft also has a complete list of the action groups that you can capture, and what they get you at this link. What I specify above are a good but not overwhelming subset of events.

Database Audit Specifications

For each database that we wish to audit, a database audit specification, similar to the server Audit Specification, must be created. As with the server audit specification, the database audit specification is linked to a server Audit. A database audit specification can be used to track any DDL statement issued against the database. This gives you a record of who created, altered or dropped any object within the database.

Furthermore, you contract DML statements to a specific table or of a specific type. This can be very useful to track activity again sensitive tables. The T SQL to create a database audit specification looks like this:

use [master]
go
--Create the database Audit spec
CREATE DATABASE AUDIT SPECIFICATION [master_dbaudit]
FOR SERVER AUDIT [SAMPLE_server_dbaudit]
       ADD ( AUDIT_CHANGE_GROUP )
       ,ADD ( BACKUP_RESTORE_GROUP )
       ,ADD ( DATABASE_CHANGE_GROUP )
       ,ADD ( DATABASE_OBJECT_CHANGE_GROUP )
       ,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )
       ,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )
       ,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )
       ,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
       ,ADD ( DBCC_GROUP )
       ,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )
       ,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )
       ,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
       ,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )
       -- 2012-newer allowed events
       ,ADD ( DATABASE_LOGOUT_GROUP )
       ,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
       ,ADD ( USER_DEFINED_AUDIT_GROUP )
       ,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )
WITH ( STATE = ON);
go

The action groups above are again a good example of what to use. We use a script that finds all databases on a server (except for model and tempdb) that don't have a database audit specification and uses those.

NOTE: There are several action groups that only arrived with SQL Server 2012. Our script notices if the server being audited is on 2008 and omits them.

The Microsoft documentation fully describing this statement is at this link. A complete listing of the action groups and what they track is given at this Microsoft link.

Understanding the Audit Output

First of all you need to know what to look for. All SQL server auditing events are stored with the event ID of 33205, making it extremely easy to filter away the millions of other security log entries that accumulate on any well configured server. Beware! Ensure that the systems administrator has increased the size of your security log to accumulate an appropriate number of entries so that auditing information is not lost. If you allow the logs to rollover, it is appropriate to consider some means of exporting the information to a text file for reference at a later date. If your server has HIPAA or SOX requirements it will be something that the auditors will ask you about. If you have the auditing, preserve the auditing!

Beware! SQL server audit log notes are big! The text associated with them can easily run over 1000 characters for a single event. Choosing an appropriate length of time to preserve event information will become extremely important as time goes on. Log forwarders such as Blanche usually charge by the number of bytes of data that they store. Archiving in perching becomes an issue.

Here's a look at a sample security log entry. I've blacked out private company information.

You can combine the Filter and Find features of the Event Viewer to search for specific things.

Utilizing the Audit

Picture this: The development team is hard at work on a critical project. All of a sudden the application crashed in the middle of a test. The report is that a stored procedure has gone missing. What happened? The developers pull out the problem resolution flowchart where every path ends at "Blame the Database and Call the DBAs." And you, the trusty DBA, are armed.

You sashay over to the Splunk console (or Event Viewer) and ask to search for events containing ID 33205 and the strings "Drop" and the procedure name. And you find out in under 30 seconds that Snidely Whiplash, a rookie developer, using workstation TieToTracks1, issued the drop from Management Studio. Even the time of the log entry precisely matches the failure. Root cause identified!

While young Mr. Snidely has some explaining to do, you and your boss breathe easy because DBA not only proved it had no responsibility for the problem, but rapidly aided developers in tracking down what happened.

Conclusion

This article is intended to be a primer to explain the power and basic implementation of SQL Server auditing. With a small amount of applied effort, you can install a worthwhile and effective tool to help you keep tabs on your servers and their databases.