[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-12
15
Medium Priority
?
321 Views
Last Modified: 2012-05-05
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
Comment
Question by:developmentguru
  • 8
  • 4
  • 2
14 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22943549
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
 
LVL 21

Author Comment

by:developmentguru
ID: 22944866
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22946373
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 21

Author Comment

by:developmentguru
ID: 22950298
Exactly
0
 
LVL 21

Author Comment

by:developmentguru
ID: 22994147
 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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22994582
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
 
LVL 21

Author Comment

by:developmentguru
ID: 23007105
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
 
LVL 21

Author Comment

by:developmentguru
ID: 23007123
sorry, should have read more like this...

Running these applications (from the flash drive) works from every other computer in the place.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23015018
aaarrrggghhh, so it is not a no brainer - it did sound like the difference was the flash drive. Guess not...
0
 
LVL 21

Author Comment

by:developmentguru
ID: 23078857
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 1500 total points
ID: 23173450
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
 
LVL 21

Author Comment

by:developmentguru
ID: 23222811
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
 
LVL 21

Author Closing Comment

by:developmentguru
ID: 31516073
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 23256651
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question