Sql Server Browser needs to be started for remote connection to a named instance

A default installation of Sql Server 2005 is pretty easy. After install, you can connect to it remotely, either through Sql Server Management Studio or sqlcmd. However, if you install a named instance on the same box, you may not be able to connect to it remotely, although connecting to it locally would still work.

This is the error message if you try a remote connection to the named instance via Management Studio:


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. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

——————————
BUTTONS:

OK
——————————

This is the error message when you try the same connection via sqlcmd:


C:\>sqlcmd -S MyServer\MyNamedInstance
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

It turns out that for named instance, Sql Server Browser needs to be running to facilitate remote connection. To start it, log on to the server, then go to Sql Server Configuration Manager, and check Sql Server Browser under Sql Server 2005 Services. You may not be able to start it right away, because it is disabled. To solve that, go to its property page, click on the second tab (Service tab), and change the Start Mode to Automatic. You can then start it. Remote connection to named instance should work afterwards.

One thought on “Sql Server Browser needs to be started for remote connection to a named instance”

  1. Thank you very much for posting about the SSIS clustering connection issue. I resolved my problem almost instantly. There are a lot of tricks to the non-cluster aware services!

    We had another similar issue with database mail, where we had to create an alias pointing from the virtual servername to the SQL instance name.

    Very Much Appreciated!

    Thanks again, Todd

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.