Auditing Logins in SQL Server 2005

Eric Vaillancourt — Sat, 29 Mar 2008 03:14:52 GMT

When I teach my SQL administration classes, I'm often ask if SQL Server provides a mechanism to log user logins. Before SQL Server 2005 SP2, this task was possible but not reliable. In SQL Server 2005 this task is easy to implement using DDL triggers (Data Definition Language).

One reason that people ask me this question, is because they need to comply with Sarbanes-Oxley. I will be writing a series of articles on auditing for SOX in the next weeks.

As a consultant, I am frequently invited to attend meetings with Sarbanes-Oxley auditors to discuss the security and integrity of corporate data. they want to know who has access to the data, how access is granted, and how is the company monitoring to prevent someone from sneaking in, logging on, and doing something they shouldn't be doing.

In SQL 2005, a trigger can be created in order to perform any action upon a DDL statement. The scope can be database level or Server level. DDL triggers allow us to track critical changes to our database environment that may be intentional, by mistake, or malicious.

When you design DDL triggers, it's important to determine what events you want to audit, and to determine in which scope each event occurs. In this article, we will write a trigger to capture logins, which are server level events. You can get a list of all events that are valid for use in DDL triggers.

The first step is to create a database and a table that we will use to store the logs.

Execute the following code:

use master

create database Admin_Log

use Admin_Log

create schema [admin]

CREATE TABLE admin.logs (

LogID int IDENTITY(1,1),

EventTime DATETIME,

EventType VARCHAR(100),

LoginName VARCHAR(100),

HostName VARCHAR(100),

AppName VARCHAR(255),

Event_Data XML)

Note that the table makes use of the XML data type, which is new in SQL Server 2005. As you'd assume by its name, its job is to hold XML data.

Once your table is in place to keep track of the events, you're ready to create the trigger necessary to watch for these event.

Execute the following code:

use master

create trigger ServerWideLoginLogs

on all server

with execute as self

for LOGON

as begin

DECLARE @event XML

SET @event = eventdata()

INSERT INTO Admin_Log.admin.logs (EventTime,EventType,LoginName,HostName,AppName,Event_Data)

VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),

APP_NAME(),

@event)

end

This script creates the trigger that declares that we want to look for all DDL Login events that occur on the server. The information resulting when this trigger fires will go into the admin.logs table that we created earlier. Notice that we casting information from our @event variable (that we initialized using the eventdata() function) to insert data into our table.

In this context, the eventdata() function will gather information about the triggered event (LOGON) because that's the event specified in the statement FOR.

To test our new trigger, try connecting to the server and execute the following code:

select * from admin.logs

After running this statement, you will see that a record is inserted every time someone connects to the server.

What's in your XML?
The Event_Data field is populated by the EVENTDATA() function from our INSERT statement in our trigger. In this case, the eventdata() function returns XML data related to the connection, including the date and time of the login, the server name, the user ID, and the machine system ID.

Hope this was helpful,

Eric Vaillancourt

Technorati : DDL Triggers,Sarbanes-Oxley,Audit,SQL Server 2005

MS SQL Admin : Sarbanes-Oxley, Audit

Auditing Logins in SQL Server 2005