question

ChuckHamilton-7817 avatar image
0 Votes"
ChuckHamilton-7817 asked CarrinWu-MSFT commented

Why do I get Error 18056 failure id is 1 (The client was unable to reuse a session ...)

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ChuckHamilton-7817, we have not get a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @ChuckHamilton-7817,

There have 2 reasons may cause Error 17892:
1.The trigger tires to insert data into a table that does not exist.
2.The login does not have permissions to the object that is referred to by the logon trigger.

From your script, we can rule out the first reason, so have you check permissions for login? And here have 3 Scenarios may cause this issue:
1.You currently have access to an open session to SQL Server under an admin account
2.You do not have any current session that is open under admin privileges, but Dedicated Administrator Connection (DAC) is enabled on the SQL Server.
3.You neither have DAC enabled on your server nor have an existing admin session to SQL Server.

Please refer to MSSQLSERVER_17892 to get more details.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.