SQL Server 2012 : Auditing in SQL Server (part 1) - Auditing Objects, Server Audit Object

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/19/2014 9:22:58 PM

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:

CREATE SERVER AUDIT [Compliance Audit]
(    FILEPATH = N'c:\audit'
(    QUEUE_DELAY = 1000,

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
ALTER SERVER AUDIT [Compliance Audit]
WHERE server_principal_id <> 268

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.

  •  SQL Server 2012 : Reordering Nodes within the Hierarchy - The GetReparentedValue Method,Transplanting Subtrees
  •  SQL Server 2012 : Querying Hierarchical Tables - The IsDescendantOf Method
  •  Protecting SQL Server Data : Obfuscation Methods (part 4) - Truncation,Encoding, Aggregation
  •  Protecting SQL Server Data : Obfuscation Methods (part 3) - Numeric Variance,Nulling
  •  Protecting SQL Server Data : Obfuscation Methods (part 2) - Repeating Character Masking
  •  Protecting SQL Server Data : Obfuscation Methods (part 1) - Character Scrambling
  •  SQL Server 2012 : Managing Resources - Limiting Resource Use, Leveraging Data Compression
  •  SQL Server 2012 : Tuning Queries (part 3) - Using the Database Engine Tuning Advisor
  •  SQL Server 2012 : Tuning Queries (part 2) - Gathering Query Information with Extended Events
  •  SQL Server 2012 : Tuning Queries (part 1) - Understanding Execution Plans
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us