I see this error occasionally in my SQL Server error log usually accompanied by error 17892.
Error: 17892, Severity: 20, State: 1.
Logon failed for login 'redacted\redacted' due to trigger execution. [CLIENT: 10..x.x.x]
Error: 18056, Severity: 20, State: 1.
The client was unable to reuse a session with SPID 73, 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.
I don't have access to application logs just the SQL error log. What would cause this?
I do have a logon trigger that that simply checks if the original_login() contains a particular string and if it does ensures that the connection is encrypted (I dont want the overhead of encryption for all connections, just a small fraction of them). If the criteria is met and the connection is NOT encrypted it does a ROLLBACK which stops the logon.
I get 1000s of successful logins per day but occasionally I get one where the criteria is NOT met but I still get the error. Here's the trigger code.
/ Ensure all REDACTED logins are encrypted /
CREATE TRIGGER [verify_encryption] ON ALL SERVER
WITH EXECUTE AS 'REDACTED'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() like '%REDACTED%' AND
(
SELECT encrypt_option
FROM sys.dm_exec_connections
WHERE session_id = @@spid
) <> 'TRUE' BEGIN
declare @message nvarchar(200)
set @message = 'REDACTED login attempted without encryption from host '+host_name()
raiserror (@message,10,1)
ROLLBACK;
END
