Server Audit Specification Object
Now that you have created a server audit
object, you can start writing auditing events to it. In this example,
let’s audit all failed logins. Since logins affect more than one
database, you need to create a server audit specification. To create a
server audit specification, select New Server Audit Specification from
the Server Audit Specifications context menu. This will launch the
dialog box shown in Figure 3.
Figure 3. Create Server Audit Specification dialog
By default, a name is provided for you, but you
can easily change this to something more meaningful. In our example,
since we want to audit just failed logins, let’s call it Logins. The
Audit drop-down box contains all the server audit objects that are
defined for the server. Notice that you can create as many server audit
objects as you want. Since you created Compliance Audit, you can select
this server audit.
The actions grid is where you specify what events or groups of events you want to record. To audit failed logins, select FAILED_LOGIN_GROUP.
If you click OK, the server audit specification will be created.
You can also create the server audit specification using DDL as follows:
USE MASTER
GO
CREATE SERVER AUDIT SPECIFICATION [Logins]
FOR SERVER AUDIT [Compliance Audit]
ADD (FAILED_LOGIN_GROUP)
GO
Now that you have both a server audit defined
and a server audit specification, you can start an audit with these two
objects. Note that auditing objects are not enabled by default since
you may not be ready for the onslaught of auditing events after simply
defining an audit. To enable the server audit specification, you can
either select Enable Server Audit Specification from the context menu
of the object or issue the following T-SQL statement:
ALTER SERVER AUDIT SPECIFICATION Logins WITH (STATE=ON)
GO
To enable the server audit, you can select
Enable Audit from the context menu of the audit or issue the following
T-SQL statement:
ALTER SERVER AUDIT [Compliance Audit] WITH (STATE=ON)
GO
Once you have enabled both the server audit and
the server audit specification, you can test the audit by trying to
make a connection to SQL Server using false credentials. Once you
attempt to make this false connection, the audit event will be written
to the audit log. You can view audit logs by selecting View Audit Logs
from the context menu of your audit, Compliance Audit. Figure 4 shows the Log File Viewer dialog box with the failed login event.
Figure 4. Log File Viewer dialog box showing an audit log
You cannot write auditing events directly to a
table in SQL Server. If you really want to see them or push them into a
table, you can use the fn_get_audit_file function. An example of using this function follows:
SELECT * FROM fn_get_audit_file ('c:\audit\*',null,null)
This yields a result set that includes a
plethora of columns. Some of the more meaningful columns include the
actual T-SQL statement that was executed and the calling context of the
user.
