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
Last activity: Sep 28, 2021, 04:50 PM