Users time out (or it's very slow) authenticating to SQL Server, but RDP authenticates immediately

Question

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].

Answer 1

This wait accumulates while SQL Server is waiting for an Active Directory query to complete. It occurs while listing group members like in in the xp_logininfo stored procedure and the sys.login_token management view. This wait frequently appears along side PREEMPTIVE_OS_LOOKUPACCOUNTSID. If Windows Authentication is being used some values in this wait type are expected. If values are high see the suggested solutions below. Pre-emptive waits are different from regular (emptive) waits as their execution is controlled by an external processes such as the operating system.

• Reduce the number of Active Directory queries
• Investigate Windows Event Viewer for any Active Directory messages
• Fix any Domain Controller performance issues Investigate network performance between the SQL Server and Domain Controller
• Simplify Active Directory group membership. Avoid excessive nested groups
• Review site topoly in AD site and services

To narrow down and prove that this issue occurs because of Active directory performance. Login to SQL server using the startup account of SQL Server and execute below query when you notice PREEMPTIVE_OS_AUTHORIZATIONOPS wait type and compare the times printed. It will give you the time it takes for SQL Server to complete the AD calls.

create procedure PREEMPTIVEOSAUTHORIZATIONOPS  with execute as self
as
set nocount on
select CONVERT(varchar, getdate(), 126) PREEMPTIVEOSAUTHORIZATIONOPS
go

then

print convert(varchar, getdate(), 126)
exec dbo.PREEMPTIVEOSAUTHORIZATIONOPS;    
print convert(varchar, getdate(), 126)

go

sources: https://sqlserverscribbles.com/2013/09/26/preemptive_os_authorizationops-waits-in-sql-server/