Linked Server Login Timeout

Error:

Linked server timeout after 10 seconds.

TCP Provider: Timeout error [258]. [SQLSTATE 42000] (Error 258)  OLE DB provider “SQLNCLI11” for linked server “xxxxxx” returned message “Login timeout expired”. [SQLSTATE 01000] (Error 7412)  OLE DB provider “SQLNCLI11” for linked server “xxxxxx” returned message “Unable to complete login process due to delay in login response”. [SQLSTATE 01000] (Error 7412).  The step failed.

Solution:

Increase “Remote Login Timeout” time setting.

Note: This needs to be set on the destination of the linked server.

Database Server -> Properties -> Advanced -> Remote Login Timeout

Using script…

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'remote login timeout', 35 ;  
GO  
RECONFIGURE ;  
GO  

After setting the “Remote Login Timeout”, also look at the “Remote Query Timeout” option which defaults to 600 seconds.

Sources:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-login-timeout-server-configuration-option?view=sql-server-2017