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.















session name


event name


package name


target name


MyServerAudit$A


audit_event


SecAudit


asynchronous_security_audit_event_log_target


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.













































































































































































































































































































































































































































































































































EntryType


:


SuccessAudit


EventID


:


33205


Message


:


Audit event: event_time:2008-05-20 02:23:30.8789386


sequence_number:1


action_id:DR


succeeded:true


permission_bitmask:0


is_column_permission:false


session_id:111


server_principal_id:263


database_principal_id:1


target_server_principal_id:0


target_database_principal_id:0


object_id:269


class_type:SL


session_server_principal_name:POWERDOMAIN\PowerUser


server_principal_name:POWERDOMAIN\PowerUser


server_principal_sid:010500000000000515000000b10a02c4a260f9cc1463f7c552040000


database_principal_name:dbo


target_server_principal_name:


target_server_principal_sid:


target_database_principal_name:


server_instance_name:POWERPC


database_name:master


schema_name:


object_name:testLogin


statement:DROP LOGIN testLogin


additional_information:


.


TimeGenerated


:


5/19/2008 9:23:31 PM


EntryType


:


SuccessAudit


EventID


:


33205


Message


:


Audit event: event_time:2008-05-20 02:23:30.8769326


sequence_number:1


action_id:APRL


succeeded:true


permission_bitmask:0


is_column_permission:false


session_id:111


server_principal_id:263


database_principal_id:1


target_server_principal_id:0


target_database_principal_id:0


object_id:0


class_type:SG


session_server_principal_name:POWERDOMAIN\PowerUser


server_principal_name:POWERDOMAIN\PowerUser


server_principal_sid:010500000000000515000000b10a02c4a260f9cc1463f7c552040000


database_principal_name:dbo


target_server_principal_name:testLogin


target_server_principal_sid:542d98c0aabe98419e406d3b9ca5e3a7


target_database_principal_name:


server_instance_name:POWERPC


database_name:master


schema_name:


object_name:dbcreator


statement:EXEC %%System().AuditEvent(ID = 1145131603, Success = 1, TargetLoginName


= @loginame, TargetUserName = NULL, Role = @rolename, Object = NULL, Provider = NUL


L, Server = NULL)


— CANNOT CHANGE SA ROLES —


additional_information:


.


TimeGenerated


:


5/19/2008 9:23:31 PM


EntryType


:


SuccessAudit


EventID


:


33205


Message


:


Audit event: event_time:2008-05-20 02:23:30.8759296


sequence_number:1


action_id:G


succeeded:true


permission_bitmask:4194304


is_column_permission:false


session_id:111


server_principal_id:263


database_principal_id:1


target_server_principal_id:269


target_database_principal_id:0


object_id:0


class_type:SR


session_server_principal_name:POWERDOMAIN\PowerUser


server_principal_name:POWERDOMAIN\PowerUser


server_principal_sid:010500000000000515000000b10a02c4a260f9cc1463f7c552040000


database_principal_name:dbo


target_server_principal_name:testLogin


target_server_principal_sid:542d98c0aabe98419e406d3b9ca5e3a7


target_database_principal_name:


server_instance_name:POWERPC


database_name:master


schema_name:


object_name:


statement:GRANT VIEW SERVER STATE TO testLogin


additional_information:


.


TimeGenerated


:


5/19/2008 9:23:31 PM


EntryType


:


SuccessAudit


EventID


:


33205


Message


:


Audit event: event_time:2008-05-20 02:23:30.8739236


sequence_number:1


action_id:CR


succeeded:true


permission_bitmask:0


is_column_permission:false


session_id:111


server_principal_id:263


database_principal_id:1


target_server_principal_id:0


target_database_principal_id:0


object_id:269


class_type:SL


session_server_principal_name:POWERDOMAIN\PowerUser


server_principal_name:POWERDOMAIN\PowerUser


server_principal_sid:010500000000000515000000b10a02c4a260f9cc1463f7c552040000


database_principal_name:dbo


target_server_principal_name:


target_server_principal_sid:


target_database_principal_name:


server_instance_name:POWERPC


database_name:master


schema_name:


object_name:testLogin


statement:CREATE LOGIN testLogin WITH PASSWORD = ‘******’


additional_information:


.


TimeGenerated


:


5/19/2008 9:23:31 PM


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

Yan Pan
Yan Pan
Yan Pan (MCITP SQL Server 2008, MCITP SQL Server 2005, MCDBA SQL Server 2000, OCA Oracle 10g) is a Senior DBA for a leading global financial services firm, where her daily duties include administering hundreds of SQL Server and Oracle servers of every possible version, working with business units on software development, troubleshooting database issues, and tuning database performance. She has written a Wrox book titled “Microsoft SQL Server 2008 Administration with Windows PowerShell” with MAK who is also a columnist for DatabaseJournal.com. You can check out the book at many book stores, such as Barnes & Noble, Borders. Previously, Yan worked as a SQL Server DBA and a .NET developer at Data Based Ads, Inc., and developed two .NET Web applications. Before that, she worked at AT&T Research Labs. She designed OLAP cubes with SQL Server Analysis Services and developed PivotTable reports for regional managers. She has master’s degrees in Computer Science and Physics.

Latest Articles