How do I diagnose an issue connecting to an SQL Server 2000 database?

I have written several applications using Delphi.  These applications use ADO to access a SQL Server 2000 database on one of our servers.  One system has had the database access stop working.  All other systems are still working well.  When I try to use Management Studio Express to connect to the database from the computer I get:

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 Server Network Interfaces, error: 26 - Error locating Server/Instance Specified)(Microsoft SQL Server)

Now, there should be no discussion of whether or not I have TCP/IP enabled on the server as it is working with other systems.  There should be no discussion of turning off the firewall on the server as it is working with other systems.  From what I have seen there is an issue in how the client talks to the server using UDP.  How would I go about trying to diagnose this problem?
LVL 21
developmentguruPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
and the SQL Server has the config option "remote access" set to 1 (default is 0)

and that 2000 database - is it on a 2000 server or a 2005 server ? And when you say "no questions... ... server as it is working with other system" is that your local server, or the remote 2000 server, or are they one in the same ?

Could be udp - the server user has to have those same accesses as you might have - you can ping that server name from the client ? If you open a query window and run xp_cmdshell 'ping <servername>' does that also get results (command shell is an advanced option and needs sp_configure as well)
0
developmentguruPresidentAuthor Commented:
Thanks for the response.

--and the SQL Server has the config option "remote access" set to 1 (default is 0)
If this were set to 0 then wouldn't all of the 70 plus systems be unable to use the database server?

The database server is SQL Server 2000.  It is the only database I am trying to access (remote server).  I can ping the server from the client via CMD window (no dns issues).  I will try the

run xp_cmdshell 'ping <servername>'

in the morning.  I wanted to give you what I could before I left work tonight, in the hope that it might jog your thought processes.
0
Mark WillsTopic AdvisorCommented:
OK, just before you go, you have SSMS (express) on your XP machine, and trying to connect to a remote SQL Server 2000, and that is when you get that message. Is that it in a nutshell ?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

developmentguruPresidentAuthor Commented:
Exactly
0
developmentguruPresidentAuthor Commented:
 I do have some more information.  A co-worker of mine discovered that the programs on this system work well if we are signed in as administrator, not if we are signed in as anyone else (even if they have administrator privileges).  This prompted him to try using the links that had been set up on the administrator desktop when logged in as another user.  Much to my surprise, this worked.  I found out that he had been attempting to execute the program from his flash drive.

  We had also reinstalled Windows prior to that experiment so I think we may have been looking at two different issues.  Before the reinstall of windows the SQL Server Management Studio Express would not connect to one particular named instance, but would connect to a different one.  This program was installed on their hard drive, so no issue with being run from a flash drive.

  One instance we had an issue with database communications.  The other instance we had what appeared to be the same issue but only when running the application from a flash drive.  I would be interested in finding any insight (perhaps into the nature of Windows security) that would help me to explain (and, in the future, diagnose) these issues.

Points go to the person who can enlighten me.
0
Mark WillsTopic AdvisorCommented:
OK so the database comms is a no brainer (well only after you know the answer), so it really is the flash drive problem.

My first thought would say that the flash drive is not part of the shared disks (to the rest of the network) on a machine, remember what and how the flash drive works - it can be more like sharing a printer than a disk.
0
developmentguruPresidentAuthor Commented:
Ok... now it is a no brainer to you... Running these applications works from every other computer in the place.  Why not this one?  If you can give me some settings to check / change then I could see if it alleviates the problem.
0
developmentguruPresidentAuthor Commented:
sorry, should have read more like this...

Running these applications (from the flash drive) works from every other computer in the place.
0
Mark WillsTopic AdvisorCommented:
aaarrrggghhh, so it is not a no brainer - it did sound like the difference was the flash drive. Guess not...
0
developmentguruPresidentAuthor Commented:
OK, no real answer here on how to diagnose the problem.  When the administrators stop by and follow up on the question I would suggest either trying to forward it to some experts in the Windows OS, or deleting the question and refunding the points.
0
Jim P.Commented:
If the app is run from the flash/USB as admin it works?

But if you are logged in as regular Joe it fails?

That is a security issue then. I would look at the local group policy settings (gpedit.msc) and if there is a Domain Gp, delete the PC from the domain and re-add it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developmentguruPresidentAuthor Commented:
Thanks, I will give that a try.  Of course it will be over a week until I am able to.  I will let you know.
0
developmentguruPresidentAuthor Commented:
Given that jimpen says makes sense and offers a solution, I will accept it.  I do not have the time to test it at this point, but I cannot leave this open any longer either.  If I later find out that it did not work then I will add a comment.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.