I had a database crash, after restoring it my linked queries which were working perfectly fine started to give this error
Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
I spent quite a bit of time on it but couldn’t find the cure. Eventually found this stackexchange post which sort of put me in right direction. The answer says
SQL Agent will use Shared Memory to connect to the local server. The Instances will attempt to use named pipes to talk to each other. You can adjust this by creating an alias in the SQL Server Client Network Utility (cliconfg.exe) and setting the alias to use TCP.
What I did here is run cliconfg.exe, added a server alias and set it to use TCP, I then went to SQL Server Configuration Manager and enabled TCP (which was disabled) and the query ran flawlessly!
No exactly sure if adding alias was required but the issue is not resolved. This is for SQL Server 2005.