Sample Header Ad - 728x90

SQL Server Logon Trigger Error after server restart

1 vote
0 answers
160 views
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)', 'sysname') set @LoginTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)', 'datetime') set @UserName = original_login() set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)', 'varchar(50)') set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)', 'sysname') set @ServerName = @LogonTriggerData.value('(/EVENT_INSTANCE/ServerName)', 'nvarchar(257)') set @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)', '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
Asked by Barry Fuhrmann (11 rep)
Sep 27, 2021, 05:39 PM
Last activity: Sep 28, 2021, 04:50 PM