SQL Server Logon Trigger Error after server restart

Question

I have a logon trigger running that is executing as another user to log connection details. It works totally fine without issue except right after a server reboot. At that point, I have to connect to the server via the DAC and disable it. Then I can just enable it again and everything is fine.

The error I see in the server logs says:

The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported.

I'm not sure why it would only run into this issue right after a reboot. Any idea what the issue might be?

create trigger [LogonAudit]
on all server with execute as 'svc_LogDBUser'
for logon
as
begin
begin try
declare @LogonTriggerData xml
       ,@SPID varchar(50)
       ,@EventType varchar(50)
       ,@LoginTime datetime
       ,@UserName varchar(50)
       ,@LoginName varchar(50)
       ,@LoginType varchar(50)
       ,@ServerName varchar(50)
       ,@HostName varchar(50)
       ,@ClientHost varchar(50)
       ,@AppName varchar(500)
set @LogonTriggerData = eventdata()
set @SPID = @@spid
set @EventType = @LogonTriggerData.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')
set @LoginTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @UserName = original_login()
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
set @ServerName = @LogonTriggerData.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(257)')
set @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @HostName = host_name()
set @AppName = app_name()
insert into LogDB.dbo.LogonAudit
    (SPID
    ,EventType
    ,LoginTime
    ,UserName
    ,LoginName
    ,LoginType
    ,ServerName
    ,HostName
    ,ClientHost
    ,AppName)
values
(@SPID, @EventType, @LoginTime, @UserName, @LoginName, @LoginType, @ServerName, @HostName, @ClientHost, @AppName)
end try


begin catch
--don't worry about it
end catch
end
go

SQL Server Logon Trigger Error after server restart

0 Answers0