SQL 2008 R2 Connection Failed From Client

Over the weekend I have been working with a colleague on deploying a new Small Business Server 2011 for a client.

One of the things that slowed my down a little was being able to access the SQL database from a client. Even though it was set to allow remote connections, it didnt want to play.

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Thankfully I found this article, that helped me out and contains some useful tips on troubleshooting this error.

For those of you who just want to know the QuickNotes; my issue was that the SQL browser service needs to be accessible on port 1434 as I had a named instance not a standard one.

Excerpt:

In a word, the reason that we get this error message is the client stack could not receive SSRP response UDP packet from SQL Browser. It's easy to isolate the issue. Here are the steps:
1) Make sure your server name is correct, e.g., no typo on the name.
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

 

Click to Read All

Thanks to Xinwei Hong for writing the article.