Deciding what method to use to collect audit information can be a task in and of itself. Depending on your needs there are a number of different ways to collect the information. There are settings such as Login Auditing and Common Criteria Compliance that are fairly simple to implement but only collect very specific sets of information. There are also coding solutions such as Triggers and Audit Columns that are very flexible but can be tricky to get right. Then there are Profiler Traces but those have been deprecated and will be removed in a future version of SQL Server. Their replacement, Extended Events, has become easier and easier to use in recent versions and can be used to collect a huge variety of information. However Extended Events does not include any Audit specific information. If you are setting up an auditing solution in SQL Server you are probably going to want to consider using SQL Server Audits. Audits are based on Extended Events and so have all of the benefits of Extended Events but also include the audit specific events. Audits have a different interface than Extended Events and a different set of T-SQL Commands. Fortunately they are very easy.

There are two components to an Audit. The Audit and the Specification.

Server Audit

The top level of any SQL Server audit is the Server Audit. This is the "header" of the audit. It contains among other things the settings for where the audit information will be stored and its behavior on failure. To bring up the GUI right click on Security/Audits and select New Audit.

There are a number of settings available when creating an audit, some of which need to be considered very carefully.

• Audit Name: Obviously the name of the Audit.
• Queue Delay(ms): How long (in ms) before the action must be logged.
  This ties in very closely with the next setting. The lower the value the greater the chance of failure, particularly in a busy system.If the value is 0 then the action and the logging are synchronous.
• On Audit Log Failure: Behavior if an action could not be logged.
  • Continue – The action will continue but the action will not be logged.
    This is the default value and the only one that is really "safe".
  • Fail Operation – The action fails. No audit record is logged.
    The risk with this setting is that actions can fail without any obvious reason or way to track what happened.
  • Shut down the server – The instance is shut down. This does require that the login issuing this has the SHUTDOWN permissions otherwise an error is generated (no audit event is created) but everything else continues normally.
    This option has the most absolute ramifications. If an action cannot be logged then the instance will be shut down. This option should only be used in the most secure systems where it is absolutely needed. If restarting the instance causes another failed audit event then the instance will shut right back down again. At this point there are two options to bring the instance back up. Either by fixing the cause (adding additional disk space for example) or the instance can be brought up in single user mode.
  • Audit Destination: Location where you want the audit information written.
    • Security log: There are additional considerations that must be taken into account if you wish to write to the security log. More information can be found here.
    • Application log
    • File (Default)
      • File Path – directory where the audit files will be written.
        It’s fairly obvious but the path selected needs to have enough room for the all of the files that might be created.
      • Audit File Maximum Limit – These settings determine the maximum possible number of files and if they roll over or if actions fail once the maximum number of files has been reached.
        If you select Maximum Rollover Files there is an unexpected consequence. When the maximum number of files is reached a new file is created and the oldest one is deleted. The problem is that it only attempts to delete the old file once. If it is unable to delete it then the file will remain. Over time this can cause the amount of space taken up by the audit files to increase dramatically.
      • Max File Size – Maximum size of each file.
      • Reserve disk space – If this is checked then maximum amount of space for the files is pre-allocated. This option is not available if the number of files is set to unlimited.
      The system view sys.fn_get_audit_file is used to read the information written to one or more Audit Files. Remember that while the files are being read they are locked and if the system tries to delete one (see Audit File Maximum Limit) it will not be able to.

  The T-SQL for the above settings is as follows:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  USE [master] GO   CREATE SERVER AUDIT [Sample Audit for Server Specification] TO FILE (      FILEPATH = N'C:\AuditFiles'         ,MAXSIZE = 500 MB         ,MAX_ROLLOVER_FILES = 10         ,RESERVE_DISK_SPACE = ON ) WITH (      QUEUE_DELAY = 1000         ,ON_FAILURE = CONTINUE ) GO

  Audit Specifications

  The Specification is the "detail" of the audit. An Audit "header" can contain one and only one Specification "detail". While the Audit contains information about where the audit events will be logged and how failures will be handled, the Audit Specification contains information about what events will be logged. Some events are server specific and some are database specific. Because of this it makes sense that there are both Server and Database Specifications. There is however, a fair amount of overlap in the events that are available. BACKUP_RESTORE_GROUP for example is available in both Server and Database specifications. The difference is that in the Server Specification the event is triggered for all databases while in the Database Specification the event is triggered only for the local database. The process for generating Server Specifications and Database Specifications is pretty similar. Under the appropriate Security heading (Server or Database) there is an Audit Specifications heading. Right click and select New Server/Database Audit Specification.

  

  Once the Create Database/Server Audit Specification window opens you can fill in the following information.

  • Name: The name of the Specification.
  • Audit: This is a drop down of the Audits available. Remember that you can only have one Specification per Audit.
  • Actions: A list of the events tied to the Specification.
    You will notice that the Actions list has a number of columns.
    • Audit Action Type: These are the actual events to be logged.
      For some events no additional information is needed, BACKUP_RESTORE_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP for example. On the other hand some events do require additional information, INSERT, UPDATE, DELETE and SELECT events for example. It appears that these events exist only in Database Specifications.
    • Object Class: This one is easy since it’s a drop down. There are three types of objects that can be audited. DATABASE, SCHEMA and OBJECT.
    • Object & Object Name: These two columns are dependent on the Object Class field. If the Object Class is OBJECT then Object is the schema name of the object being audited, otherwise it is left blank. The Object Name column is the name of the object to be audited. If you want to audit everything then you use the DATABASE Object Class and the Object Name will be the database name. If on the other hand you only want to monitor a single object then use the OBJECT Object Class and the Object will be the schema and the Object Name will be the objects name. Both of these columns are filled in automatically from the ellipsis (…) to the right of the Object Name column.
      If the Object Class is DATABASE then the Object Name must be the current database.
    • Principal: This is who is to be audited. It can be a Role, User, or Application Role. If all users need to be audited then the Public role should be specified. This column is also filled in using the ellipsis (…) to the right of it.

  Here is a sample database specification using the GUI and the associated T-SQL code.

  

  1 2 3 4 5 6 7 8 9 10 11 12 13

  USE [Test] GO   CREATE DATABASE AUDIT SPECIFICATION [Sample Database Specification] FOR SERVER AUDIT [Sample Audit for Database Specification] ADD (BACKUP_RESTORE_GROUP), ADD (FAILED_DATABASE_AUTHENTICATION_GROUP), ADD (EXECUTE ON DATABASE::[AdventureWorks2014] BY [dbo]), ADD (INSERT ON OBJECT::[Baseball].[PlayerPosition] BY [guest]), ADD (UPDATE ON SCHEMA::[Baseball] BY [public]) GO

  In Conclusion

  SQL Server Audits are remarkably simple to set up and work with. They do require some limited maintenance (checking to make sure the Audit Files are being deleted for example) but even that is fairly simple to automate. One very important aspect of auditing that is frequently forgotten, however, is that the once the data has been collected it needs to be reviewed. It can be very simple to just set up and Audit and forget about it. But if the data is not being reviewed on a regular basis, preferably using some sort of automated report, then the Audit is a waste of resources.

  
  
  • Author
  • Recent Posts

  

  Kenneth Fisher

  Kenneth works as a SQL Server Administrator near Dallas Texas. He started working with databases over 20 years ago and he’s been working in SQL Server for over 15 years now with a fairly even split between administration and development.
  
  So far he collected an MCTS in 2005 administration and an MCITPs in 2008 administration and development. He enjoys writing and sharing some of the interesting bits and pieces he learned over the years.
  
  View all posts by Kenneth Fisher

  

  Latest posts by Kenneth Fisher (see all)

  • Reviewing the SQL Server Audit - December 25, 2014
  • Intro to Auditing in SQL Server - September 30, 2014

  Related posts:

  1. SQL Server Policy Based Management – check your audit configuration
  2. Using Extended Events to review SQL Server failed logins
  3. SQL Server auditing with Server and Database audit specifications
  4. Understanding the SQL Server Audit
  5. SQL Server Audit Overview