none
Logon Trigger

    Question

  • I want a logon trigger to validate that the user isn't a admin on the box and also a sysadmin on the database. It all works good, except that it throws random errors at times. What I would like is for it to swallow all errors and not rollback unless I roll it back. I have a try catch but I can't seem to swallow the errors and the trigger roles back. Any Idea's. I can't just use events because I do need to block the logon under those conditions.

    I bleed .net

    Friday, August 01, 2014 3:27 PM

Answers

  • I just tested. EXECUTE AS CALLER around the crucial statements works for me.

    And if you scrap the table you are inserting to in the error handler, you don't need EXECUTE AS 'sa'. (Or you could use certificate signing rather than EXECUTE AS in the header.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Boomport Friday, August 01, 2014 9:58 PM
    Friday, August 01, 2014 9:38 PM

All replies

  • As you havent provided the logon trigger (code) or the errors that you got all I can suggest is to read this MSDN article about logon triggers.

    http://msdn.microsoft.com/en-gb/library/bb326598.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, August 01, 2014 3:32 PM
  • Can you try to see the below link might help you with some answer

    http://www.neteye-blog.com/2009/08/microsoft-sql-server-login-auditing-with-logon-triggers/

    --Prashanth

    Friday, August 01, 2014 3:50 PM
  • Tx for the reply, finding the error isn't why I posted this.  I want to not rollback on an error, any error.

    Here is the gist of the trigger

    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [tr_Logon_Blacklist]
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
     DECLARE @raisedEventData XML
     declare @Admin bit
     declare @SysAdmin bit
     declare @Login varchar(128)

     begin try
     SET @raisedEventData = eventdata()
     SET XACT_ABORT OFF;
      set @LoginDate =@raisedEventData.value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(128)')

     --*********************************************************************
     --** Check Admin and SysAdmin under user context
     --*********************************************************************
      execute as login = @Login

      set @Admin= IS_MEMBER ('BUILTIN\Administrators')

      set @SysAdmin=IS_SRVROLEMEMBER ('sysadmin')

      revert
      -- Rollback here if both are true
     end try
     begin catch
      insert into [tblTriggerErrors] SELECT
       newid() as [ID],
       [EData]=@raisedEventData,
       ERROR_NUMBER() AS ErrorNumber
       ,ERROR_SEVERITY() AS ErrorSeverity
       ,ERROR_STATE() AS ErrorState
       ,ERROR_PROCEDURE() AS ErrorProcedure
       ,ERROR_LINE() AS ErrorLine
       ,ERROR_MESSAGE() AS ErrorMessage
       ,[DateTime]=getdate();
     end catch
    END;


    I bleed .net

    Friday, August 01, 2014 3:51 PM
  • Tx Prashanth for your reply.  This is about what I am doing, and this works well most of the time, but occationally it errors out (Don't know right not, and don't really care) and I can't have my users not being able to log on because the trigger is having trouble. 

    So the real problem here that I need solved, is there a way to never roll back or change the error code to something that SQL won't rollback for, unless I want to??


    I bleed .net

    Friday, August 01, 2014 4:05 PM
  • Tx Prashanth for your reply.  This is about what I am doing, and this works well most of the time, but occationally it errors out (Don't know right not, and don't really care)

    You will have to care.

    So the real problem here that I need solved, is there a way to never roll back or change the error code to something that SQL won't rollback for, unless I want to??

    No. I see that you have already increased your chances by adding the command SET XACT_ABORT OFF to the trigger, which permits you to ignore some errors. However, even when XACT_ABORT is OFF, many errors can abort the batch and roll back the transaction. Which errors that has which effect is very willy-nilly.

    So, yes, you will have to care why the trigger fails. And looking at the trigger code, i would suspect it is the EXECUTE AS statement which fails that impersonation is not possible. I have not tested, but EXECUTE AS CALLER may work better.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 01, 2014 8:13 PM
  • Tx Erland for your reply. 

    I did trace it to the Execute As statement.  It doesn't seem to have a reason why it will start to fail, but once it does its hard to get working again. 

    Maybe a better question is, is there functionality like this:

    IS_MEMBER ([User To Check], 'BUILTIN\Administrators')
    IS_SRVROLEMEMBER ([User To Check], 'sysadmin')

    That I wouldn't have to call 'Execute' As but could just pass in the user to check?


    I bleed .net

    Friday, August 01, 2014 9:16 PM
  • I just tested. EXECUTE AS CALLER around the crucial statements works for me.

    And if you scrap the table you are inserting to in the error handler, you don't need EXECUTE AS 'sa'. (Or you could use certificate signing rather than EXECUTE AS in the header.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Boomport Friday, August 01, 2014 9:58 PM
    Friday, August 01, 2014 9:38 PM