1

SQL Server EVENTDATA() is not working from MAC SQL Pro for SQL Server. I'm using a trigger to validate the IP address of the users that get logged in to SQL Server. This is my trigger:

CREATE TRIGGER [trLogOnCheckIP] 
    ON ALL SERVER  WITH EXECUTE AS 'sa'  FOR LOGON 
AS
BEGIN
    DECLARE @IPAddress NVARCHAR(50)

    SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',
                                               'NVARCHAR(50)') ;

    IF @IPAddress IS NULL OR NOT EXISTS (SELECT IP 
                                         FROM master..ValidIPAddress 
                                         WHERE IP = @IPAddress)
    BEGIN
        -- If login is not a valid one, then undo login process
        ROLLBACK --Undo login process

        INSERT INTO master..RejectedLogIn (IP) VALUES (@IPAddress)
    END
END

I'm getting no rows in RejectedLogIn and the user cannot login, getting a trigger error. It's probably failing when executing EVENTDATA(). If I comment out the body of the trigger it works.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Francisco Goldenstein
  • 13,299
  • 7
  • 58
  • 74

1 Answers1

1

I fixed the problem by setting the following variables:

SET 
          ANSI_NULLS, 
          QUOTED_IDENTIFIER, 
          CONCAT_NULL_YIELDS_NULL, 
          ANSI_WARNINGS, 
          ANSI_PADDING 
        ON;

I realized about this after checking the server logs:

SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Francisco Goldenstein
  • 13,299
  • 7
  • 58
  • 74
  • `SET QUOTED_IDENTIFIER` and `SET ANSI_NULLS` can be set when the trigger is created. This settings are remembered and used at execution time too. – Dan Guzman Sep 16 '15 at 01:14
  • Thanks for pointing that out and you're right but the parameter that made this trigger work correctly from MAC was CONCAT_NULL_YIELDS_NULL. I'm not sure if that can also be set at trigger creation and I don't know if that makes a big different. I'm not an expert in sql configuration parameter but at least they did the trick :) – Francisco Goldenstein Sep 16 '15 at 12:55
  • 1
    Only the 2 settings I mentioned earlier are "sticky" and override the session settings without an explicit SET in the trigger code. In addition to the 5 settings you listed, ARITHABORT ON and NUMERIC_ROUNDABORT OFF are required. Legacy clients might not have those set. – Dan Guzman Sep 17 '15 at 01:30