• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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?
0
developmentguru
Asked:
developmentguru
  • 8
  • 4
  • 2
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now