Saturday, March 7, 2009

How to Fix Error: 26 - Error Locating Server/Instance Specified - Microsoft SQL Server

One more for ya (btw, if anyone is wondering why I keep posting these notes, it's because I know I'll encounter it again in the future and will never remember what happened).

Symptom


Your web application gets an Error: 26 - Error Locating Server/Instance Specified. You know the database is up and working, and that the named instance is working. You've ruled out spelling errors (or the application has been up and running and is now suddenly returning this error).

Other factors


You've tried to connect via odbcad32.exe, but it worked fine, or it didn't work. Or on some computers it worked and on others it didn't.
You've connected to the database via SQL Management Studio from your development PC and it works.
You've also tested connecting with the limited rights ID that the web application uses. It worked or didn't work (or worked on some machines and not on others).
The instance you are connecting to is not the default instance, it's a named instance other than the default (In other words, your server is connected to using the "SqlServer\Instance" convention).
You've checked your database server and SQLBrowser is running (if not, that's your fix, start it up!)

The Fix


Take a look at the other factors since those are really the first steps to troubleshooting this sort of a problem. Take the web app out of the picture, check that services are running, etc.
Once you've gotten that far, cycle the SQLBrowser service and refresh your web app, it'll, most likely, start working again.

Why did that work?


SQLBrowser helps your client to find the named instance on the SQL Server. If you hunt around in your logs you may find an event that indicates that the SQLBrowser service has decided it's no longer going to respond to hosts because it encountered an error.
Folks who write services know that there's a "good way to handle errors" and a "bad way". We'll just say that leaving the service in a running state and having it give little to no indication that it's died is not the "good way". Cycling the SQL Browser service, if it is working, may result in other applications not being able to connect to the database for the seconds that it is unavailable though as you may have noticed in the Other Factors section, this isn't a guarantee. In my case, I was able to connect to all instances on a few PCs, including my development box despite the SQL Browser service being dead.

What's the actual cause


I'm not completely sure. In my case the issue was immediately preceded by a memory error on the server, so that was what went wrong. Your case may be different.
Happy Saturday!

1 comment:

Linglom said...

Hi, I posted an article similar to this post but has more detail steps. It may be useful to users, visit Enable remote connection to SQL Server 2005 Express