SQL Server Audit in SQL Server 2008 – Part 1

SQL Server 2008 February CTP introduces a new feature, SQL Server Audit. This feature facilitates auditing of Database Engine events by providing simple T-SQL statements to enable, store, and view audits on server and database objects. Compared to auditing with SQL Server trace files, using SQL Server Audit is much easier and cleaner. SQL Server Audit is implemented in the SQL Server Extended Events infrastructure. Extended events are created by the T-SQL statements to record the occurrences of the auditable actions.

To implement SQL Server audits, you first need to create a SQL Server Audit object. This object collects server and/or database-level actions and groups of actions that you want to monitor. It is created at the SQL Server instance level. You can have multiple SQL Server Audit objects per SQL Server instance. In the SQL Server Audit object, you specify the location for the output of the audit results. The results can be written to a binary file, the Windows Application Log, or the Windows Security Log. The file or the log is the Target component and the consumer of the associated extended events. Then you create Server or Database Audit Specification objects that belong to the SQL Server audit object. A Server Audit Specification collects server-level actions grouped in server audit action groups. It cannot collect individual actions. However, a Database Audit Specification collects both database-level audit action groups and individual actions.

In Part I of this series, we will focus on the server level events. Server audit action groups record logon and logoff operations, server principal changes, server and database role membership changes, database maintenance operations, server object changes, database object changes, server and database statement and object permission changes. Any database related action groups are collected on all the databases on the server instance. For example, DATABASE_ROLE_MEMBER_CHANGE_GROUP in a Server Audit Specification collects any actions that add or remove a login from a database role in any database on the server. If you want to audit only one database, you will need to define a Database Audit Specification in that specific database.

As part of the auditing requirements, we would like to monitor server logins and their server-level permission changes. To monitor login additions and deletions, we can include the SERVER_PRINCIPAL_CHANGE_GROUP audit action group in our server audit specification. Server-level permissions can be changed by issuing GRANT, REVOKE, or DENY, or through assigning server role memberships. Therefore, we can include the SERVER_PERMISSION_CHANGE_GROUP and SERVER_ROLE_MEMBER_CHANGE_GROUP audit action groups.

In this script, we write the audit to the Application Log.

/* Create a SQL Server Audit Object that writes the audit results to the Windows Application Log every one second.

   If the write fails, the instance continues running without stopping.  */

CREATE SERVER AUDIT MyServerAudit

    TO APPLICATION_LOG

    WITH ( QUEUE_DELAY = 1000,  ON_FAILURE = CONTINUE);

/* Create a Server Audit Specification object for the server audit.

   This object include three audit action groups related to server principal changes. */

CREATE SERVER AUDIT SPECIFICATION MyServerAuditSpecification

FOR SERVER AUDIT MyServerAudit

    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),

    ADD (SERVER_PERMISSION_CHANGE_GROUP),

    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP);
/* By default, both the audit and audit specification are created in the disabled state.

   We need to enable them before using them to record actions. */

ALTER SERVER AUDIT SPECIFICATION MyServerAuditSpecification

WITH (STATE = ON);

ALTER SERVER AUDIT MyServerAudit

WITH (STATE = ON);

You can confirm that the server audit object and the server audit specification object have been created by selecting from the system views shown below.

SELECT * FROM sys.server_audits

SELECT * FROM sys.server_audit_specifications

To confirm that an extended event session has been created, we can select from the extended events dynamic management views.

select s.name as [session name], se.event_name as [event name], p.name as [package name], t.target_name as [target name]

from sys.dm_xe_sessions s

join sys.dm_xe_session_events se on s.address = se.event_session_address

join sys.dm_xe_packages p on se.event_package_guid = p.guid

join sys.dm_xe_session_targets t on s.address=t.event_session_address

The result is as shown below.

As we can see above, an extended event session called MyServerAudit$A was created. It includes the audit_event from the SecAdudit package. The audit is written to Application Event log asynchronously.

Let’s test this script by adding a login called testlogin. Grant the View Server State permission to the login, and add it to the dbcreator role.

CREATE LOGIN testLogin WITH PASSWORD = ‘Welcome123’

GRANT VIEW SERVER STATE TO testLogin

EXEC sp_addsrvrolemember ‘testLogin’, ‘dbcreator’

DROP LOGIN testLogin

We can examine the Application Log by running the Windows Powershell command below.

Get-EventLog -logname “application” -newest 4 | where {$_.Source -eq ‘MSSQLSERVER’ }   |

  select EntryType, EventID, Message, TimeGenerated | format-list

The complete output is shown here.

As we can see above, all the four actions have been recorded. They were sorted in the reverse order of the time they were generated. The session_server_principal_name column showed that the login POWERDOMAIN\PowerUser owned the sessions and performed the actions. The target_server_principal_name column showed that the target login of the actions was testLogin. Take the last action as an example. It had an action id CR, which stood for Creation. The create login statement was also captured in the statement column.

Conclusion

Security auditing is a critical aspect of database administration. In this article, we utilized SQL Server Audit in SQL Server 2008 to monitor server principals. In the next article, we will show you how to create an audit to monitor database-level actions. We will also write audit results to a binary file and read from it.

» See All Articles by Columnist Yan Pan