I have set up a Linked Server between 2 SQL Server 2008 R2 databases (both 10.50.1600).

The linked server worked normally for several weeks until I received reports that users were not able to hit one of the Linked Servers. I got this report on Friday.

When investigating via SQL Server Management Studio, when expanding the Catalogs for the linked server, I noticed that all the databases that the account being used for the linked server normally has access to were gone. Instead there was a "default" database listed.

I dropped and recreated the linked server using the same account used previously, and it didnt work the first several times I tried. I got "Linked server error - OLE DB provider "SQLNCLI" for linked server "" returned message "Login timeout expired" exceptions until finally it decided to "take".

It worked for little while, but this morning I observed issues with the linked server again and expanded the server catalogs and again got the "default" database with no other databases.

This time, I refreshed the view, and now all databases are displayed, and the jobs we run that depend on this linked server are executing.

SQL Server logs on either server do not show any exceptions surrounding Logon events.

In light of the fact that there are no logged events, where are some other places I should look to start troubleshooting this, knowing that no hardware configuration changes have been made that could influence this behavior?

  • Does the login associated have a password that expires? – Steve Mangiameli Jan 16 '15 at 21:45
  • @SteveMangiameli no. In fact, we have as a workaround configured the job to run as soon as it fails on login. It fails after a brief period (15 seconds?) but then will work on subsequent login attempts. – Elliot Rodriguez Jan 19 '15 at 18:00
  • 1
    Sounds like you may have a DNS issue. I am not a network guy, but check out this link - sql-server-performance.com/forum/threads/…. Similar issue as yours and adding the IP/serverName to the client's host file fixed the issue. – Steve Mangiameli Jan 20 '15 at 16:08
  • @SteveMangiameli That is an interesting read, and something I hadn't considered. I'm going to pass this on and see if this helps. Thanks. Unfortunately I don't have the rep to vote your comment up. – Elliot Rodriguez Jan 20 '15 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.