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.

This entry was posted in Tutorials/Tips and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s