Home / server / Questions / 338391
Accepted
sqlreader
Asked: 2011-12-07 17:24:07 +0800 CST

Native SQL logins timing out at login validation phase

  • 772

The default SQL Trace of my instances shows the following issue for native SQL logins (non-domain accounts):

Network error code 0xe8 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 18230 ms, enqueued 2 ms, network writes 5 ms, network reads 128 ms, establishing SSL 71 ms, negotiating SSPI 0 ms, validating login 18096 ms. [CLIENT: ]

I've done some digging and haven't been able to find what tolls to use or metrics to gather to better assign the login validation performance issue.

This is SQL Server 2005 with Service Pack 3 on Windows 2003 R2 with Service Pack 2 on VMware 4.1.0 with the latest VMware tools installed.

sql-server sql-server-2005 windows-server-2003

2 Answers

  1. What edition of SQL Server? Have you checked whether you have logon triggers enabled? (Your logon trigger could have a performance issue.) 

    USE master;
GO
SELECT * FROM sys.server_triggers
  WHERE OBJECT_DEFINITION([object_id])
  LIKE '%FOR%LOGON%';

    Definitely sounds like most of the time was spent validating login - so if it's not a logon trigger, then maybe it's some other resource constraint - for example, how many logins in the system?

    SELECT COUNT(*) FROM master.sys.server_principals;

    How many databases?

    SELECT COUNT(*) FROM master.sys.databases;

    How many of the databases associated with the SQL Server login you are using are set to auto-close?

    SELECT COUNT(*) FROM master.sys.databases WHERE is_auto_close_on = 1;

    Does the same symptom happen if you log in with sa, or is this isolated to a specific SQL login?

    • 1
  2. Best Answer

    On a hunch, we remove the Symantec Endpoint Client which was a full deployment. We've had issues with the Network filter-level driver in the past.

    On reboot we saw significant improvement in general performance and the login timeouts. We'll adjust the SEP deployment for AV only to eliminate the troublesome network filter driver.

    • 0