Sql-server – Is it possible to return a custom error for a logon trigger

raiserrorsql serversql-server-2012trigger

I'm trying to get a logon trigger to return a custom error. Thus far everything I have tried causes it to return the standard error:

enter image description here

My trigger looks like this:

CREATE TRIGGER Test_Logon_Trigger
ON ALL SERVER 
FOR LOGON
AS
BEGIN

IF ORIGINAL_LOGIN()= 'login_test' 
    BEGIN
        raiserror('Custom Error',25,16) 
        --PRINT 'Print'
        --ROLLBACK;
    END
END;

I have tried printing an error message and using a RAISERROR command separately and in combination. I've also tried using the WITH NOWAIT clause on RAISERROR. In all cases it just sends the message to the error log. Does anyone know how to modify the error being returned displayed on the screen? I'm even ok with returning a second error if necessary.

Best Answer

This behaviour is a security feature to make hacking more difficult, specifically because there is no variation in the output depending on the input. (Aside from exposing the fact there is a logon trigger active.)

If a password is mistyped, the system doesn't respond with an error saying "the password is wrong" or even whether the login name specified exists. If it told you why, this makes penetrating the system much easier because there's less guesswork involved.

So really if there is a way to do this using built-ins, I would have no hesitation to report it as a bug.

That of course doesn't solve what you're trying to do, which, IMO, is questionable for the same reasons.

Since login failures should be rare, what I would suggest is that all the restrictions be published internally in a predictable location (e.g., SharePoint). The main things with this are to make sure the list stays up-to-date (it could be data-driven), and also that it's visible to everyone who has access to the server (as opposed to everyone who is able to attempt to connect to the server, which is very different).

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Related Question