Version: Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) Mar 13 2020 14:53:45 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
This is a follow-up question to the question answered in the above referenced Forum entry. <thank you, that was very helpful!>
I implemented the attached logon trigger and it has been running with no errors in our development environment for the past 3 months.
Today I installed it in our production environment and it partially worked, but also logged thousands of failures. I don't understand why.
When I say "partially", it logged some 100's of user connections but failed on most.
Within the first minute, I got thousands of errors that look like this:
03/22/2021 10:01:09,spid645,Unknown,The client was unable to reuse a session with SPID 645<c/> which had been reset for connection pooling. The failure ID is 1. >This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
03/22/2021 10:01:09,spid645,Unknown,Error: 18056<c/> Severity: 20<c/> State: 1.
03/22/2021 10:01:09,Logon,Unknown,Logon failed for login 'XXX_user' due to trigger execution. [CLIENT: www.xxx.yyy.zzz]
03/22/2021 10:01:09,Logon,Unknown,Error: 17892<c/> Severity: 20<c/> State: 1.
I had to disable and drop the trigger from production.
I never received any errors like this on my development server. The only thing I can think of is that the production environment is very busy, where as development is relatively quiet all day. My TRY/CATCH print messages never appear in the error log, so I don't know where things were going wrong.
sp_helprotect output on my target table looks like this:
Thank you for any insight you might provide.
if (exists (select * from master.sys.server_triggers where type = 'TR' and name = 'my_trigger') ) begin drop trigger my_trigger on all server; end; go /** ** SQL Server LOGON TRIGGER: my_trigger ** ** Abstract: ** This LOGON TRIGGER is designed to log all connections to an audit table contained in the master database. ** ** The table name is master..sql_server_login_audit_t ** ** Note: ** ** This TRIGGER executes under the username LoginTrigger. This Login has VIEW SERVER STATE Permission which is required ** to select from sys.dm_exec_connections. ** ** CREATE LOGIN LoginTriggerUser WITH PASSWORD = 'xxxxxxxxxx'; ** ALTER LOGIN LoginTriggerUser DISABLE; ** CREATE USER LoginTriggerUser For LOGIN LoginTriggerUser; ** GRANT select,insert,update,delete ON sql_server_login_audit_t TO LoginTriggerUser; ** GRANT VIEW SERVER STATE to LoginTriggerUser; ** ** Useful SQL commands ... ** DISABLE TRIGGER my_trigger ON ALL SERVER; ** ENABLE TRIGGER my_trigger ON ALL SERVER; ** DROP TRIGGER my_trigger ON ALL SERVER; **---------------------------------------------------------------------------------------------------------------------------------- **/ create trigger [my_trigger] on all server with execute as 'LoginTriggerUser' -- use LoginTriggerUser because it has VIEW SERVER STATE permission, which is required for sys.dm_exec_connections for logon as begin set nocount on; declare @Login_Name varchar(60), @Client_Net_Address varchar(60), @Host_Name varchar(60), @Program_Name varchar(255), @Client_Interface_Name varchar(255), @Now datetime, @SecondsSinceStarted bigint, @SqlServerStartSeconds bigint = 300; -- 5 minutes (60 seconds * 5 minutes) /* * Check to see if SQL Server has just restarted. If so, allow immediate connection without logging. The assumption is that we don't want the * trigger to get overwhelmed with thousands of xxx reconnections all at once. */ select @SecondsSinceStarted = datediff(second, si.sqlserver_start_time, getdate()) from sys.dm_os_sys_info si; if ( @SecondsSinceStarted < @SqlServerStartSeconds ) return; /* * Check if the incoming connection SPID is a user process. If not, get out of here quietly. */ if (exists ( select * from sys.dm_exec_sessions s inner join sys.dm_exec_connections c on (s.session_id = c.session_id) where s.is_user_process = 1 and s.session_id = @@SPID and upper(s.host_name) not in ('HOST1','HOST2','HOST3','HOST4','HOST5','HOST6','HOST7') ) ) /* * If we got this far, we are a valid user_process. Let's get some key identifying information */ begin select @Login_Name = ORIGINAL_LOGIN(), @Client_Net_Address = c.client_net_address, @Host_Name = s.host_name, @Program_Name = s.program_name, @Client_Interface_Name = s.client_interface_name, @Now = getdate() from sys.dm_exec_sessions s inner join sys.dm_exec_connections c on (s.session_id = c.session_id) where s.session_id = @@SPID; /* * Check to see if we've seen this connection before ... if so, update the # of attempts, and login_dt */ if (exists ( select * from master.dbo.sql_server_login_audit_t where login_name = @Login_Name and host_name = @Host_Name and client_net_address = @Client_Net_Address and program_name = @Program_Name and client_interface_name = @Client_Interface_Name ) ) begin begin try update master.dbo.sql_server_login_audit_t set attempts = attempts + 1, login_dt = @Now, current_spid = @@SPID where login_name = @Login_Name and host_name = @Host_Name and client_net_address = @Client_Net_Address and program_name = @Program_Name and client_interface_name = @Client_Interface_Name; end try begin catch print 'Failed to UPDATE sql_server_login_audit_t: ' + @Program_Name + ', login ' + @Login_Name + ', Error ' + convert(varchar(50), ERROR_NUMBER()) + ', Error Msg ' + ERROR_MESSAGE(); end catch end; else /* * Ok, this is a new connection, so let's log it to our table and start tracking it. */ begin begin try insert into master.dbo.sql_server_login_audit_t (login_name, host_name, client_net_address, program_name, client_interface_name, attempts, login_dt, current_spid) values ( @Login_Name, @Host_Name, @Client_Net_Address, @Program_Name, @Client_Interface_Name, 1, @Now, @@SPID ); end try begin catch print 'Failed to insert sql_server_login_audit_t: ' + @Program_Name + ', login ' + @Login_Name + ', Error ' + convert(varchar(50), ERROR_NUMBER()) + ', Error Msg ' + ERROR_MESSAGE(); end catch end; end; end; go