Twitter Updates

    follow me on Twitter

    Tuesday, November 14, 2006

    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

    2 comments:

    Anonymous said...

    Is there a way to just trap logons to a particular database on the server?

    Anonymous said...

    Very interesting entry.
    I tried it out--with a few changes--on several servers. I ran into two problems that others may wish to be careful about. Of course these are simple once the cause is identified, but that is not always immediately apparent.

    1) Be very careful about the code before applying. I had a typo in the INSERT table. When I next needed log in, all accounts failed. The only solution was to login with the DAC and disable to trigger. The command is "DISABLE Trigger ON ALL SERVER;".

    2) Be sure that each user has sufficient permissions to write to the INSERT table. We had some restricted user accounts that failed login due to this.