SQL Server 2008 introduces new auditing capabilities that can be invaluable to the DBA tasked with producing detailed audits that track database usage. Auditing can be implemented at the server and database levels, enabled on individual database objects and saved to different formats, such as binary files or the Windows Application log.
To set up auditing in SQL Server 2008, follow these steps:
- Create a SQL Server audit for the specific instance of SQL Server 2008.
- Create a server audit specification, a database audit specification or one of each.
- Enable the SQL Server audit.
- View the audited data.
In this tip, I'll review each step and give examples to demonstrate how they work. Note that these examples are, for the most part, based on Transact-SQL statements. However, the steps can also be performed by using the SQL Server Management Studio interface. For details on how to use SQL Server Management Studio, see Microsoft SQL Server Books online.
1. Creating a SQL Server audit
The first step you should take to set up auditing on an instance of SQL Server 2008 is to create a SQL Server audit. An audit is a security object configured to log-specific collections of events associated with the database engine. You can create multiple audits on an instance of SQL Server 2008.
When creating an audit, you must specify a name for the audit and the target location for the event output. The target can be a binary file, the Windows Security log or the Windows Application log. You can also specify one or more of the optional arguments available to the audit object.
To create an audit, use the CREATE SERVER AUDIT statement, as shown in the following example:
USE master
GO
CREATE SERVER AUDIT SrvAudit
TO FILE (FILEPATH='C:\Data', MAXSIZE=5 MB)
WITH (QUEUE_DELAY = 3000)
Notice, you must create the audit from the master databa...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
se. Because an audit is associated with the SQL Server instance as a whole, you cannot create one from a user database.
The first line of the CREATE SERVER AUDIT statement simply provides a name for the audit (in this case, SrvAudit). The next line is a TO clause that identifies the target location for the event output. For this example, I am sending the output to a file, so I must specify TO FILE and then provide the FILEPATH value. Note that this is a path name only. SQL Server automatically names the output file in the following format:
<audit_name>_<audit_GUID>_<partition_number>.sqlaudit
In the example above, the TO FILE clause also includes the MAXSIZE argument, which limits the file size to 5 MB. This argument is one of several that are optional to the TO FILE clause. If you send the audit data to the Application log or Security log, you need to specify only the log name option, as in the following example:
CREATE SERVER AUDIT SrvAudit2
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 3000)
As you can see, the TO FILE clause has been replaced with a TO APPLICATION_LOG clause, and no additional arguments are provided.
The last line in the CREATE SERVER AUDIT statement is a WITH clause. The clause supports several options that control how the audit is created. In this case, I've included the QUEUE_DELAY argument and set its value to 3000. The argument specifies the number of milliseconds that can elapse before audit actions are forced to be processed. By default, the number is 1000 milliseconds (1 second).
For details about all the options available to the CREATE SERVER AUDIT statement and other statements in this article, refer to Microsoft SQL Server Books Online.
2. Creating a server audit specification
After you've created the SQL Server audit, you must create a server audit specification or a database audit specification -- or one of each. A server audit specification is a set of one or more server-level audit action groups associated with a specific SQL Server audit. An action group is a set of related events exposed by the database engine. For example, the SERVER_OPERATION_GROUP action group is raised when security audit operations occur, such as when a user alters server settings.
You can create only one server audit specification per audit. However, you can add multiple action groups to the specification. To create a server audit specification, run a CREATE SERVER AUDIT SPECIFICATION statement from the master database, as in the following example:
USE master
GO
CREATE SERVER AUDIT SPECIFICATION SrvAuditSpec
FOR SERVER AUDIT SrvAudit
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE=ON)
The first line of the CREATE SERVER AUDIT SPECIFICATION statement provides a name for the specification (SrvAuditSpec). The second line, the FOR SERVER AUDIT clause, specifies the name of the audit (SrvAudit) that the specification is associated with. The third and fourth lines are ADD clauses that add the audit action groups to the specification. In this case, I am adding the SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP action groups to track the security principals that try to log on to the SQL Server instance.
The final line of the CREATE SERVER AUDIT SPECIFICATION statement is the WITH clause. The WITH clause includes the STATE argument that enables the specification when you create it. By default, the specification is disabled (STATE=OFF). If you do not enable the specification when you create it, you must enable it at a later time, before you can audit the action groups.
Creating a database audit specification
Unlike a server audit specification, a database audit specification is specific to a database. However, like a server audit specification, you can add audit action groups -- but they are specific to databases. In addition, you can add individual audit actions to the specification. An audit action is a specific action taken on a database, such as deleting data or running a stored procedure.
To create a database audit specification, run a CREATE DATABASE AUDIT SPECIFICATION statement from the target database, as in the following example:
USE AdventureWorks2008
GO
CREATE DATABASE AUDIT SPECIFICATION DbAuditSpec
FOR SERVER AUDIT SrvAudit
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SELECT, INSERT, UPDATE, DELETE
ON Schema::HumanResources BY dbo)
WITH (STATE=ON)
In the first line of the CREATE DATABASE AUDIT SPECIFICATION statement, I provide a name for the specification (DbAuditSpec), and in the second line, the FOR SERVER AUDIT clause, I identify the audit the specification is associated with. Next, I add an audit action group, which, in this case, is DATABASE_OBJECT_CHANGE_GROUP. This action group causes an event to be raised whenever a CREATE, ALTER or DROP statement is executed against the AdventureWorks2008 database.
The second ADD clause specifies individual audit actions, rather than an action group. In this case, the audit actions are SELECT, INSERT, UPDATE and DELETE. Notice, however, that the next line includes an ON clause specifying the HumanResources schema and the dbo security principal. As a result, whenever the dbo queries an object in the HumanResources schema or inserts, updates or deletes data in the AdventureWorks2008 database, SQL Server will log an event.
Finally, the last clause in the CREATE DATABASE AUDIT SPECIFICATION statement is a WITH clause. Once again, you can enable the specification as I've done here or enable the specification at a later time.
3. Enabling the SQL Server audit
Unlike the audit specifications we just reviewed, the WITH clause in the CREATE SERVER AUDIT statement does not support the STATE argument. That means you must enable the audit in a separate step, as in the following statement:
USE master
GO
ALTER SERVER AUDIT SrvAudit
WITH (STATE=ON)
As you can see, I use the ALTER SERVER AUDIT statement to modify the SQL Server audit (SrvAudit) I created earlier. The WITH clause in the ALTER SERVER AUDIT statement does support the STATE argument, which I've set to ON. After I run this statement, SQL Server will begin auditing the specified events.
4. Viewing the audited data
You can use the Log File Viewer in SQL Server Management Studio to view the audited data. In addition, if you set up your SQL Server audit to save your events to the Application log or Security log, you can use Event Viewer to view that data. I have found that the easiest way to review the event information is to save your audit data to a binary file and then use the Log File Viewer to review that data.
To access the Log File Viewer, open Object Explorer in SQL Server Management Studio, expand the Security node, and then expand the Audit node. Next, right-click the audit you want to review, and then click View Audit Logs to launch the Log File Viewer. Figure 1 shows a sample of events logged for the SQL Server Audit (SrvAudit) created in the example above.
[IMAGE]
Figure 1: Viewing audited data in Log File Viewer. (Click on image for enlarged view.)
And that's how to implement auditing and reviewing the audited data. SQL Server 2008 makes this process much easier than it is in earlier versions. You simply create the SQL Server audit, attach one or two audit specifications and enable the audit. SQL Server does the rest. For more details about any of the auditing components, be sure to check out Microsoft SQL Server Books Online.
ABOUT THE AUTHOR:
[IMAGE]Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems and business intelligence design and implementation. His books include Beginning MySQL (part of the Wrox Programmer-to-Programmer series), SQL: A Beginner's Guide (based on the SQL:1999 standard), MCSE Training Kit: Designing Highly Available Web Solutions with Microsoft Windows 2000 Server Technologies, and MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. You can find more information at www.rhsheldon.com.
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
