Lets face it the role of the DBA keeps expanding. Its not just about the Database it also now includes things like security and compliancy. Yes I agree the DBA was always worried about the protection or recoverability of the data but it goes way beyond that now. To take a line from a previous blog entry…
If you are publicly traded you fall under Sarbanes Oxley act,(Public Company Accounting Reform and Investor Protection Act of 2002). If you are in healthcare you fall under the healthcare Health Insurance Portability and Accountability Act (HIPPA). If you process credit cards you must meet the requirement of the Payment Card Industry (PCI) Compliance. New compliancy requirements come up all the time.
Massachusetts just passed a law that could affect your business. This new 201 CMR 17.00: Standards for The Protection of Personal Information of Residents of the Commonwealth comes into effect in 2009.
To read the entire blog…..
SQL Server 2008 Compliance Guide (Sarbanes, Hippa)
well this blog is about Microsoft SQL Server 2008 Auditing. Microsoft just releases a new white paper on it. I have included a copy of it here. At the beginning & end will be a link back to the source. A place I would check for any updates or corrections.
Auditing in SQL Server 2008
Auditing in SQL Server 2008
SQL Server
Technical Article
Writer: Il-Sung Lee, Art Rask
Technical Reviewer: Jack Richins, Rick Byham, Sameer
Tejani, Al Comeau, JC Cannon
Published: February 2009
Applies to: SQL Server 2008
Summary: With SQL Server Audit, SQL Server
2008 introduces an important new feature that provides a true auditing solution
for enterprise customers. While SQL Trace can be used to satisfy many auditing
needs, SQL Server Audit offers a number of attractive advantages that may help
DBAs more easily achieve their goals such as meeting regulatory compliance
requirements. These include the ability to provide centralized storage of audit
logs and integration with System Center, as well as noticeably better
performance. Perhaps most significantly, SQL Server Audit permits fine-grained
auditing whereby an audit can be targeted to specific actions by a principal
against a particular object. This paper provides a comprehensive description of
the new feature along with usage guidance and then provides some practical
examples.
Introduction
A key part of
any data security strategy is the ability to track who has accessed, or
attempted to access, your data. This provides the ability to detect
unauthorized access attempts or, if necessary, to piece together the actions of
malicious insiders who misused their legitimate access. Furthermore, a rich and
robust tracking capability can provide oversight of sensitive configuration
changes made by administrators.
Such
considerations are ever more relevant in today’s information economy. Data is
collected, stored, used, and misused at an ever increasing rate. Governments
and private sector organizations around the world are responding to this by
establishing various compliance regimes to improve the stewardship of data by
those who hold it. A few of the most widely known examples include:
- European
Union Data Protection Directive, a strict data protection policy with
implications across the EU and the global economy.
- HIPAA,
or Health Insurance Portability and Accountability Act, part of United States
law
- Sarbanes-Oxley,
part of United States law governing corporations.
- Payment
Card Industry Data Security Standard, mandated by major credit card companies,
with worldwide implications.
These formal
regulations affect organizations of all sizes, in all industries, around the
world. They place significant pressure on organizations to ensure their IT
platforms and practices are compliant. And ultimately, these requirements land
at the feet of the DBAs, developers, and IT professionals who manage the data.
It is
important that a data management platform provide the means to meet these
requirements, and do so efficiently. To address these needs, SQL Server 2008
introduces a rich and deeply integrated auditing capability that offers major
improvements over previous versions of the Microsoft® SQL Server® database
software.
This paper
will review the new audit features of SQL Server 2008, compare them to past
versions, and walk through some implementation examples.
Auditing
in SQL Server 2005
In SQL Server
2005 and earlier versions, auditing was implemented using a combination of
tools. At the server level, options were available to log successful and/or
failed logins to the Application log in the Windows® operating system and the
SQL Server Error Log. Login triggers, server triggers, and DDL triggers were
available to do custom auditing of specific types of events. The tool of choice
for detailed activity audits, however, was SQL Trace. SQL Trace is a mechanism
for monitoring a broad range of events internal to the SQL Server database
engine. It is used for detecting deadlocks, monitoring application performance,
debugging, and many other development or administrative purposes. It is useful
as an audit tool because it can capture individual statements as they are
executed, including the user id.
SQL Trace has
limitations as an auditing tool, though. Configuring and managing traces
requires the use of a separate tool, the SQL Server Profiler. This is a tool
well-loved by many developers and administrators, but it is not integrated with
SQL Server Management Studio, the primary SQL Server administrative interface.
And, its interface is not focused on the specific task of creating and managing
information audits. At the Transact-SQL level, traces are configured by a set
of system stored procedures which take opaque numeric codes as arguments – not
the most inviting of management APIs.
Trace
functionality in SQL Server 2005 is also accessible programmatically via Server
Management Objects (SMO). However, the SMO trace classes are dependent on
binary trace definition files (.tdf) authored by SQL Server Profiler, so they
do not really serve as a standalone API for managing server audits.
Finally,
because SQL Trace is a multipurpose tool, it doesn’t always easily answer the
questions an auditor would care about. When used as an audit tool, SQL Trace
captures statements. But this does not necessarily expose at a glance what
information a user read or modified. Statements might reference views, stored
procedures, or user-defined functions, in which case further analysis is
required when the real question is “who read data from this table?”
All the tools
described above remain in SQL Server 2008, and they will continue to be useful
for a wide array of needs. But SQL Server 2008 brings a new, more focused, and
more deeply integrated auditing capability to the table.
Auditing
in SQL Server 2008
SQL Server
2008 Enterprise enhances the auditing capabilities of the server by introducing
native auditing functionality into the database engine. The new SQL Server
Audit feature maintains all the capabilities of the SQL Server 2005 auditing
solutions and provides enhancements such as flexibility in audit data targets
and granular auditing.
Overview
The SQL
Server Audit feature in SQL Server 2008 is intended to replace SQL Trace as the
preferred auditing solution. SQL Server Audit is meant to provide full auditing
capabilities and only auditing capabilities, unlike SQL Trace which also serves
for performance debugging. As a result, SQL Server Audit was designed with the
following primary goals in mind:
- Security
– The audit feature, and its objects, must be truly secure.
- Performance
- Performance impact must be minimized.
- Management
– The audit feature must be easy to manage.
- Discoverability
- Audit-centric questions must be easy to answer.
The
subsequent sections discuss how each one of these design goals is realized.
As mentioned
earlier, SQL Server Audit is now native to the server. Doing so affords the new
SQL Server Audit feature performance benefits and allows the audit objects to
be treated as first class database objects. Being a first class object means
that the audit objects can be created and manipulated through Transact-SQL DDL
statements (or through the SQL Server Management Studio if you prefer).
Furthermore, the audit objects will be secured through the database’s familiar
permission model.
When an audit
object is created, the destination for audit events must be specified. The
typical target is a file which is probably suitable for most cases. However, in
SQL Server 2008, the audit events can also be written to the Windows
Application log. This facilitates access to the audit information in some situations
and can allow consolidation through management applications. In addition, the
very security conscious will be pleased to learn that the Windows Security log
can be specified as a target for Audit (starting with the Windows Server® 2003
operating system). This is significant in that the Windows Security log is
considered to be resistant to tampering and nonrepudiation. Another benefit of
using the Security log is that the Audit Collection Service (ACS) of the
Microsoft System Center Operations Manager can be used to securely collect
audit information from the security logs of multiple machines and generate
consolidated reports.
A final, but
important, point to mention about SQL Server Audit in SQL Server 2008 is the
granularity at which the audit can be specified. The auditing of activity of
users, roles, or groups on database objects can be restricted down to the table
level. That is, you can target SQL Server Audit to track specific activities of
a user or users down to the individual table level. For example, SQL Server
Audit allows a record to be made of all the UPDATEs to the Payroll table by DBO.
Security
Auditing is a
central component of a database security strategy and as such the SQL Server
Audit feature itself must be highly secure and securable. Because it is a
database object, the privilege to create, delete, or modify an audit object is
controlled by the database engine’s permission model and enforcement control.
The SQL Server Audit feature introduces a new server-level permission called ALTER
ANY SERVER AUDIT to allow a principal to CREATE, ALTER, or DROP an Audit or
Server Audit Specification object. Likewise, a database-level permission called
ALTER ANY DATABASE AUDIT is introduced to allow a principal to CREATE, ALTER,
or DROP a Database Audit Specification object. An audit specification can
briefly be described as an object that describes the activity to capture in the
audit (a more thorough description is found later in this paper).
The audit log
is obviously another asset that needs protection. Careful consideration must be
given regarding where to send the audit data. Ideally, the audit information
should be sent to a location that cannot be modified or tampered with, even by
a sysadmin. A simple strategy to accomplish this is to send the audit events to
a file on a share to which the SQL Server service account only has write
access. The person in the auditor capacity would obviously need read access to
this file but no one else. Also, if the share is on a different computer, it’s
possible to obtain protection from the Windows administrator (of the SQL Server
computer) as well, although there are obviously some performance considerations
and vulnerability to network outages. Another option is the Windows Security
log. This is a good choice for someone who wants to store the SQL Server Audit
log locally but wants protection from the sysadmin and even the local Windows
administrator. One final point – SQL Server does not encrypt or otherwise
protected the Audit log. Such additional layers of protection can be applied,
of course, through a variety of means external to SQL Server, such as file
system encryption.
If, for
whatever reason, SQL Server Audit is unable to write its audit events to the
audit target, you can configure the audit object to shut down the server
instance. While shutting down the server instance may seem drastic, it is a
necessity for certain scenarios, such as meeting the requirements of the Common
Criteria to ensure that the server cannot operate without its activity being
audited. To configure the audit to behave in such a manner, the person issuing
the CREATE or ALTER AUDIT DDL must additionally have the SERVER SHUTDOWN server
permission. If the server cannot start because of SQL Server Audit, it is
possible to bring the server up by starting SQL Server with the -m trace flag; SQL Server Audit itself
will not be deactivated and only the shutdown behavior will be disabled. Please
note that -m starts SQL Server in
single-user mode and is meant to allow a DBA to start the database and make
changes to the SQL Server Audit configuration if necessary. Alternatively, the -f trace flag can be used to start SQL
Server in minimal configuration mode, in which case Audit will be disabled but
Audit DDL can still be issued. In practice, -m should be sufficient for most cases that require SQL Serve Audit
reconfiguration.
If a failure
to write the Audit event does not trigger the SQL Server instance to shut down,
what happens with the audit records? The answer is that Audit events are buffered
in memory until they can be flushed to the target. If the records fill the
memory buffer and cannot be written to the Audit log, the server blocks any new
activity that would result in an audit event being written until the buffer
space is freed up or the audit is disabled. The size of the memory buffer
varies, but it is around 4 MB per audit in the default case, which can
accommodate at least 170 audit events (the exact number depends upon the amount
of data contained in each event). If the problem is not correct before the
operating system returns an error, such as a disk write failure, the Audit
session is taken offline with a corresponding error written to the server’s
error log; all buffered and new audit events are discarded. Upon correction of the
problem, the audit object will need to be restarted in order for auditing to
resume. If lost audit records are unacceptable, the audit should be configured
to shut down rather than continue upon write failure. To ensure tight
synchronicity between the events captured in the audit log and the activity on
the server, SQL Server Audit can be configured to write the audit entries to
the log in a synchronous fashion, meaning that transactions are blocked until
the event is written to its destination. The tradeoff here is obviously that
performance may be affected adversely. In most situations, asynchronous Audit
log writing is recommended. As described below, the length of time before
writes occur can be configured; increasing the time value from the default of
one second can improve the performance of the server.
One last
point to highlight is that any changes to the state of the server audit object
itself are recorded in its audit log. This is a shortcoming of SQL Trace, and
it is an important feature because it can potentially capture any attempts to
circumvent the audit. Assuming that the audit events are sent to a target that
cannot be modified by the SQL Server service account, this feature can
effectively protect the audit log from the DBA. Furthermore, it is possible to
configure SQL Server Audit to record changes to all audit objects defined
within the instance, not just the immediate server audit object (this can
accomplished by adding the AUDIT_CHANGE_GROUP to the server audit
specification; this will become more clear later in this document).
Performance
SQL Server
2008 introduces a new high-performance eventing infrastructure called SQL
Server Extended Events. The SQL Server Audit feature is built on top of
Extended Events to leverage the performance benefits and provide both
asynchronous and synchronous write capabilities (by default, SQL Server Audit
uses the asynchronous eventing model). One thing to note is that the Audit
event is a protected type of Extended Event so the CREATE/ALTER/DROP SESSION EVENT
commands cannot be used to manipulate SQL Server Audit directly. For more
information about Extended Events, see SQL Server Books Online.
By default,
the audit events are written to the audit target in an asynchronous fashion for
performance reasons. When tighter guarantees of audit records being written to
the audit log are required, you can select synchronous write, with the
understanding that some amount of performance degradation should be expected
(the exact amount depends on the amount and type of audit records generated).
The choice of asynchronous or synchronous is controlled by the QUEUE_DELAY
option of the CREATE AUDIT DDL (explained below); by default, the value is
1000, which indicates that SQL Server Audit will queue the audit record for up
to one second before writing to the target. If tolerances allow, you can set
this value to a larger number, which can increase performance; the tradeoff is
that activity recorded in the log might be increasingly out of sync with the
actual database activity, and there is more potential for lost records if a
fatal error occurs on the server. To enable synchronous logging of SQL Server
Audit, set the QUEUE_DELAY to the value 0 (zero).
Targeted
auditing can also aid in minimizing the performance impact. The obvious
strategy is to limit auditing to the exact set of actions of interest and
nothing more. The fine-grained auditing capabilities of SQL Server Audit allow
auditing to be configured so that it targets specific actions, principals, and
objects (down to the individual table level). As a result, resources are not
wasted generating unwanted audit information. This is in contrast to SQL Trace,
which typically generates a lot of records and relies upon post-filtering to
provide targeted auditing.
Because of the
architecture of SQL Server Audit, the best performance is generally achieved by
using a file as the audit target. However, the performance difference between
the different audit targets is highly dependent upon the audit configuration,
among other things.
Management
The audit
features of SQL Server 2008 are fully manageable using the main management
interfaces of the product, specifically SQL Server Management Studio, Server
Management Objects (SMO), and Transact-SQL DDL.
In SQL Server
Management Studio, server level audit configuration is available in the user
interface under the Security folder. The two relevant subfolders are Audits and
Server Audit Specifications. Database-level audit configuration is available
under the Security folder of each database, in the Database Audit Specification
subfolder.
In contrast
to previous versions, the audit configuration can be fully managed
programmatically via SMO, using objects in the
Microsoft.SqlServer.Management.SMO namespace.
Finally,
rather than the system stored procedures used to configure SQL Trace, SQL
Server Audit uses clear SQL DDL syntax to create, manage, and secure audits.
Discoverability
Audit data
can be written to a binary file or to the Windows Event log (Application or
Security). When written to a file, the audit data is accessible through a
built-in table-valued function, which allows the use of regular SELECT syntax
to query the audit trail. Events written to the Event log can be accessed with
Event Viewer or with specialized Event log management software, such as
Microsoft Systems Center Operations Manager. Accessing and analyzing audit data
is discussed in more detail later in this paper.
Audit Value of Data
Besides
providing a robust infrastructure for collecting audit data, SQL Server 2008
makes it easier to meet specific audit requirements. Audit records are captured
based on permission checks on database objects. This means, for example, that a
SELECT on table A is recorded as
such, regardless of whether the actual SQL statement referenced table A, a view, a stored procedure, or
another object.
Also, SQL
Server 2008 allows granular definition of audit criteria. Audits can be scoped
to individual tables, to specific DML actions (for example, DELETE vs. SELECT)
and to specific principals. This granularity can reduce the volume of audit
data that must be stored and analyzed to meet a specific set of requirements.
Technical Overview of Auditing
To understand
the setup and management of auditing in SQL Server 2008, it is best to start
with the three main objects which describe audits.
The Server Audit object describes the
target for audit data, plus some top-level configuration settings. Think of a
Server Audit object as the declaration of the audit sink, or destination. This
destination can be a file, the Windows Application log, or the Windows Security
log. The allowable delay for writing events to the destination can also be
configured on this object. Note that the Server Audit object contains no
information about what is being audited – just where the audit data is going. Multiple Server Audit objects can be
defined with each object being specified and operational independent from one
another.
The Server Audit Specification object
describes what to audit. As its name
suggests, this object is focused on server instance-wide actions. A server
audit specification is associated with a server audit in order to define where
the audit data is written. There is a one-to-one relationship between the
Server Audit Specification object and the Server Audit object.
The Database Audit Specification also
describes what to audit. But, as its
name suggests, this object is focused on actions which occur in a specific
database. Where the audit data is written is defined by the association of a
database audit specification with a Server Audit object. Each database audit
specification can be associated with only one server audit. A server audit, for
its part, can be associated with only one database audit specification per
database.
The
interaction of these objects to define an overall audit regime is depicted in
Figure 1 below.
All the
relationships between objects in Figure 1 are optional. Of course, without a
minimum of one server audit and one specification (server or database), an
audit cannot produce any data. All of these objects – Server Audit, Server
Audit Specification, and Database Audit Specification – can be fully managed
with DDL, the SQL Server Management Studio administrative interface, or SMO.
Let’s drill
down on the details of each of these parts of an audit definition.
Figure 1: Audit object layout
Server Audit
Server audits
are created with the CREATE SERVER AUDIT statement. Besides a name, the most
important thing specified when the server audit is created is the destination
for the audit data. In SQL Server 2008, the available destinations are a binary
file, the Windows Application log, and the Windows Security log (specific
permissions – discussed later – are required of the SQL Server service account
in order to write to the Security log). For file destinations, the user must
provide at least the file path and optionally, the maximum size and the maximum
number of rollover files. SQL Server names audit files automatically, according
to the following pattern:
<audit_name>_<audit_guid>_nn_<timestamp_as_bigint>.sqlaudit
When the
active audit file reaches the maximum size specified, it automatically rolls
over to a new file. This process continues up to the maximum number of rollover
files specified by the MAX_ROLLOVER_FILES argument. When or if the maximum
number of rollover files is reached, SQL Server begins deleting an existing
audit file for every new one created, starting with the oldest first. Note that
if the delete operation fails for whatever reason, the audit silently
continues.
Optionally,
the audit can reserve the maximum disk space on startup. Naturally, the SQL
Server service account must have write permissions on the destination folder.
To provide
control over security-performance tradeoffs, a queue delay can be configured on
the server audit. If this value is 0, audit records are written synchronously
with the action that generated them. Alternatively, a queue delay in
milliseconds can be specified, to lessen performance impact under load. The
minimum delay is 1,000 milliseconds (the default).
To meet the
most stringent security requirements, a server audit optionally can be
configured so that SQL Server will shut down if the audit fails. For example,
if the destination drive for the audit log becomes unavailable, SQL Server
shuts down and can be restarted manually with the –m command switch unless the root cause of the shutdown is
corrected.
By default,
server audits are created in a disabled state. This can be overridden in DDL so
that the audit is enabled immediately. In most cases, however, administrators
will want to fully configure the audit before enabling it.
A server
audit is automatically tagged with a GUID which uniquely identifies it. To
support distributed scenarios such as database mirroring, the CREATE SERVER
AUDIT statement allows this GUID to be assigned explicitly. After the audit is
created, the GUID cannot be changed.
The following
DDL example shows the creation of an audit named PCI_Audit, which will write to
a file with no delay, and force the server to shut down in the event of a
failure to write to the file.
CREATE SERVER AUDIT PCI_Audit
TO FILE (FILEPATH = ’F:\AuditLogs\’, MAXSIZE = 1GB, MAX_ROLLOVER_FILES = 80)
WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN)
This server
audit is disabled initially.
The server
audit can be modified after creation by using the ALTER SERVER AUDIT statement.
The following example changes the destination to the Windows Application log
and relaxes the queue delay and failure settings.
ALTER SERVER AUDIT PCI_Audit
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
The ALTER
SERVER AUDIT statement is also used to enable or disable a server audit.
ALTER SERVER AUDIT PCI_Audit
WITH (STATE = ON)
A server
audit can only be enabled or disabled outside of the scope of an active transaction.
In SQL Server
Management Studio, server audits are created and managed in the Audits folder,
which is under the server-level Security folder.
Figure 2: Creating an audit in SQL Server
Management Studio
Permissions
Actions on a
server audit (CREATE, ALTER, or DROP) require the ALTER ANY SERVER AUDIT
permission, which is covered by the CONTROL SERVER permission.
Writing to the Windows Security Log
Writing to
the Windows Security log deserves special mention, because there are some extra
considerations to take into account. Because writing to the Windows Security
log is a very privileged operation, the SQL Server service account needs to be
granted the SeAuditPrivilege (also known as “Generate Security Audit”)
privilege. This can be accomplished though the Local Security Policy tool
(secpol.msc). Just add the service account to LocalPolicies/User Rights Assignment/Generate Security Audits.
In addition,
the audit policy granularity for Windows must be modified before the SQL Server
entries can be logged. On Windows Server 2003, use the Security Policy Tool to
modify the Local Policies/Audit
Policy/Audit Object Access setting; both ‘Success’ and ‘Failure’ should be
enabled. On the Windows Vista® operating system and Windows Server 2008, use
the auditpol command-line tool and issue the command auditpol /set
/subcategory:”application generated” /success:enable /failure:enable. Please
note that in some domain environments, the domain policy may override these
settings, and assistance from the Domain Administrator will be required in this
case. Users who install SQL Server 2008 Developer onto a Windows XP computer
will not be able to write the audit information to the Windows Security log
because this capability is not supported by the operating system.
Server Audit Specification
Server audit
specifications can be created with the CREATE SERVER AUDIT SPECIFICATION
statement. A server audit must exist before a server audit specification can be
created, since the DDL requires that an audit name be specified.
The main
content of a server audit specification is the list of action groups that it
will audit. Auditable actions are collected into groups and cover server-level
activity such as server configuration changes and login/logout. These groups
are hardwired, and they are detailed in the SQL Server documentation.
The following
statement creates a server audit specification to track backup/restore events,
SQL Server service starts and stops, and changes in the membership of server
roles.
CREATE SERVER AUDIT SPECIFICATION PCI_Server_Mgmt_Spec
FOR SERVER AUDIT PCI_Audit
ADD (BACKUP_RESTORE_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBERSHIP_CHANGE_GROUP)
WITH (STATE = OFF)
Server audit
specifications can be reassigned to a different server audit after they are
created by using the ALTER SERVER AUDIT SPECIFICATION statement. This statement
also allows the list of audited action groups to be changed and the state to be
toggled between enabled (ON) and disabled (OFF).
The following
example adds an action group to the specification created in the previous
statement.
ALTER SERVER AUDIT SPECIFICATION PCI_Server_Mgmt_Spec
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
Change to the
state of an audit (enabled or disabled) can only be made outside the scope of a
transaction. Also, if the ALTER SERVER AUDIT statement changes the state from
ON to OFF, it may not include any other changes within the same statement.
In SQL Server
Management Studio, server audit specifications are created and managed in the
Server Audit Specifications folder, which is under the server-level Security
folder.
Permissions
Actions on a
server audit specification (CREATE, ALTER, or DROP) require the ALTER ANY
SERVER AUDIT permission, which is covered by CONTROL SERVER.
Database Audit Specification
Database
audit specifications are much like server audit specifications, but there are
some significant differences. As the name implies, of course, database audit
specifications pertain to the auditing of database level actions. As with
server audit specifications, a predefined list of audit action groups can be
used to define what the specification tracks.
In addition
to this, however, database audit specifications allow more specific audit
actions to be tracked. This is where granular actions within the database can
easily be targeted. Before exploring the details of this, examine the following
statement.
CREATE DATABASE AUDIT SPECIFICATION PCI_Txn_Database_Spec
FOR SERVER AUDIT PCI_Audit
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SELECT ON dbo.Customer BY dbo),
ADD (INSERT ON dbo.Customer BY dbo),
ADD (UPDATE ON dbo.Customer BY dbo),
ADD (DELETE ON dbo.Customer BY dbo),
ADD (EXECUTE ON dbo.usp_SubmitPO BY public)
This
specification tracks all actions that fall into the
DATABASE_OBJECT_PERMISSION_CHANGE group, which is a useful thing to monitor if
you have multiple administrators working in your database. In addition, it also
tracks specific DML actions against the Customer
table by the dbo user, and any execution of the usp_SubmitPO stored procedure. Also note the use of the keyword
public to track changes by all users.
In SQL Server
2008, this granular action definition is a key distinction of database audit
specifications from their server-level counterparts. The general form for
defining an action to be audited is:
<action>
ON <object> BY <principal>
Auditing
occurs regardless of the means by which the action was taken. In other words, a
SELECT on the Customer table will be
audited if the user action was a SELECT statement on Customer, a SELECT on a view that queries the table, an EXECUTE of
a stored procedure that queries the table, and so on.
Note that
<object> in the above format can refer to an object like a table, view,
or stored procedure, or an entire database or schema. For the latter cases, the
forms DATABASE::<db_name> and SCHEMA::<schema_name> are used,
respectively.
Specifying a
principal allows the audit to be narrowly focused, thereby minimizing the
amount of data captured in the audit. In order to audit an action for all
principals, the public role can be specified. For example, the following action
records all SELECTs on all objects within the MyDB database by any user in the
MyDBRole role.
SELECT ON DATABASE::MyDB BY MyDBRole
As with
server-level specifications, a database audit specification can be enabled or
disabled at creation, or afterwards with the ALTER DATABASE SERVER
SPECIFICATION statement. The ALTER statement also allows modification of the
list of audited actions and action groups.
In SQL Server
Management Studio, database audit specifications are created and managed in the
Database Audit Specifications folder, which is under the database-level
Security folder for the database in question.
Permissions
Actions on a
database audit (CREATE, ALTER, or DROP) require the ALTER ANY DATABASE AUDIT
permission, which is covered by CONTROL DATABASE.
More on the Technical Architecture of Audit
Before we
move into how audit data is read and used, a few more technical aspects of the
implementation are worth mentioning. Earlier, it was stated that SQL Server
Audit is native to the server. There are many aspects of this feature that are
more tightly bound to the DBMS, not the least of which is the way in which
audit events are triggered. Auditing in SQL Server 2008 is implemented by
hooking the internal permissions checks, which occur inside the database
engine. When a permission check occurs, SQL Server Audit has the opportunity to
produce an audit record, if a relevant audit is specified. This means all forms
of access to an object are audited.
A second
aspect of SQL Server Audit that should be noted is its behavior within
transactions. There are two pieces to this. The first is how operations on an
audit itself interact with transactional boundaries. All audit-related DDL
statements, including the CREATE SERVER AUDIT statement, can be issued within
an active transaction unless they trigger changes in the state of an audit
object. If within a transaction, the creation of the audit will be scoped
within the transaction – it will commit or roll back with the transaction.
Another piece
is how auditable events are recorded when they occur within a transaction. The
simple way to describe this is: They are always audited. Clearly there would be
negative security implications if data could be read, but no audit was recorded
because a pending transaction was rolled back. So, the recording of audit
events occurs irrespective of transaction scope and whether a rollback occurs
or not.
Using Audit Data
This section includes information about the ways in which
you can use and apply the data generated by SQL Server Audit.
Audit Files
When a SQL
Server 2008 server audit is configured with the audit destination of File
option, the audit data is written to a binary file on disk. The target location
can be a local folder or a UNC path to a network share.
Although the
server audit can specify the path where the audit file will be created, the
file name is automatically generated by SQL Server. The file name pattern is:
<audit_name>_<audit_guid>_nn_<timestamp_as_bigint>.sqlaudit
In this
pattern, nn is a sequential partition
number that is incremented as new audit files are generated, if the audit is
configured to allow rollover to new files.
The audit
file may be written to a folder protected by NTFS encryption, provided the SQL
Server service account has the appropriate permissions. Writing to a folder
with NTFS compression is also permitted. In both cases, of course, some
performance impact should be expected. If the target location for the audit
file is a remote file share, the audit content is not encrypted when travelling
across the network.
As mentioned,
the audit file is in a binary format and is not readable with a text editor.
SQL Server provides a table-valued function called fn_get_audit_file(), which
is used to read audit data. This function takes the arguments indicated in
Table 1.
Table 1: Arguments to fn_get_audit_file()
The following
statement reads all audit files for a specific audit.
SELECT * FROM fn_get_audit_file(
'D:\Audits\MyAudit-_C26128D1-F97B-4B82-9E47-B6A296045B05_*.sqlaudit',
default, default)
The following
example reads all audit files in the E:\SqlAudits\ folder.
SELECT * FROM fn_get_audit_file('E:\SqlAudits\*', default, default)
And this
example reads all audit records in a specific audit, starting from a known
offset.
SELECT * FROM fn_get_audit_file(
'D:\Audits\MyAudit-_C26128D1-F97B-4B82-9E47-B6A296045B05_*.sqlaudit',
'D:\Audits\MyAudit-_C26128D1-F97B-4B82-9E47-B6A296045B05_0_128439520854140000.sqlaudit',
5120)
Incidentally,
the initial_file_name and audit_file_offset parameters can be determined by
looking at the file_name and audit_file_offset columns respectively from the
output of the last invocation of fn_get_audit_file().
Naturally,
because this is a SELECT from a table-valued function, a WHERE clause, ORDER BY
clause, and other standard SQL SELECT syntax can be used to shape the result
set.
Because the
fn_get_audit_file() function takes an explicit path as an argument, it is
possible to move inactive audit files to a new location and read them using
this function. At this point the audit file is just static data. Thus audit
files can be moved and queried at will after they are no longer receiving audit
events. A useful implication of this is that audit files from multiple servers
can be consolidated in a single folder and queried as a set. In this way,
administrators or auditors can easily look for key events across a distributed
set of SQL Servers.
The
fn_get_audit_file() function returns a rich set of information captured by SQL
Server Audit. More than two dozen columns are available, providing details in
several areas, such as:
- The
date and time of the event, and whether it succeeded (or permissions were
denied)
- The
user context of the event (principal id and name at server and database level)
- The
type of action (for example, SELECT, EXECUTE, DELETE)
- The
target object
- The
connection context (server and instance, database, schema)
- The
actual Transact-SQL statement that triggered the audit event (if applicable)
- The
audit file name and the position of the audit record in the file
- Additional
XML information for some special events, such as audit management events
As is
apparent, a great deal of information is available about each audited event.
One of the columns, statement,
contains the actual text of the action that caused the audited event, although
the actual data values in the statement might not be displayed if the statement
was parameterized. Note that this may not directly name the audited resource.
For example, an audited SELECT event on Table1
might be caused by a SELECT on View1, which references Table1.
The statement field is nvarchar and is 4,000 characters in length. In the event that a
statement exceeds this size, SQL Server 2008 generates multiple audit records,
spreading the statement text across as many records as necessary to capture the
full content. A sequence number field in the audit records enables the full
statement text to be reassembled.
The
fn_get_audit_file() function is your jumping-off point to implement the best
audit analysis solution for your environment. Because it essentially presents
the audit data as a table, it provides the means to query, mine, archive, or
report on the audit logs in any way necessary. In this sense, it is perhaps the
most flexible of the three possible targets for audit data.
The Windows Event Log
A SQL Server
2008 audit can alternatively be configured to log to the Windows Application
log or the Windows Security log. This can be valuable for a few reasons:
- The
Windows event log is a familiar location and format for many server
administrators and auditors.
- The
Security log has inherent restrictions on writing, tampering, and viewing,
which make it attractive as a repository for audit information.
- There
are existing Microsoft and third-party solutions aimed at consolidating,
monitoring, and alerting on Windows event logs. For example, Microsoft System
Center has a component called Audit Collection Services that can selectively
consolidate Security event logs from many servers. And the System Center
Operations Manager product is dedicated to monitoring, reporting, and alerting
on Windows event logs.
When a server
audit is configured with the TO APPLICATION_LOG or TO SECURITY_LOG option, all
audit events are written to the relevant log. The events contain the same
information as is returned by the fn_get_audit() table-valued function for
audits to file. In the event log, this information is written to the event body
in text format.
For users who
intend to set up monitoring or alerting on event log entries, it is important
to know what event log specific attributes will be assigned to SQL Server Audit
entries. All SQL Server Audit events are logged with the SQL Server instance
name as the event source. The event id is 33205. The description of the event
is where distinguishing characteristics of events can be found. So, to
implement a filtering or alerting on audit events, the Event log management
tool used will need to support identification of events based on the
description for example, regular expression searches).
Performance Comparison
Using a
sample of OLTP workloads typical of a customer environment, the performance of
SQL Server Audit was measured and compared to SQL Trace where possible. The
results consistently indicated a performance advantage for SQL Server Audit
with improvements ranging from 11% to 45%. The following list provides a short
profile of the workloads used and the subsequent table reveals values for a
sample of the run times for each workload. For the comparison, SQL Trace is
running in C2 Audit mode and SQL Server Audit is configured to audit the
equivalent events and write the output to a file.
Workload 1 contains 11 databases, ranging from
1.94 MB to 1812.5 MB. It also has 755 tables with an average of 2761
rows, with primarily char and then smallint and decimal columns.
Here is the
activity summary for workload 1 (% of workload):
- Stored
procedures:
- EXECUTE
(38.72%)
- Statements:
- ORDER
BY (21.90%)
- SELECT
FROM (39.51%)
- 1,219,234
statements were executed.
Workload 2 contains 2
databases ranging from 64 MB to 423.88 MB. It has 35 tables with an
average of 49,141 rows, with primarily bit,
varbinary, int, and nvarchar
columns.
Here
is the activity summary for workload 2 (% of workload):
- EXECUTE
(100%)
- 1,633,557
statements were executed
Workload 3 contains 3 databases ranging from
1.94 MB to 1059.63 MB. It has 154 tables with an average of 586 rows,
with primarily real, numeric, and float columns.
Here is the
activity summary for workload 3:
- Stored
procedures:
- sp_execute
- sp_prepare
- sp_unprepare
- EXECUTE
(94.25%)
- Statements:
- 585,400
statements were executed.
Workload 4 contains 1 database at
3235.75 MB. It has 84 tables with an average of 144,245 rows, with
primarily int, varchar, and datetime
columns.
Here is the
activity summary for workload 4 (% of workload):
- Stored
procedures:
- EXECUTE
(95.89%)
- 3,435,303
statements were executed.
Workload 5 contains 1 database at
174.94 MB. It has 152 tables with an average of 4,108 rows, with primarily
char and numeric, and then int
and float columns.
Here is the
activity summary for workload 5 (% of workload):
- Stored
procedures:
- sp_cursorclose
- sp_cursorfetch
- sp_cursoropen
- EXECUTE
(78.58%)
- Statements:
- 296,642
statements were executed.
Table 2: Performance numbers for SQL Server
Audit vs. SQL Trace
In the table,
the PERF COST VS BASE is calculated as (SQL AUDIT– BASE TIME) / BASE TIME, and
the PERF GAIN VS TRACE is calculated as (SQL TRACE – SQL AUDIT) / SQL TRACE.
The
improvement between SQL Trace and SQL Server Audit ranges from 11.08% to
45.09%, with Workload 2 showing the greatest difference between the two. This
is likely a result of the workload generating a huge amount of Audit events which
tends to emphasize SQL Server Audit’s greater efficiency at file I/O. Also
observed from the table is that the cost of running SQL Server Audit ranges
from 6.24% to 35.42%. Keep in mind that these values are with a large number of
audit action groups enabled so the cost would be lower with more modest Audit
settings. Since SQL Trace lacks the fine-grained auditing capabilities of SQL
Server Audit, a performance comparison between the two is not possible for this
scenario.
Examples
The following examples provide real-world scenarios for the
use of SQL Server Audit.
Example 1
A relatively
simple but very relevant usage of SQL Server Audit is to monitor the activity
of privileged users accessing a sensitive table. This can easily be
accomplished with the SQL Server Audit feature in SQL Server 2008 by setting up
the proper database audit specification. For this example, assume that the
sensitive table is in the hr_db
database and is called hr.salary and
we want to detect when the dbo (and consequently sysadmin) tries to query the
table. In this example, the audit information will be written to a file on a
remote share called \\AuditServer\Audit, on which the SQL Server service
instance has permissions to write but not read or modify.
First we need
to create the Audit and the Database Audit Specification objects.
USE master
CREATE SERVER AUDIT audit1 TO FILE (FILEPATH='\\AuditServer\Audit')
USE hr_db
CREATE DATABASE AUDIT SPECIFICATION hr_dbspec FOR SERVER AUDIT audit1
ADD(SELECT,UPDATE,INSERT,DELETE ON hr.salary by dbo)
Because the
objects are created disabled by default, they need to both be enabled.
USE master
ALTER SERVER AUDIT audit1 WITH (STATE=ON)
USE hr_db
ALTER DATABASE AUDIT SPECIFICATION hr_dbspec WITH (STATE=ON)
At this
point, the audit is turned on and all queries against the hr_db table by the dbo or the sysadmin are recorded, as are any
actions that enable or disable the audit.
To confirm
that both the audit and the audit specification are enabled, the system views
can be queried.
SELECT is_state_enabled FROM sys.server_file_audits
SELECT is_state_enabled FROM sys.database_audit_specifications
Example 2
Some
compliance regulations require that access and usage of cryptographic keys be
audited. SQL Server Audit is capable of recording all activity that touches a
symmetric encryption key or the private key of a certificate or an asymmetric
key pair. Concisely, this activity is covered by the
DATABASE_OBJECT_CHANGE_GROUP action group at both the server and database
levels. The DATABASE_OBJECT_CHANGE_GROUP action group covers all activity
involving key usage as well as CREATE, ALTER, and DROP of objects in a database
not contained within a schema. Aside from the initial setup period of the
database, the incidents of CREATE, ALTER, and DROP should be low, meaning that
most of the activity captured under this action group should be restricted to
cryptographic key activity. For this example, creation of the Audit will be
demonstrated using SQL Server Management Studio. To create the audit object,
expand the Security tree under the instance, right-click Audits, and then click New
Audit. The Create Audit dialog
box, which is displayed in Figure 3, opens, enabling you to configure the audit
properties.
Figure 3: Create Audit dialog box
If the
defaults are acceptable, the only input required is the File path,
assuming that the audit destination is a file. Notice that SQL Server
Management Studio created a default audit name based on the timestamp. Next the
server and database audit specifications must be created. This can be
accomplished by right-clicking on Server
Audit Specifications and Database
Audit Specifications in the Object Explorer respectively (see Figure 4).
Figure 4: Creating a database audit
specification through SQL Server Management Studio
For the
database audit specification, the Create
Database Audit Specification dialog box, which is shown in Figure 5,
appears.
Figure 5: Create Database Audit Specification
dialog box
The audit
object created in the previous step needs to be selected in the Audit drop-down list. Next select
DATABASE_OBJECT_CHANGE_GROUP from the Audit
Action Type drop-down list.
The steps for
creating a server audit specification are very similar. At this point, enable
the audit and the related specifications by right-clicking them in the Object
Explorer.
After the
audit is running, administrators can leverage the abilities of Policy-Based
Management (PBM) to manage the audits on multiple servers to see whether they
are running and whether they are configured properly. A basic understanding of
PBM is assumed in the subsequent discussion; for more information, see SQL
Server Books Online.
Creating a
policy to determine whether an audit or an audit specification is enabled is
fairly straightforward. To create a policy that reports whether a server has an
audit enabled, a new condition needs to be created using the audit facet and an
expression that evaluates whether @Enabled
equals True (see Figure 6).
Figure 6: PBM Create New Condition dialog box
Note that the
value in the Name box is arbitrary.
Now that the condition is created, a new policy should be created. In this
example, the new policy is given the name AuditOnPolicy, and the value in Check Condition is set to the AuditOn
condition that was just created (Figure 7).
Figure 7: PBM Create New Policy dialog box
Clicking OK creates the policy that will
evaluate whether the audit object on the server is enabled (or servers if the
evaluation is initiated through the Registered
Servers dialog box). Evaluation of the policy provides a list of audit
objects defined in the instance and an indication of whether they are active or
not. The results of the evaluation in Figure 8 indicate that one audit is not
enabled.
Figure 8: PBM Evaluate Policies dialog box
Please note
that the audit objects are read-only in this release of SQL Server. Because of
this, clicking Configure returns an
error. Finally, a similar policy should be created to determine whether the audit
specification(s) are enabled as well.
Of course,
knowing that an audit is enabled is important but equally interesting is
validating that the audit is configured properly. Although determining whether
an action group, such as DATABASE_OBJECT_CHANGE_GROUP, has been defined in the
audit specification might not be obvious, it is possible. Because no PBM facet
exists for audit action groups, the policy can instead be described as a T-SQL
query to determine whether a specific action group is included within the
specification. One such query is provided here.
SELECT 1
FROM sys.server_audits AS a
JOIN sys.database_audit_specifications AS s
ON a.audit_guid = s.audit_guid
JOIN sys.database_audit_specification_details AS d
ON s.database_specification_id = d.database_specification_id
WHERE a.is_state_enabled = 1
AND s.is_state_enabled = 1
AND d.audit_action_name = N'DATABASE_OBJECT_CHANGE_GROUP'
The query
returns the value 1 if an active audit contains a specification created with
the DATABASE_OBJECT_CHANGE_GROUP action group. To create a condition based on
this query in PBM, do the following:
- Create
a new condition and in Expression,
click on the “…” button to bring up
the Advanced Edit dialog box.
- Scroll
down the Functions and properties
list and double-click ExecuteSql().
This populates Cell Value with the
text “ExecuteSql(string returnType, string sqlQuery)”. You can edit this string
to replace string returnType with ’numeric’ and string sqlQuery with the SELECT statement above (see Figure 9; note
that the single quote in the query must be escaped with another single quote).
Figure 9: PBM Advanced Edit Window
3. After
clicking OK and returning to the Create New Expression dialog box,
complete the Expression by setting Operator
to = and Value to 1.
4. Set Facet to Database because you want this query evaluated for each database in
the instance.
When
evaluated, a policy using this expression will help the administrator detect
when the database is not being audited properly.
Conclusion
The new SQL
Server Audit feature introduced in SQL Server 2008 represents a significant
improvement over the auditing capabilities offered in previous versions of SQL
Server. One of the key advancements is the introduction of fine-grained
auditing whereby events can be targeted to specific actions on an object by
particular principals; the objects can even be scoped down to the individual
table level. Performance is another big incentive, because SQL Server Audit performs
significantly better than a comparable SQL Trace, up to around 45% in some
cases. Additionally, the audit target is no longer confined to just files,
because the Windows Application and Security logs are now options. And
considering benefits such as persistence of the audit state between instance
restarts and the involuntary recording of changes to the audit state, the new
SQL Server Audit feature provides a robust and comprehensive auditing solution
for the enterprise.
For more information:
http://msdn2.microsoft.com/en-us/library/cc280386(SQL.100).aspx:
Understanding SQL Server Audit
http://msdn2.microsoft.com/en-us/library/bb630282(SQL.100).aspx:
SQL Server Extended Events:
http://msdn2.microsoft.com/en-us/library/bb510667(SQL.100).aspx:
Administering Servers by Using Policy-Based Management
http://www.microsoft.com/sqlserver/2008/en/us/security.aspx:
SQL Server 2008 Security
http://www.microsoft.com/sqlserver/:
SQL Server Web site
http://technet.microsoft.com/en-us/sqlserver/:
SQL Server TechCenter
http://msdn.microsoft.com/en-us/sqlserver/:
SQL Server DevCenter
Here is a link to the original white paper. For your convience I placed a copy of what was on the microsoft web site. I would encourage you to go to the original location.
Auditing in SQL Server 2008
Posted Michael Corey,
Founder & CEO, Ntirety
www.ntirety.com
My Personal Twitter Account: Michael_Corey
Ntirety Corporate Twitter Account: Ntirety