Auditing in SQL Server 2008 - SQL Server 2008

The need for auditing has probably existed since the days of the caveman. Its part of the sad fact that it is not possible or logical to trust everyone. Just ask the stockholders of the once powerful Enron Corporation about trust in their management.

The high-profile corruption of Enron and its accounting firm gave fuel to the creation of additional governmental legislation like the Sarbanes-Oxley (SOX) Act of 2002, which was intended to address the issues around public accounting. SOX is just one of many regulatory compliance laws. As a DBA, you may be tasked with working with a consultant to see how your company can comply with respect to the databases used in your company. Since these laws dont come out and say, You must turn on encryption for this type of information,” actual configuration of the database is left up to interpretation.

Even if you are not affected by SOX or other governmental regulations, auditing adds value in many other situations. Does your organization outsource work to third-party companies or employees? Do you ever want to know the user who is reading data from a particularly sensitive table? An effective auditing solution can answer these questions and many others.

Auditing has always been available to some degree in SQL Server. SQL Server 2005 leveraged its SQL Trace functionality and created a set of auditing classes. These trace classes enable administrators to audit events like failed login attempts and users trying to create or alter objects within the database. The SQL Server 2005 functionality was useful and effective, but a few areas needed improvement. Because auditing was exposed via SQL Trace, the logical tool to use to create and manage auditing was the SQL Server Profiler tool. Profiler is a tool designed for performance, tuning, and optimization, not auditing a database. In addition, the fact that auditing was using SQL Trace implied the performance of the system may be affected, since enabling trace on the database degrades performance to some degree.

Recognizing the importance of auditing, the SQL Server 2008 product team designed an auditing solution that addresses these concerns and provides a framework for further auditing capabilities in future SQL Server releases. DBAs no longer need to use SQL Server Profiler to create and manage audits. They can use either SQL Server Management Studio or native T-SQL. Auditing no longer uses SQL Trace. Instead, it employs a powerful new eventing subsystem in SQL Server called Extended Events. This new eventing subsystem leverages Service Broker to securely and transactionally store and forward auditing messages.

With any new features comes a variety of new objects, catalog views, and possibly some new T-SQL to learn. Auditing is no exception. If you have used auditing previously in SQL Server, it may benefit you to temporarily forget what you know. We will refer to how the new auditing in SQL Server 2008 relates to SQL Trace when we talk about specifying what to audit. Keep in mind that SQL Server Management Studio provides a variety of management dialog boxes, so you do not need to remember the T-SQL syntax.

Where to Write Audit Data

An audit needs to write records somewhere so auditors and other users with read access can review the actual auditing information. The Server Audit object defines the location of where audit data will be written. Server audits are defined at the server level, as opposed to defined for a particular database.

An audit is allowed to write to a file on the file system, the Windows Applications Log, or the Windows Security Log. Each of these options has slightly different requirements and parameters available for configuration.

Here is an example of logging audit data to a file:

CREATE SERVER AUDIT [CustomerProtectionAudit]
TO FILE
(FILEPATH = N'C:AuditLogs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 0
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

Two options are common to all auditing destinations:

QUEUE_DELAY: Since we are leveraging Service Broker behind the scenes, audit events are written to a queue. In the example here, the queue delay is 1 second (1000). This means the queue is allowed to fill up for 1 second before it is flushed to disk or to the Windows logs. This also means that if the server died unexpectedly within this 1 second, you may potentially lose auditing events. Setting the QUEUE_DELAY to 0 means synchronously writing auditing records. Those of us experienced with the word synchronous know that usually means slow, and setting QUEUE_DELAY to 0 will slow down your application. Your organization will need to determine if you must guarantee audit events are logged or if you are able to take a 1-, 2-, or 10-second hit in auditing records due to an unforeseen catastrophic event.

ON_FAILURE: The possible options here are CONTINUE and SHUTDOWN. CONTINUE means if for some reason audit records can be physically written, the server instance on which auditing is defined should continue to run, just as it does 24 hours a day, 7 days a week. SHUTDOWN means that if the audit records cant be physically written, the server instance should shut down. Yes, SHUTDOWN means that users would be kicked off, transactions would be aborted, and many people may be upset, but it is all for the sake of auditing.

Sending audit events to either the Windows Application Log or the Windows Security Log does not require any special parameters. For example, here’s the form for using the Windows Application Log:

CREATE SERVER AUDIT [CustomerProtectionAudit]
TO APPLICATION_LOG

Logging to the Windows Application Log is straightforward and requires no special configuration. However, logging to the Windows Security Log requires that the SQL Server service account be Local System, Local Service, Network Service, or a domain user with SeAuditPrivilege, and not an interactive user. These are requirements that come from the operating system.

When you send audit events to a file, you need to define certain behaviors, such as how big to allow the audit file to grow and where to put the file on the file system. The TO FILE parameter provides these definitions, as in the preceding example:

TO FILE
(FILEPATH = N'C:AuditLogs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 0
,RESERVE_DISK_SPACE = OFF
)

Notice that you do not specify a filename for the audit data, but instead specify a file path. This is because SQL Server will autogenerate a filename for the audit data. The format of the filename is composed of the audit name, the audit globally unique identifier (GUID), a file offset value, and a timestamp value.

CustomerProtectionAudit_3986977D-1EFF-434A-8078-22B1BB22BD1D_0_
128367603342740000.sqlaudit

SQL Server allows you to specify both local and remote fileshares.

MAXSIZE is simply the maximum size you are willing to let the audit file grow to. A value of 0 means unlimited. When a file gets to a maximum size, it will roll over to a new file if you have the MAX_ROLLOVER_FILES parameter set. By default, MAX_ROLLOVER_FILES is set to 0, indicating the audit file will never roll over to a new one. To keep audit files manageable, it may be necessary to set Âsome arbitrary limit, like 500MB or 1GB; otherwise, viewing or managing the audit file could take an extra long time. Do not worry about concatenating the files, because SQL Server has a function that will easily return the entire audit (or pieces of it) to you, without you needing to manually piece together these files. This function is called fn_get_audit_file and will be discussed later in this chapter, when we walk through an auditing example.

RESERVE_DISK_SPACE means that SQL Server will preallocate the size of the audit file on disk to that defined in MAXSIZE. This is useful for ensuring that you will have enough disk space to audit for a given period of time.

Note that other parameters are available for specifying where to log data for these T-SQL statements. Refer to SQL Server Books Online for the complete list.

What to Audit

Now that we have discussed where the audit events are written to, it might be helpful to describe exactly how to tell SQL Server what is interesting to audit. In SQL Server, we have things that happen and exist in the service instance scope, and things that happen and exist in the database scope. This is also how auditing is divided. SQL Server auditing has objects called Server Audit Specification and Database Audit Specification.

Server Audit Specifications

Server Audit Specification objects allow auditors to audit server instance information, such as failed and successful logins, whenever a login’s password changes, and whenever a login’s membership in a role changes. Table shows the kinds of groups of events you can audit. See SQL Server Books Online for a complete list.

Server-Level Audit Event Groups

Server-Level Audit Event Groups

From a glance at Table Description column, you can see that the Server Audit Specification events are equivalent to SQL Trace event classes. This is not because auditing in SQL Server 2008 uses SQL Trace, but rather that internally in the engine code, where it has a path to raise a SQL Trace event, it has a path to raise an auditing event, leveraging the extended eventing infrastructure. Doing this allows for an easier upgrade for those users using SQL Trace in SQL Server 2005 for their auditing needs.

Let’s take a look at an example of creating a Server Audit Specification:

CREATE SERVER AUDIT SPECIFICATION [ProductionServerAuditSpecification]
FOR SERVER AUDIT [CustomerProtectionAudit]

Notice there are no options on the CREATE SERVER AUDIT SPECIFICATION DDL other than to specify an existing Server Audit object. You must define a Server Audit object before you can define a Server Audit Specification.

In SQL Server 2008, you also must create the object first, and then ALTER it to add or remove any audit events that you wish to manage. For example, to add the FAILED_LOGIN_GROUP to the sample Server Audit Specification, issue the following:

ALTER SERVER AUDIT SPECIFICATION [ProductionServerAuditSpecification]
ADD (FAILED_LOGIN_GROUP)

You can have one or more Server Audit Specification objects defined on an instance. In addition, you can point any Server Audit Specification to any Server Audit (the place where audit records are written) you defined.

Notice that we have not talked about filtering of any kind. Once you enable the FAILED_LOGIN_GROUP, for example, you get an audit for every single failed login. In SQL Server 2008, you can’t say just give me failed logins for the sa login only. This lack of filtering is only for Server Audit Specification objects. For Database Audit Specification objects, some filtering is available.

Database Audit Specifications

Database Audit Specification objects allow auditors to audit database-scoped information, such as whenever a CREATE, ALTER, or DROP statement is issued; whenever a database user is created; or whenever permissions to this user have changed.

Database-Level Audit Events

Database-Level Audit Events

As with Server Audit Specification events, Database Audit Specification events are based on the locations of SQL Trace events, but this is not the case with all of them. Some can go a bit more granular than was possible using the SQL Trace audit event classes.

Again, for this type of auditing, you must first create the Database Audit Specification object. Then you can ALTER the object and add events:

CREATE DATABASE AUDIT SPECIFICATION [CustomerDatabaseAudit]
FOR SERVER AUDIT [CustomerProtectionAudit]
ALTER DATABASE AUDIT SPECIFICATION [CustomerDatabaseAudit]
ADD (SELECT ON [dbo].[Accounts] BY Developers)
GO

Database Audit Specification objects do allow some degree of filtering. In this example, we want to audit SELECT on the Accounts table only when users of the Developers group are issuing the statement.

Database Audit Specification objects apply to one Server Audit object. However, you can have more than one Database Audit Specification for any given database.

Since Database Audit Specification objects live in the database and are linked to the Server Audit object via a GUID, in certain scenarios where two copies of the same database are used, such as in the case of database mirroring, you will need to use the same audit GUID for both sides of the mirror. The SERVER AUDIT DDL syntax allows for creating the Server Audit object with a specific GUID. Thus, all you need to do to support this scenario is create the Server Audit object on the mirrored server with the same GUID as the primary server.

An Auditing Example

To help illustrate the concepts described in the previous sections, we will walk through an auditing scenario. Suppose you are a senior DBA at a financial company, and your organization uses an internal application written by another team in your company. Lately, this application seems to be having some problems, and you need to allow a group of developers access to your production SQL Server installation. Since the production servers contain customer-sensitive information, you need to ensure that they do not modify or add any information. Also, the upper management wants to track all SELECT queries against the data.

Implementing Auditing

For this example, you will simplify the database architecture for the internal application and have a single database called Customers. This database will have a table defined as follows:

CREATE TABLE Accounts
(customer_id INT NOT NULL,
Balance DECIMAL NOT NULL,
social_sec_num VARCHAR(13) NOT NULL)
GO
--We should insert some interesting values as well
INSERT INTO Accounts VALUES (1,59768.34,'041-00-0000')
INSERT INTO Accounts VALUES (2,128.01,'368-11-1111')
INSERT INTO Accounts VALUES (3,59768.34,'532-22-2222')
GO

Next, assume you have aWindows NT group called Developers and aWindows user called User1, who is a member of this group. At this point, you want to create the SQL login and a database user for this NT group:

USE master
GO
CREATE LOGIN [MYCOMPANYDevelopers] FROM WINDOWS
GO
USE Customers
GO
CREATE USER [Developers] FOR LOGIN [MYCOMPANYDevelopers]
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[Accounts] TO [Developers]
GO

Now that the accounts are created, you can create your Server Audit object:

CREATE SERVER AUDIT [CustomerProtectionAudit]
TO FILE
(FILEPATH = N'C:AuditLogs'
,MAXSIZE = 0 MB
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)

Once the Server Audit object is created, you can create a Database Audit Specification object that audits any SELECT against the Accounts table for Developers:

USE Customers
GO
CREATE DATABASE AUDIT SPECIFICATION [CustomerDatabaseAudit]
FOR SERVER AUDIT [CustomerProtectionAudit]
ALTER DATABASE AUDIT SPECIFICATION [CustomerDatabaseAudit]
ADD (SELECT ON [dbo].[Accounts] BY Developers)
GO

Note that, by default, server audits are not enabled. Thus, in order to start receiving audit events, you need to enable them, as follows:

--We must be in the master database to issue the ALTER SERVER AUDIT command
USE master
GO
ALTER SERVER AUDIT [CustomerProtectionAudit]
WITH (STATE=ON);
GO

At this point, you are auditing events. To simulate a live environment, you can launch SQLCMD under User1’s context and issue a SELECT and an INSERT query against the Accounts table. If you want to see the auditing data, you can view this in SQL Server Management Studio, or you can use the new function fn_get_audit_file.

Using fn_get_audit_file to View Auditing Data

The new fn_get_audit_file function returns information to the user in a record-set form from audit files written to the file system. This function can return a specific audit file or a single record set containing information from all the audit files within a particular directory. In the example, since you have only one auditing file, you can view your auditing events simply by passing the pathname and the wildcard *:

SELECT * FROM fn_get_audit_file('c:AuditLogs*',null,null)

Alternatively, you could directly call out the audit file, like this:

If you had more than one Server Audit object writing to the same folder, you would have another file that contained another name, GUID, timestamp, and offset. If you didn’t want to return this information, you could use the wildcard again. This time, placing the * just after the GUID will give you all the audit records in the folder for the given Server Audit:

When you issue this command, you will see a row for every audit event and a plethora of columns—too many to list on a book page. What is important to note here is that, in this example, you audited SELECT, INSERT, UPDATE, and DELETE for a database user that was really aWindows NT group. The audit log will show the Windows user that accessed the command—in this case, MYCOMPANYUser1, instead of the NT group MYCOMPANYDevelopers. It will also tell you the exact statements that were issued.

Managing Audits

In SQL Server 2005, there was a big push to get users off of querying the system tables directly and to use catalog views instead. Auditing follows this effort and provides a bunch of catalog views that describe each of the components of auditing.

The sys.server_audits view describes all the server audits that are defined on the server instance. This information includes the names, where they write their audit events, if they shut down on failure, and the queue delay value. The catalog views for database audits include one that describes their configuration and many others. These catalog views are sys.database_audit_ specifications and sys.database_audit_specification_details.

There are also dynamic management views that can be used to show the runtime status of auditing. Check out sys.dm_server_audit_status to determine if the audit is enabled and the current file size (if auditing to a file).

For those of us who prefer to use a user interface to manage databases, SQL Server Management Studio now has support for auditing. Everything that you did via T-SQL in the previous section can be done via Management Studio. Server audits have an Audits node below the Security node of a server instance, as shown in Figure.

Server Audits and Server Audit Specifications in Object Explorer

Server Audits and Server Audit Specifications in Object Explorer

You can see from Object Explorer which server audits are enabled and which are disabled by observing the icon associated with the Server Audit object name. As with most other database objects, from the context menu of a particular Server Audit object, you can enable, disable, view properties, or create a new Server Audit object. You can also view the audit history. Viewing the audit history will launch the familiar Log File Viewer dialog box, which shows the application log, security log, or the audit events from the file system.

Since Server Audit Specification objects are scoped at the server-instance level, they are also available under the Security node of the server instance. Here, you can also enable, disable, and manage your server audits.

Database Audit Specification objects are scoped to the database. To manage them, you need to navigate down the specific Database node to the Security node.

Database Audit Specifications in Object Explorer

Database Audit Specifications in Object Explorer

As with all other audit objects in Object Explorer, you can create new database audit specifications, modify the properties, and enable and disable them by using the context menus.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics