Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 6, 2008

SQL Server Audit in SQL Server 2008 - Part 1

By Yan Pan

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date