The authentication timeouts and slowness happen for both local SQL logins and Windows authentication using AD. Rebooting "masks" the problem for a while, but after 6-8 hours, we see them again.
The whole time, I see high numbers of PREEMPTIVE_OS_AUTHENTICATIONOPS waits.
It can't be strictly an AD or routing issue because authentication happens instantly during RDP login using a domain account, even after many hours or days of uptime.
telnet to the relevant ports on the DC instantly return success, so the ports are open.
I've gone through everything on this page, to no avail: https://samzsimplesql.wordpress.com/2015/09/01/troubleshooting-connectivity-issues-timeout-error-258-unable-to-complete-login-process-due-to-delay-in-prelogin-response-pre-login-handshake-failed-2/
- SQL Server: 2008 R2 EE 10.50.6560.0
- Windows: 2008 Ent
- AD: 2012 R2
(Yes, I know those are ancient versions, but have no control over that.)
What else can I look at?
EDIT: There are about 1000 connections (it varies throughout the day). "All" (except for the few from me) logins are from the same domain account.
The problem started last Monday.
There are no errors in the SQL Server error log. This is the only (and there were a lot of them!) message in the DB server's event viewer:
The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
Severity: 16 Error:258, OS: 258 [Microsoft][SQL Server Native Client 10.0]Shared Memory Provider: Timeout error [258].
PREEMPTIVE_OS_AUTHENTICATIONOPS
does not apply to SQL authentication, although SQL auth could be a victim. I suggest you add more info to your question, such as specific error messages and metrics like the number of concurrent connections with that wait.sp_WhoIsActive
while trying to login with another session, particularly via a SQL account, to see if it exposes any interesting wait types or other potentially useful information. I've never had this kind of issue before, so not sure how effective it'll be, but probably worth a shot.