SQL Server EVENTDATA() not working from MAC SQL Pro for SQL Server

359 views Asked by At

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.

1

There are 1 answers

3
Francisco Goldenstein On BEST ANSWER

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.