SQL Server Logon triggers part 2

In April I wrote an article entitled "SQL Server 2005 logon triggers". The article will be published in the forthcomming book "The best of SQL Server Central - Volume 4".

In the article I described how to mimic Oracle logon triggers using event notifications in SQL Server 2005.

That was part one. Now comes part 2. Last week Microsoft released the CTP of SQL Server 2005 SP2. Here's a list of all new features in SP2. And one of them is logon triggers. Great, finally we have them.

So can we do everything with logon triggers? No, there are some differences with the method I descibed in April using event notifications.

• Logon triggers are part of the "logon transaction", this means you can rollback or cancel a logon. Event notifications go on the queue, after the logon. So, no easy way to cancel a logon.
• triggers are raised synchronously with events, whereas event notifications are asynchronous.
• Logon triggers only fire if authentication succeeds, event notifications can also capture failed logons and logouts.
  

Anyway it is a nice new feature. I rewrote my logon trigger and it's now a lot easier to understand (keeping in mind the differences mentioned above). You can find the script at the end of this post.

Ever wanted to prevent those Excel or Access users from reading your SQL tables directly? Well these 7 lines of codes will do the trick:

CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END

Here's a complete sample script:

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
-- Frederik Vandeputte - www.vandeputte.org


--TESTED ON SQL 2005 SP2 CTP

USE MASTER
GO
--Clean up old logging db
IF DB_ID ('Logging_demoSP2') IS NOT NULL
BEGIN
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
END
GO
--Create Logging DB and table
CREATE DATABASE Logging_demoSP2
GO

USE Logging_demoSP2

CREATE TABLE Logging (

LoggingID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
FullLog XML
)
GO


--The LOGON Trigger
CREATE TRIGGER logon_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @message_body XML

SET @message_body = eventdata()

INSERT INTO Logging_demoSP2.dbo.Logging (
EventTime,
EventType,
LoginName,
HostName,
AppName,
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/ClientHost/text()') AS VARCHAR(100)),
APP_NAME(),
@message_body)


END


GO

--Open demo connections

select * from logging


--Prevent acces from Excel
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END


--Try to connect from Excel

--Clean DB
USE MASTER
GO
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
DROP TRIGGER logon_trigger ON ALL SERVER
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER