Introduction

Oracle introduced logon triggers in Oracle 8i, which make it very easy to do some basic logging on logons. Every time a user connects to the database a trigger is fired. In the logon trigger you simple insert a row into an auditing table. A very easy to read example can be found on

http://www.dba-oracle.com/art_builder_sec_audit.htm.

Microsoft introduced the Service Broker in SQL Server 2005. Service broker is an advanced architecture for building asynchronous distributed database applications. Along with SQL Server 2005 and the Service Broker came event notifications.

Event Notifications allow you to capture events on your SQL Server instance or database and respond to them. Event Notifications are similar to DDL triggers (another nice new feature in SQL Server 2005). However, event notifications are more advanced than DDL triggers. First of all they are handled asynchronous which make them ideal for logging. Secondly, event notifications allow you to respond to more events than DDL triggers. DDL triggers can only respond to DDL events, that's why they are called DDL triggers after all. Event notifications can respond to DDL events, but also to SQL Server Profiler events. And that's where we find the login related events.

Implementing Logon Triggers in SQL Server 2005 Using Event Notification

Before we start off we create a new "Logging_demo" database and add an audit table called Logging. We must enable the Service Broker on the database as well since it is disabled by default.

--Create Logging DB and table
CREATE DATABASE Logging_demo
GO
--Enable service broker
ALTER DATABASE Logging_demo SET ENABLE_BROKER
GO
USE Logging_demo
CREATE TABLE Logging (
LoggingID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
NTUserName VARCHAR(100),
NTDomainName VARCHAR(100),
Success INT,
FullLog XML
)
GO

Event notifications (and also Service Broker) work with 3 important objects: Queues, Services and Routes. Queues are very simple; they hold the messages (events) we want to process. The service will be listening on the queue, and the route makes it possible to read the queue. OK, I admit it is a little bit more complex than this, but we are focusing on our logon trigger. Detailed information about queues, services and routes can be found in books online.

Below we will create all three of these: the queue, the service, and the route.

--The queue
CREATE QUEUE LoggingQueue
GO
--The service
CREATE SERVICE LoggingService
ON QUEUE LoggingQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
--The route
CREATE ROUTE LoggingRoute
WITH SERVICE_NAME = 'LoggingService',
ADDRESS = 'LOCAL'
GO

Next we create our event notification object. We are interested in auditing successful and failed logons on the server. We use the appropriate events and link it to the service we created before.

CREATE EVENT NOTIFICATION Logging_Event_Notification
ON SERVER
FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED
TO SERVICE 'LoggingService', 'current database'
GO

Now everything is in place to start our logging. If you open new connections to your server those logon attempts will be captured and send to our LoggingQueue. The SELECT below will show you what is on the queue.

SELECT * FROM LoggingQueue

Great, we have messages in the queue, but how do we get them from the queue into our logging table? We use the new RECEIVE statement to receive messages from the queue and put them into variables.

 WAITFOR (
 RECEIVE TOP(1)
@message_type_name=message_type_name,
@message_body=message_body,
@dialog = conversation_handle
FROM LoggingQueue
    ), TIMEOUT 2000

All the information about the event can be found in the @message_body variable. This is a XML type variable and we can use XQuery to retrieve the information we want and store it in our logging table.

INSERT INTO Logging (
EventTime,
EventType,
LoginName,
HostName,
NTUserName,
NTDomainName,
Success,
FullLog)
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
@message_body)

Based on those RECEIVE and INSERT statements we create a stored procedure to handle our logon event:

--The proc to handle the events
-- Set options required for the XML data type.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LoggingProc
AS
SET NOCOUNT ON;
DECLARE     @message_body XML,
            @message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER ;
--Endless loop
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION ;
    -- Receive the next available message
    WAITFOR (
        RECEIVE TOP(1)
            @message_type_name=message_type_name,
            @message_body=message_body,
            @dialog = conversation_handle
            FROM LoggingQueue
    ), TIMEOUT 2000
--Rollback and exit if no messages were found
IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION ;
            BREAK ;
        END ;
   --End conversation of end dialog message
    IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
        PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
        END CONVERSATION @dialog ;
    END ;
    ELSE
    BEGIN

INSERT INTO Logging (
EventTime,
EventType,
LoginName,
HostName,
NTUserName,
NTDomainName,
Success,
FullLog)
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
@message_body)
END
COMMIT TRANSACTION
END
GO

In the last step we just link the stored procedure to our queue.

ALTER QUEUE LoggingQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = LoggingProc ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF )

From now on all logon attempts will be monitored and logged in our audit table.

SELECT * FROM Logging

Conclusion

I admit it is a little bit more complex to setup than it is in Oracle. However event notifications can do a lot more and it is really a nice tool to do all kinds of monitoring on your SQL Server 2005 databases and installations. When you are ready with event notifications go out and play with service broker and create some cool asynchronous applications.

You can download the code here.

About the author:

Frederik Vandeputte is a SQL Server Consultant and trainer for Cronos (www.cronos.be) in Belgium. He has been working with SQL Server since version 6.5 and holds certifications as MCSA, MCSE, MCDBA, MCT and recently MCTS and MCITP Database Administrator on SQL Server 2005. His blog can be found on www.vandeputte.org.