I am trying to log users logons to my Sql Server (A) in another Sql Server (B, different location physically), but when I try to log in I get the error Login failed ... due to trigger execution and this is the body of my logon trigger:
alter TRIGGER [tgr_blocker]
ON ALL SERVER
FOR LOGON
AS
BEGIN
--
declare @IP nvarchar(50)
declare @UN nvarchar(50)
declare @HostName nvarchar(100)
declare @ProgramName nvarchar(100)
set @UN = ORIGINAL_LOGIN()
select @IP = client_net_address FROM sys.dm_exec_connections WHERE
session_id = @@SPID
SELECT @HostName = host_name, @ProgramName = program_name FROM
sys.dm_exec_sessions WHERE session_id = @@SPID
insert into [LinkedServerIP].SecurityLog.dbo.AAA_Logger
(IP,HostName,UserName,Session,Date,ProgramName,ServerName,Result)
values (@IP,@HostName,@UN,@@SPID,GETDATE(),@ProgramName,@@SERVERNAME,'Successful')
end
and when I test the insert in a query it works, but when I log in it does not work and results to the error, I thought maybe Sql Server can not access Linked Servers during Logon Triggers, is that true? If not where else should be the problem?
