There are a good many third-party auditing tools for SQL Server on the market. But what if you lack the budget to help the vendor's salesman pay for his second Ferrari? Or what if your company has standardized on a log forwarding tool like Splunk? Microsoft has heard your cry. Starting with SQL Server 2008, Microsoft has built some fairly excellent auditing into SQL Server.

Why audit anything? Who needs it?

Why do we need to be "big brother" over the data? Because we are the Guardians of that Data. Think of SQL Server Auditing as the highly intelligent genetically-modified raccoon that keeps an eye on what is happening. SQL Server audit specifications can be set up at the server and the database level to track the activities in which you may be interested: Creating, altering and dropping logins, database users, DBCC execution, even data operations on certain tables.

So if Bob, the disgruntled IT guy, gains access, creates a login, accesses the payroll table, drops the login and tweets your boss' salary, you can provide the paper trail to your fuming boss. Or if he does it and finds that the boss' name and salary is encrypted using SQL 2016 Always Encrypted, you can report the attempted breach to your appreciative boss, who will marvel at your diligence.

SQL Server Auditing was recently a feature available only in Enterprise, Evaluation and Developer editions, but as of SQL Server 2016 Service Pack 1 (SP1), it has been available in Standard Edition as well. It imposes a microscopic performance hit, an expense well worth the investment, to keep a close eye on your data assets.

What Does Auditing Give You?

You can receive output as a flat file. This may have advantages for some. However, the log file can only be read through Management Studio or with a built-in function named sys.fn_get_audit_file. Therefore, it is limited in its usefulness.

I prefer to have the audit information placed into the Windows Security Event Log. These logs may be written to if SQL Server has the appropriate permissions set up; I show how to do that below. Log entries stored in this manner may be read using event viewer, exported to a text file, and or easily accumulated by a log-forwarding tool. In our shop, we have many servers to protect. We also have a high-quality log aggregator with Splunk.

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.

In order to set the server Audit Policies, follow the following steps. Note that a Windows administrator account must be used to perform these.

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.

Next we want to set a 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 here to 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 object at the instance level. 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, we create a database audit specification. 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 can track 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, this 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 Splunk, usually charge by the number of bytes of data that they store. Archiving and purging 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 crashes 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.