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.
I fixed the problem by setting the following variables:
I realized about this after checking the server logs: