SQL 2005: Errors due to SQL Server Browser not running

With SQL 2000 I was fairly proficient with troubleshooting connection issues, adjusting the client library network settings, and even dealing with dynamic ports and different SQL Server instances. But with SQL 2005, a few features were introduced that change how I troubleshoot connection issues. The features probably help to reduce the number of connection issues by eliminating most client configuration, but when issues do come up, my impression is that they can be more difficult to diagnose with SQL 2005. Fortunately, the resolution is typically simple, but diagnosing the problem can be very frustrating.

Two times in the last several months, I've run into a problem where I simply cannot connect to SQL Server 2005 from another computer. I know the SQL service is running, and I can login to SQL from the server itself, but client workstations and other servers are unable to connect. This has prevented me from logging into Dynamics GP, or it has prevented an eConnect integration from working.

The error message I receive looks something like:

SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [0xFFFFFFFF].

[SQL Native Client]Login timeout expired

[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.

Reading this message might have you think, "Ah, I forgot to enable remote connections!". But then you check the server settings, and you find that Remote Connections are in fact enabled. At this point, the error message becomes misdirection.

You can ping the server, and browse the server shares, so the network is okay. And strangely, you may find that every other workstation in the company can login to GP and connect to the SQL Server without any issues. So you are then possibly tempted to think that it is a workstation issue--again, more misdirection.

The next step is to Google the message, which will provide tons of fruitless newsgroup posts by people who have the exact same problem, but also lacked an answer. Until you stumble onto this excellent post by the Microsoft SQL Server Protocols Team (a protocols team!, who knew!).

It provides an exhaustive list of SQL Server 2005 connection errors, along with actual English descriptions of what those cryptic or kurt messages actually mean. For each error, they also provide some recommended steps for troubleshooting and resolving the error message.

In my case, I was running into lovely error Message #6. And their recommendation: You should enable the SQL Server Browser service on the server.

Sure enough, the SQL Server Browser service was not running. Once I configured and started the service, the login errors instantly disappeared and everything worked fine. Neither the error message, nor the symptoms (other workstations were fine) would have naturally led me to think of the SQL Browser service, and even though I have run into this exact issue before, I simply forgot the role that the Browser service plays with SQL 2005.

In my next post, I'll discuss the SQL Server Browser service, what it does, and why we need it.