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?
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?
ASKER
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.
--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.
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 ?
ASKER
Exactly
ASKER
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.
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.
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.
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.
ASKER
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.
ASKER
sorry, should have read more like this...
Running these applications (from the flash drive) works from every other computer in the place.
Running these applications (from the flash drive) works from every other computer in the place.
aaarrrggghhh, so it is not a no brainer - it did sound like the difference was the flash drive. Guess not...
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
Glad to be of assistance. May all your days get brighter and brighter.
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)