968
Part VII: Monitoring and Auditing
■ (^) Both Server Audit Specifi cations and Database Audit Specifi cations can defi ne sets
of events or groups to be captured. Event groups encapsulate a number of related
events. Database actions include select, insert, update, and delete, and they cap-
ture the user context and the entire DML query.
■ (^) The audited data includes user context information.
■ The SQL Server Audit sends all the captured events to a single target: a fi le, the
Windows Security event log (not in Windows XP), or the Windows Application event
log. The Management Studio SQL Audit UI includes a tool for browsing the audit
logs.
■ SQL Server Audits, Server Audit Specifi cations, and Database Audit Specifi cations
can all be created and managed either with Object Explorer or by using T-SQL.
■ SQL Server Audits, Server Audit Specifi cations, and Database Audit Specifi cations
can all be enabled or disabled. They may be modifi ed only while disabled. All are
disabled by default when they are fi rst created because that’s how Extended Events
works.
■ SQL Server Audits, Server Audit Specifi cations, and Database Audit Specifi cations
can all be managed by Policy-Based Management.
■ SQL Audits are serious. The SQL Server Audit object can be confi gured to shut down
the server if the audit doesn’t function properly.
Creating an Audit
The fi rst step to working with SQL Audit is to create a SQL Server Audit object.In Object Explorer, SQL Server Audit objects are listed under the server ➪ Security ➪ Audits
node. The New Audit command in the Audits node context menu opens the Create Audit
dialog, as shown in Figure 42-1.The queue delay, which determines how long SQL Server can wait before processing the
Extended Event, ranges from 1 second (1,000 milliseconds) to almost 25 days (2,147,483,647
milliseconds). The default (1 second) is reasonable for most situations. If the server is hit
with heavy traffi c, increasing the queue delay gives SQL Audit more fl exibility.Selecting Shut Down Server on Fail Operation ensures that the target fi le or the log receiv-
ing the events can be written to. If SQL Audit can’t write to the target, then it writes a
msg_audit_forced_shutdown event to the error log and shuts down the server. SQL
Server Audit is much more resilient to failures in SQL Server 2012; if the target fi le or log
receiving the events cannot be written to, SQL Server Audit can recover after the fi le or log
comes back online. A new option has also been added, which enables you to fail database
actions if they cause audited events to occur. Any action, which does not cause an audited
event, continues but audited events do not. The audit continues attempting to log events
and resumes when the condition that caused the failure is corrected. For additional infor-
mation see the fail_operation for the on_failure event in create server audit.c42.indd 968c42.indd 968 7/31/2012 10:17:48 AM7/31/2012 10:17:48 AM
http://www.it-ebooks.info