SSPI Handshake failed - Cause Known - But why so many?
-
Hi,
We have a .NET application that uses integrated security when users connect to the database, and we have found that if a users Windows password expires while they have the application open it causes (understandably) login failure messages to be logged to the SQL error logs. The issue we have is the sheer volume of error messages - dozens and dozens of errors every second, meaning after a couple of minutes there are thousands of messages bloating the SQL error log.
Resolving the errors is simple - get on the phone and ask the user to change their password - but we're trying to understand why there are so many errors being generated. The developers are stumped as there doesn't seem to be any application side logic that would cause it to try and continually authenticate. In testing after forcibly killing a user's session the application did not try and re-connect.
Is this a SQL side problem? Is there something in the .NET framework that could be causing this?
Any clues or pointers gratefully received
!!!An example error message, just for completeness:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: xx.xx.xx.xxx]
Many thanks,
Matt.
-
I am guessing that the application is not trying to reconnect as it is already connected. the problem is that the credentials of the account have changed and I believe SQL server will try and validate those credentials for every query.
You mention that you see dozens of errors every second, under normal circumstances would you see dozens of queries a second for a valid connections? What I think is happening it the application is continuing to run "normally"(ask developers what happens, if anything, in app when it receives this error) but each query that is executed is failing to have credentials validated.
-
Thanks for your reply.
It's probably more activity than I would expect to see, but I'll refer it back to the developers to confirm. If the issue does lie with SQL trying to re-authenticate an existing connection, I'm wondering if I can configure some kind of SQL Agent alert response to identify and kill the connections. The errors don't identify the sessions involved only that there has been a failed login, and if we're killing sessions we better make sure they're the right ones
Thanks again.
-
If this is what is occurring then ideally you would want the application to close the connection when it receives the error.
This would be a simple thing to test to identify what is actually going on. Login to application with test account, then verify connection with something like:
SELECT session_id,login_name,last_request_end_time,host_name, program_nameFROM sys.dm_exec_sessionswhere login_name = 'TestAccount'while still logged in turn on extended events or profiler trace to monitor activity of test account. once monitoring session is setup, change the password of test account and then verify if connections are still active with query above and look through what occurred in monitoring session.
here is an example extended events session that could help in monitoring. may need to be tweaked for your needs
CREATE EVENT SESSION [tuning] ON SERVERADD EVENT sqlserver.error_reported(ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'testaccount')),ADD EVENT sqlserver.module_end(SET collect_statement=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'testaccount')),ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'testaccount')),ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'testaccount')),ADD EVENT sqlserver.sql_batch_completed(ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'testaccount')),ADD EVENT sqlserver.sql_statement_completed(SET collect_parameterized_plan_handle=(0)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'testaccount'))ADD TARGET package0.ring_bufferWITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply