Introduction

Intended audience

This document is intended for database administrators who plan to develop, deploy, assess or implement auditing solutions in Microsoft SQL Server on Microsoft Windows platform.

Context

Security has become a very critical mission for almost every IT professional. In Europe, for instance, the European Commission has published a regulation called « General Data Protection Regulation » a.k.a. GDPR that allows personal data circulation (in Europe) but under certain conditions: every company must guarantee that its data are safe or at least it did its best to make it secure.

This regulation makes auditing a requirement as we should be able to detect security breaches and prove at any time that you made the best effort to guarantee confidentiality, integrity and availability of the system and log any activities of interest. For instance, we need to know that someone (who?) has modified the structure of a table at 1 AM even if it’s planned maintenance. This regulation not only applies to European-based company, but also any company around the world that processes data related European citizens.

There are multiple ways to audit activity in SQL Server. They use different features and techniques and they all have their advantages and disadvantages. Based on those particularities and what we need to audit, we can come to think that it could be “better” to choose one from another. Among them, there are SQL Server Audits which are built on top of Extended Events.

But we won’t insist on each of those features as they are already very well introduced by Minette Steynberg in her article entitled “Creating a successful auditing strategy for your SQL Server databases“.

Actually, in the following sections, we will first take a look at the SQL Server Audit using SQL Server Management Studio (SSMS). This discovery will lead us to notice important components in audit architecture. Finally, we will see what can be done with audits and what built-in tools (dmv, dmf) are at our disposal.

A discovery of SQL Server Audit

To provide a good overview of SQL Server Audit, let’s assume that we’ve never read anything about this feature and we are going to use SQL Server Management Studio (SSMS) to get an insight on them. In the following subsections, we will try to set up an audit for DBCC commands.

Creating audits using SQL Server Management Studio (SSMS)

Creating a SQL Server Audit is pretty simple using SSMS. To do so, we must connect to a SQL Server instance (using SSMS) and go down the tree view to “Security/Audits”.

Then right-click on “Audits” and choose “New Audit…”

And you can fill the form, then click “OK”.

Note
The File path must exist otherwise you get the following error message

As you can see in the figure below, the audit is actually created in a disabled state.

Here are the actions we can do on this audit when we right-click on it:

There are basically two actions that can be useful here: “Enable Audit” and “View Audit Logs”, which is only valuable if the audit is enabled.

But, so far, we haven’t told the audit what user action or feature events we want to audit… Actually, if we enable the audit, we will only get a list of events related to this audit. Here is what is stored in audit log if we enable then disable the audit at this stage: (the audit log is actually truncated because it’s too large to display on a single page)

So, we can say that an audit, audits events on itself.

Creating Server Audit Specifications

To tell SQL Server the audit “Audit-Demo-SSMS” has to store for instance, all calls to DBCC, we must create an additional SQL Server object called “Server Audit Specification”:

We can right-click on this “folder” and add a new Server Audit Specification. We must link it to the “Audit-Demo-SSMS” audit object and add “DBCC_GROUP” in the “Audit Action Type” column then click “Ok”.

Note

1. The Server Audit Specification is created in a disabled state
2. We can’t choose a name for Server Audit Specification

So, in order to keep track of DBCC command execution, we need to enable this server specification as well as the server audit object itself. If we do not enable the server audit specification, these commands won’t be tracked.

Then we can run a DBCC command like DBCC CHECKDB and see what happens…

Note
Log records only appeared once DBCC CHECKDB complete.

Database Audit Specifications

Well, for curious ones who have the chance to run Enterprise Edition of SQL Server, you will be able to notice that there is also an audit specification for databases:

Summary

Based on our observations, we can conclude that:

• SQL Server Audits is not a single object that lives alone inside SQL Server. Multiple components are used to be able to perform the task we want them to do like:
  • Server Audit Specifications
  • Database Audit Specifications
• These specifications respond to events that occur on SQL Server instance
• Amongst these events, there are all changes on the audit itself
• The audit has an output configuration

As we could imagine, the audit will output to a given target also known as « Audit Log ». We will see it in further details in following section. Let’s now dive into the architecture of SQL Server Audits…

The architecture of SQL Server Audits

Audits takes advantage of an Event-based architecture

SQL Server Audit is actually part of an event-based architecture built inside SQL Server that involves several elements that we will be talking about in this section. These elements are combined into a single “package” and specialized to respond to a group of server or database actions.

Events may be raised because of user activity like CREATE DATABASE or DBCC CHECKDB statements or because of a change on a feature like Database Mirroring or on the server itself.

We can summarize how SQL Server Audit works as follows. First of all, a DBA must create a new SQL Server Audit object and specify the events that should be part of this audit. It will also create mandatory elements to trap those events and get it as input. So, once the audit object is created, every time an event subject to this audit occurs, this event is trapped, eventually transformed, and pushed as an input to the SQL Server Audit object previously created. This object will finally take care of the event and output its data as per its “configuration”. We sometimes refer to that output as the audit.

The figure sums up the workflow of the way an event is handled by SQL Server Audit in a very high level:

To provide a suitable comparison, defining SQL Server Audits reminds me the way to define an event handler when implementing a graphical user interface.

In that context, there could be a “ButtonClicked” event that is raised by user interface to an event manager and we should define an “OnButtonClicked” procedure that should run whenever the “ButtonClicked” event occurs. To make it happen, we need to link this event and the procedure we wrote. In some language, we will use a SIGNAL.. SLOT call, in some other we would call an AddEventHandler function. With SQL Server Audit, we will use an Audit Specification object as the link between events and SQL Server Audit.

The input: Extended Events

The input or the source of information of a SQL Server Audit is always an instance of an Extended Event. For those who are not familiar with them, we will talk a little bit about them. First of all, here is how Microsoft defines Extended Events:

SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications.

Concretely, when we define/create an Extended Event, we define:

• A set of SQL Events (like those ones we can choose in SQL Trace) that might be collected.
• A set of additional session data that should be collected when an event occurs. These data can be the hostname of the end-user that raised the event or the application this client used. They are referred to as Extended Event Actions.
• A destination for the collected information known as a “target“.

  There can be several kinds of targets like:

  1. Event counter.

     They are used to obtain information about workload characteristics without adding the overhead of full event collection.

  2. Event File.

     They are used to write event session output from complete memory buffers to disk. That’s generally the preferred target for most DBAs.

  3. Histogram.

     They are used to count the number of times that a specified event occurs.

  4. Ring Buffer

     They are used to hold the event data in memory on a first-in first-out (FIFO) basis, or on a per-event FIFO basis. We can imagine that Ring Buffer output is used in SQL Server Audit implementation.

     (Definition from SQL Server Extended Events Targets)

• A set of parameters to provide to internal extended event handler like its state (ON/OFF) or the way event retention should be made.

As an example, you will find below the creation statement of default extended event called system_health that is defined by default starting SQL Server 2012.

We will notice that several events are defined, some with actions. We will also see that events can be filtered (using a WHERE clause). Finally, we will notice that two targets are set, one to a file and one to a ring buffer.

If you are interested in the subject of extended events, please, refer to their dedicated documentation on msdn.

The notion of Audit Action Type (or Groups)

As Extended events can take care of multiple SQL Events at the same time, the SQL Server Audit feature comes with the definition of groups of events. These groups can be divided into three primary levels:

• Server-Level to audit server operations like login/logoff, create server trigger and so on
• Database-Level to audit database change operations using either DML or DDL
• Audit-Level to audit actions performed on the audit itself

Each of these levels has a set of event groups. Here are some example events:

Notice the second event is this list can be defined at both server and database levels. To get the entire list of those event groups, please, refer to SQL Server Audit Action Groups and Actions.

Audit specifications or the link between Extended events and SQL Server Audit

So, in the previous section, we saw that SQL Server Audits takes care of auditing groups of SQL events. How do we actually tell a SQL Server Audit to respond to such a group? We create an audit specification object.

As we could expect, there are two kinds of audit specifications:

1. Server Audit Specification for server-level events
2. Database Audit Specification for database-level events

The output: Audit Target

As you can read in Minette Steynberg’s article entitled “Understanding the SQL Server Audit” or in the CREATE SERVER AUDIT technet page, the audit target also referred to as audit destination is of one of the three following types:

• Files in a directory on host’s filesystem or on a network share. This is the most common and recommended way to perform an audit. The name of the file is generated by SQL Server using different factors that make this name unique. While this is the simplest way to implement server audits, it’s not the most secure way to store this information as a file can be deleted, altered, lost.
• Windows Security Log. This is the least used way for implementing server audits because it requires additional permissions for SQL Server service account to be able to write into this log. In fact, these restrictions make it a very good place to store audit information.
• Windows Application Log. SQL Server has, by default, access to the Application Log and so, can write server audit information to the Application Log. If you don’t have a sort of “application log fetcher” which takes every new application log entry and transmits it to a safe place, don’t use this mode. Why? Because the Application Log is designed as a rollover log which means that persistence of server audit data is not guaranteed.

Each destination has its own set of configuration parameters and I recommend you to have a look at the CREATE SERVER AUDIT technet page.

Summary

You will find below a diagram that summarizes the architecture of SQL Server Audits. SQL Server Audit makes use of Extended Events feature by grouping events into Audit Action groups. Those Audit action groups refer to a given level: either server, or database or audit. Audit action groups are mapped to a server audit specification or a database audit specification, primarily based on our audit policy.

All these components together form what we call a server audit. This server audit outputs to an audit target which can be either a file on the server host, the application log or the security log.

Reading the output of a SQL Server Audit

Reading the output of a SQL Server Audit is pretty easy in T-SQL while the destination is set to File. Actually, Microsoft provides us a function called fn_get_audit_file. You will find an example usage taken from Microsoft documentation:

Security Considerations

Unfortunately, due to its nature, SQL Server Audit has not an extremely customizable permission assignment model. We could expect to grant permissions on a particular audit object, but it’s actually not the case. After all, it’s a database administration feature and in essence a DBA has already need advanced permissions on the instances he manages.

So, let’s list and review different permissions that can be assigned to a user in order to administrate SQL Server Audits. For each permission, there is a little description and eventually a link to a documentation page on Microsoft’s website.

By the way, if you have situations where you are obliged to live with the developers having full control permissions (I mean sysadmin and/or db_owner fixed roles) over a production server (this should never happen), be aware that they are almighty and can tamper any component you put in place…

Management objects

Now we are able to create a complete audit solution, let’s dive into the objects that Microsoft provides us to manage this solution.

So far, we’ve already seen one of these objects in section 4, the sys.fn_get_audit_file function that allowed us to read the content of an audit file.

But there are other management objects, which are:

• sys.server_audit, a view that contains one row for each SQL Server audit in a server instance
• sys.server_file_audits, a view for audits targeting to a file
• sys.server_audit_specifications, a view for audit specifications
• sys.server_audit_specification_details, another view like the previous one, but with more details
• sys.database_audit_specifications, same as sys.server_audit_specifications, but for database-level audit specifications
• sys.database_audit_specification_details, same as sys.server_audit_specification_details but at database-level.
• sys.dm_server_audit_status is a particularly useful view to monitor audit status
• sys.dm_audit_actions, a view that returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit

Conclusion

Today, we’ve acquired sufficient knowledge to understand SQL Server Audit architecture so that we should be able to build and manage a security audit based on SQL Server Audit feature. I would understand those practical guys who are willing to get into a concrete example.

This is actually the subject of the article entitle « SQL Server Audit feature: DDL events auditing examples ». In this article, we will take a look at some solutions to implement DDL event auditing and pinpoint advantages and disadvantages of each method.

Next article in this series:

• SQL Server Audit feature – DDL event auditing examples

Resources

• SQL Server Audit (Database Engine)
• SQL Server Audit Action Groups and Actions
• EVENTDATA (Transact-SQL)
• SQL Server Extended Events Targets

• Author
• Recent Posts

Jefferson Elias

Living in Belgium, I obtained a master degree in Computer Sciences in 2011 at the University of Liege.

I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development.

I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.

View all posts by Jefferson Elias

Latest posts by Jefferson Elias (see all)

• How to perform a performance test against a SQL Server instance - September 14, 2018
• Concurrency problems – theory and experimentation in SQL Server - July 24, 2018
• How to link two SQL Server instances with Kerberos - July 5, 2018