SQL Server: Frequent Login timeout expired in Linked server

Question

I am using SQL Server 2012. I have scheduled a job to run every 1 hour, with 1 step calling 1 simple Stored Procedure. The Stored Procedure migrates around 1K data from production server to our local server. Linked server has been established successfully. The query used in Stored procedure looks like below.

SELECT ID,Col1,Col2,Col3,Col4,Col5 into #temp from [111.111.111.111].ProdServer.dbo.SourceTable

Insert into Localtable(ID,Col1,Col2,Col3,Col4,Col5)
SELECT ID,Col1,Col2,Col3,Col4,Col5
FROM #temp where ID NOT IN (select ID from Localtable)

The job execution sometimes takes 25-30 secs which will successfully executed. Sometimes it takes 48-50 seconds which results in failure. Please refer failure message below and other details in above screenshot.

Failure Message:

Executed as user: NT AUTHORITY\NETWORK SERVICE. Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 42000] (Error 53) OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412). The step failed.

More Details

• The table is locked by nobody and no queries run on it.
• In Linked Server Properties, both Query Timeout and Connection Timeout has been set to 0.
• In Server properties remote query Timeout is also set to 600 Secs.

As last option I tried to change Connection Timeout Settings from its default value 15 to 6000 or higher. I could not achieve it. Please suggest solution to execute my scheduled jobs successfully even it takes 48 minutes or slight more.

Right Click on server > Click on properties > Click view connection properties on left pane under connection - to see Connection Timeout Property

1 new observation: The job is failing if it takes 48 seconds or more to run.

Answer 1

I am here answering my own question. After so many observations and experiments with searching over the internet, I found solution to this issue. You need to run this simple query

USE Yourdatabase;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO

The setting takes effect immediately without restarting the server.

Please refer Configure the remote query timeout Server Configuration Option and sp_configure.

Answer 2

Right click the server, open the Server Properties, navigate to Connections and ensure that Allow remote connections to this server is checked.

Also, check the firewall settings.