Server Trigger

  • Hi,

    We have a DDL trigger on our server preventing access from Microsoft Office, Excel or Access applications for unauthorised users. Users and their workstation details are in a 'white list', which gives them access if they've been approved. Every one else gets a 'login failed due to trigger execution' message.

    I've just found out that when trying to create a DSN connection using the ODBC manager (SQL Server Native Client 10), it also fires the trigger. Is this a security feature to stop Office users using DSN connections as a backdoor, or is the ODBC manager seen as part of the office suite? Having said that, I've also tried using the ODBC manager from a machine without MS Office installed, and it still failed.

    Login trigger code:

    /****** Object: DdlTrigger [Logon_Trigger_Monitor_Excel] Script Date: 11/02/2011 09:01:39 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Logon_Trigger_Monitor_Excel]

    ON ALL SERVER WITH EXECUTE AS 'xxxx\xxxx'

    FOR LOGON

    AS

    BEGIN

    IF APP_NAME() LIKE '%MICROSOFT OFFICE%' OR APP_NAME() LIKE '%EXCEL%' OR APP_NAME() LIKE '%ACCESS%'

    IF UPPER(ORIGINAL_LOGIN()) LIKE 'HERMES\%'

    BEGIN

    IF (SELECT COUNT(*)

    FROM monitoring.dbo.tblExcelWhiteList

    WHERE UPPER(loginwho) = UPPER(ORIGINAL_LOGIN())

    AND UPPER(loginfrom) = UPPER(HOST_NAME())) = 0 ROLLBACK

    END

    END

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ENABLE TRIGGER [Logon_Trigger_Monitor_Excel] ON ALL SERVER

  • Actually, just figured it out. SQL Server's picking up the connection as 'Microsoft Data Access Components'. Our login trigger's screening applications like '%ACCESS%'...

    thanks for looking.. 😀

  • I ended up with a similar solution in the past because I had no other option.

    However, be aware that this is not the best way to enforce security. Users should be unable to login because they don't have a login at all.

    -- Gianluca Sartori

  • We allow users to access the database through the designated application, for which they are in an AD group with limited permissions, but we don't want those same users accessing the database from Excel, Access, etc. That's why it's set-up this way.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply