Login timeout expired LinkedServer SQL

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.

Leave a Reply

Your email address will not be published. Required fields are marked *