Auditing is the process by which you
track events occurring within the database. These events could be
almost anything, including the creation of new users, the insertion of
data into a table, or the querying of data. The capability of auditing
has been around in some form for a few versions of SQL Server, but its
implementation was sketchy at best. Up until SQL Server 2008, there has
never been any formal user interface for auditing.
In the past, SQL Server
DBAs used Profiler, a performance tuning and optimization tool, to
create and manage audits. Alternatively, they could roll their own
auditing solutions using DDL triggers. Or they could forget the pain
and drop some money on third-party auditing solutions.
With the worldwide effort around defining and
enforcing compliance, SQL Server comes with native auditing support in
all editions of the product. The majority of regulatory compliance
agencies want some form of auditing, and chances are, if your company
is going through the process of becoming compliant, either you will be
involved with auditing via SQL Server or you’ll be aware of it
occurring using hardware-based auditing devices.
Auditing Objects
Auditing support in SQL Server consists of the following three objects:
- Server audit: Defines an abstract container, if you will,
representing a specific audit that you want to perform. For example,
you might want to create a compliance audit to ensure that you comply with a specific regulation.
- Server audit specification: Defines a specific, server-level
item to watch and record as part of an audit. Perhaps you want to
record failed logins as part of your compliance audit. You would create
a server audit specification for that purpose.
- Database audit specification: Defines a specific,
database-level item to watch and record as part of an audit. For
example, you might want to log stored procedure executions as part of
your compliance audit.
Figure 1 shows these three objects and the scope where these objects are defined.
Figure 1. The three auditing objects and their corresponding scopes
The concept of scope
is important to understand when you learn about auditing, because only
through having a good understanding of instance scope versus database
scope will you know which audit specification object to choose for a
given purpose. If you are talking about something affecting a
particular database, you are referring to a database-scoped object or
action. If you are interested in auditing failed logins, you would
create a server-instance–scoped audit specification, since logins
affect all databases.
The idea of an audit as an abstract
concept is also important to grasp. A server audit is an abstract
object—a container, if you will—containing one or more audit
specifications. When you enable or disable a specific audit, you are
really enabling or disabling all of that audit’s specifications.
Likewise, when you specify a location at which to record audit data,
that location applies to all specifications within an audit. This
container concept will get clearer as you read through the following
examples.
Server Audit Object
When you want to perform an audit, you have the
option to write audit data to one of three places. First, you can write
audit data to the Windows Application log. Next, you can write data to
the Windows Security log. Finally, you can write audit data to the file
system.
To create an audit object, navigate down the Object Explorer tree to the Security node.
Select New Audit from the Audits node context menu. This will launch the Create Audit dialog box, shown in Figure 2.
Figure 2. Create Audit dialog box
The audit name will be auto-generated for you,
but you can easily change the generated name to something simple like
Compliance Audit. A number of options are available when you create the
audit object. The first is the queue delay. Since auditing is
leveraging a service-broker–based eventing system called extended events,
it is possible to configure the maximum amount of time you are willing
to wait before auditing events are guaranteed to be written. Here’s
another way of thinking about the queue delay. If a meteor flew in from
the sky and crashed through the roof into our data center right through
our server and we had defined the queue delay to be 1,000, that meteor
may cause us to lose one second’s worth of auditing events, because the
queue delay is in terms of thousandths of a second, or milliseconds. If
the default queue delay of 1,000 milliseconds seems unacceptable, just
consider the performance impact of making the queue delay smaller.
In some cases, auditing is
so important that if SQL Server fails to write an audit event to the
log, the SQL Server service will be stopped. This is what happens when
you select the “Shut down server on audit log failure” option.
Selecting, “Fail operation” will cause the transaction that caused the
audit to be rolledback. The most unintrusive option is the default,
Continue, which simply raises an error in the event log if SQL Server
fails to write the audit event.
The “Audit destination” drop-down box allows
you to choose File, Application Log, or Security Log. Where you write
auditing information is extremely important. Auditing information can
contain sensitive information such as Social Security numbers,
salaries, and credit card numbers. Thus, you would not want to write
this information in a place where other users might unintentionally
have access to it. Writing to the Application log is easy, and you do
not need to have an elevated privilege within Windows to see the
Application log. A more secure solution would be to use the Windows
Security log. Additional measures are in place to restrict the number
of users who can see and erase the Security log.
If you do not plan on using another tool to
consume audit data, writing the audit data to the file system is the
best option. For one thing, it’s very fast, but more important, it is
possible to protect against repudiation attacks by the sysadmin. A repudiation attack is where a malicious user does something they shouldn’t and cleans up after themselves so their actions can’t be tracked.
Selecting a file destination also enables a
series of other options. Since it’s the file system we are writing to,
you can specify the maximum file size an audit can be as well as
reserve the space up front so you are sure to always have enough room
for the audit data.
You can also create the audit object via DDL. An example of this is as follows:
USE MASTER
GO
CREATE SERVER AUDIT [Compliance Audit]
TO FILE
( FILEPATH = N'c:\audit'
)
WITH
( QUEUE_DELAY = 1000,
ON_FAILURE = CONTINUE
)
GO
Auditing can generate a massive number of
events in a very short time. In most cases, we may not be interested in
auditing everything but rather a subset of events based on a certain
condition. Assume for a moment that we have an application that
connects to SQL Server under a common SQL login, AppUser. Users who use this application do not directly connect to SQL Server but access the database via the AppUser
account from the application itself. Our auditing requirements state
that we are interested in auditing anyone that connects or tries to
connect with an account other than AppUser. To do this,
we simply add the search criteria or filter to the audit definition. In
SSMS, the Create Audit and Audit Properties dialog has a Filter tab;
alternatively, you can specify a filter for the above DDL as follows:
USE master
GO
ALTER SERVER AUDIT [Compliance Audit]
WHERE server_principal_id <> 268
GO
This filter will ignore any audits generated by the server_principal_id of 268. The number 268 is just an example. If you are filtering by the server_principal_id, the actual ID number of the principal you want to filter may be different. You can use the system view sys.server_principals to see a list of server principal IDs. A sample of using this view follows:
SELECT name,principal_id,type_desc FROM sys.server_principals
This view contains 13 columns including
information on when the principal was created and modified last. For
this discussion, the name, principal, and type_desc fields are most interesting. Following are some of the query results:
Name principal_id type_desc
------------------------- ------------ -------------
Sa 1 SQL_LOGIN
Public 2 SERVER_ROLE
Sysadmin 3 SERVER_ROLE
…
SQL2012-RC0\Administrator 259 WINDOWS_LOGIN
…
Test 267 SQL_LOGIN
Here, you can see from the type_desc field what kind of principal each item is.
