This message has come across my desk a couple of times in the last week and when that happens I like to produce blog content.
The error is when you are trying to use a pooled connection and the reset of the connection state encounters an error. Additional details are often logged in the SQL Server error log but the 'failure ID' is the key to understanding where to go next.
Event ID: 18056
Description: The client was unable to reuse a session with SPID 157, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Map the failure ID to the following (SQL 2008 and SQL 2008 R2 failure id states)
Default = 1,
RedoLoginException, 29 (Kind of generic but you can use dm_os_ring_buffers to help track down the source and perhaps -y)
Login assignments from master
LoginSessDb_UseDbImplicit, 40 (I can cause this by changing the default database for the login at the server)
Connection string values
RedoLoginSessDb_UseDbExplicit, 46 (Data specificed in the connection string Database=XYX no longer exists)
Common Windows API calls
Error: 18056 Severity: 20 State: 46.
The client was unable to reuse a session with SPID 1971 which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
State 46 = x_elfRedoLoginSessDb_UseDbExplicit = 0n46
There is only one place in the code (We are simply trying to execute a usedb and getting a failure.) that sets this state and it is after we have printed the message 4060 to the client that we could not open the database or the user does not have permissions to the database. Since there are not messages about a database going offline or being recovered and this connection as already established – “Would there have been any permission changes at this time to prevent this login from accessing the database?”
I tried this with a test application.
Connection pool using database dbTest
User RDORRTest with default database dbTest
When I drop the user in the database dbTest the client starts getting the errors as I expected to see.
07/28/10 07:56:45.391 [0x00001E5C] SQLState: 28000, Native Error: 18456 [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'RDORRTest'.
07/28/10 07:56:45.410 [0x00001E5C] SQLState: 42000, Native Error: 4064 [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open user default database. Login failed.
My SQL Server error log shows
2010-07-28 08:02:40.41 Logon Error: 18456, Severity: 14, State: 50.
2010-07-28 08:02:40.41 Logon Login failed for user 'RDORRTest'. Reason: Current collation did not match the database's collation during connection reset.
2010-07-28 08:02:40.41 spid53 Error: 18056, Severity: 20, State: 50.
2010-07-28 08:02:40.41 spid53 The client was unable to reuse a session with SPID 53, which had been reset for connection pooling. The failure ID is 50. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
I password change for the login at the server generated state 8.
If I rename the database I don’t get any information about the rename in the error log and I start getting connection failures.
All my attempts to far had been when the login was setup with a default database. However, to get to the 46 condition I had to specify the DATABASE for the connection string.
Now all I had to do was drop the user from the database and I get state 46.
2010-07-28 08:29:51.61 Logon Error: 18456, Severity: 14, State: 46.
2010-07-28 08:29:51.61 Logon Login failed for user 'RDORRTest'. Reason: Fa iled to open the database configured in the login object while revalidating the login on the connection. [CLIENT: 22.214.171.124]
Added the user back and I no longer get the error and the connections continue their work.
Bob Dorr - Principal SQL Server Escalation Engineer