?
Solved

Connect to SQL server 2000 with TCP/IP not working

Posted on 2007-10-13
8
Medium Priority
?
277 Views
Last Modified: 2012-08-13
Hello,

I have a SQL 2000 box which works fine with Named Pipes.
I'm trying to connect via TCP/IP. However, I get a connection error message of SQL Error : 10054 and SQL Error 11 even when trying to connect locally when testing a System DSN against the database.
Tried other databases on the same box and get the  same connection error.

TCP/IP is installed in SQL Server Network Utility, default port.
Still no luck.
0
Comment
Question by:Waxed
  • 4
  • 2
7 Comments
 
LVL 8

Expert Comment

by:MrRobot
ID: 20071008
have you tried to turn off the windows firewall?
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20071018
go into the command prompt, and type

netstat -an

to see if the ports are open. you can also use a port monitoring tool like tcpview

0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20071025
is the sql server installation upgraded to the latest version? before a version sql server 2000 blocks tcp/ip connections due to a security problem.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Waxed
ID: 20071064
I have
TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
Don't see the local socket.
0
 

Author Comment

by:Waxed
ID: 20071068
SQL SP4 installed.
no windows firewall.
Box is Win2k/SP4
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20072141
hey waxed,

Sorry for the latency.

have you checked the sql server logs for further info on the error? I have read several ms forum threads and articles on the subject, it seems like nobody got a proper solution or the cause of the problem, seems to be an odbc issue with tcp/ip connections. may be related to the network libraries, re-installation of some components may work here.

Due to the error message, you can connect to the server, but your connection is getting reset by the server. So the server listens to the ports and get connections.

At first, we haven't talk about the details on the server configuration. The tcp/ip may be enabled but not for the network interface the software tries to connect, so try this one;

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2205120&SiteID=1

Secondly, try to directly connect using Enterprise Manager, to see if the problem is related with ODBC or a command sent by the application at the first connection.

It may be related with the instances, here is a good compiled list of connection issues, mostly valid for sql server 2000:
http://blogs.msdn.com/sql_protocols/default.aspx

Here is why it may be the result of an action :
http://msdn2.microsoft.com/en-us/library/aa937499(sql.80).aspx

There are also some threads related to this problem in Experts Exchange, they can give an idea, but they are mostly resolved in a different way, like moving the databases to another software, anyway here they are for your info:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21047923.html?sfQueryTermInfo=1+10054
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21784219.html?sfQueryTermInfo=1+10054
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20387457.html?sfQueryTermInfo=1+10054
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22891265.html?sfQueryTermInfo=1+10054
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22420035.html?sfQueryTermInfo=1+10054

In my opinion, it is probably related to network configuration, existance of unused protocols (named pipes etc), instances, or broken network library.

Please let me know any improvements. Good luck.

rob
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22422904
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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