Can't get it to listen on TCP port 1433

Been having SQL Server 2000 running for some time now, but recently it stopped listening on TCP port 1433, the log reports its listening on shared memory, Named pipes and Rpc, but no sign of 127.0.0.1 port 1433 or any errors to say why it won't listen.

I've done a netstat -na and nothing else is listening on that port, tried restarting using the enterprise manager, gonna try restarting the entire Server when everyone has gone home, but I'm pretty sure its been restarted recently.

All the other archive logs going back a few days show its not listening.

Yes, I have used the Server network utility to make sure TCP/IP is enabled and set to 1433, even added a comma and 1434 to see if it will listen on multiple ports, no go.

Any help?
What service pack do you have installed?|||Have you told your firewall to allow connections to Microsoft SQL Server?|||SQL Server 2000 - 8.00.194 build 3790 Service pack 1

Mmmm, isn't SP3 out?
Lets hope it doesn't break it even more, as after the restart of the entire server last night, its now not responding to TCP connections locally 127.0.0.1

Not sure about the firewall, I'll have to check, its been fine with it for the past few years.

|||

SP4 has been out for quite some time. Here is a handy web site which lists all of the hot fix and service packs which have been released http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx.

Start by upgrading to SQL 2000 SP4. Odds are a Windows 2000 hot fix shuts down SQL from talking over the net if prior to SP3 as there is a major security flaw prior to SP3 which the SQL Slammer worm expliots.

Can't connect via Enterprise Manager

I had a similar problem that was due to the server not
using the proper TCP/IP port. I am guessing that one of
your servers is using a different port, (or none at all),
so it can't be seen by clients looking for the default
port number 1433, at least that was the problem in my case.
Check your Windows registry settings for the following
entries, (this is the key for a default instance, but
there are similar key paths for multiple instances):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServ
er\SuperSocketNetLib\Tcp\TcpPort
This value is the port that SQL Server will listen on. In
some cases it does not get set up correctly on the
install. The default is a decimal value of "1433" (if it
was blank then the server was only using shared memory
protocol)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\Su
perSocketNetLib\Tcp\DefaultPort
This value is the port that clients use to communicate
with the server. It normally will also be "1433" (decimal).
If you do change these values you should shut down the
server service first and check the registry again after
the server is running again... I had a heck of a time
getting my server to use port 1433, as it kept switching
to port 2433 after the server started up. (Not sure if
it's necessary, but I also put a "" in the TcpDynamicPorts
variable) to make sure dynamic port assignments is off.
Alternatively, if you do have different port settings and
need to keep them as they are, you can create alias
entries on your clients that will associate the proper
protocols and ports for each server.
HTH,
TK
>--Original Message--
>Hi All,
>We have a MSDE on a W2K server installed. It is running
fine and the
>application can access it without a problem. We have
another server on that
>same subnet that has an SQL 2000 server installed. When
trying to connect to
>the MSDE via the Enterprise Manager, only the local SQL
shows up and we
>cannot connect to the MSDE instance.
>When we go to the ODBC on the MSDE box we see both SQL -
the local and the
>remote one. On the ODBC on the other server we only see
the local one.
>Does anybody have an idea what is going on?
>Thanks,
>Claus
>
>.
>
Thanks for the reply. I finally was able to connect. I still don't have any
idea what the reason was. I rebooted both servers and it worked without
making any changes. No entries in the event logs.
MS never stops surprising me.
thanks again to all of you for your good advise,
Claus
"TK" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9f01c4a58c$61560b90$a301280a@.phx.gbl...[vbcol=seagreen]
> I had a similar problem that was due to the server not
> using the proper TCP/IP port. I am guessing that one of
> your servers is using a different port, (or none at all),
> so it can't be seen by clients looking for the default
> port number 1433, at least that was the problem in my case.
> Check your Windows registry settings for the following
> entries, (this is the key for a default instance, but
> there are similar key paths for multiple instances):
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServ
> er\SuperSocketNetLib\Tcp\TcpPort
> This value is the port that SQL Server will listen on. In
> some cases it does not get set up correctly on the
> install. The default is a decimal value of "1433" (if it
> was blank then the server was only using shared memory
> protocol)
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\Su
> perSocketNetLib\Tcp\DefaultPort
> This value is the port that clients use to communicate
> with the server. It normally will also be "1433" (decimal).
> If you do change these values you should shut down the
> server service first and check the registry again after
> the server is running again... I had a heck of a time
> getting my server to use port 1433, as it kept switching
> to port 2433 after the server started up. (Not sure if
> it's necessary, but I also put a "" in the TcpDynamicPorts
> variable) to make sure dynamic port assignments is off.
> Alternatively, if you do have different port settings and
> need to keep them as they are, you can create alias
> entries on your clients that will associate the proper
> protocols and ports for each server.
> HTH,
> TK
> fine and the
> another server on that
> trying to connect to
> shows up and we
> the local and the
> the local one.

can't connect to SQLEXPRESS locally

I have SQLEXPRESS installed on a Windows 2003 Server. The SQL Tools show that TCP and Named Pipes are Enabled. I can't connect using either the command line or the Mgmt Studio with either of these protocols.

Following is excerpt from program I'm trying to install:

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

And this from the command line

C:\Documents and Settings\edulog>sqlcmd -E -S localhost
HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively re
fused it.

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Thanks,

That's expected. Enable shared memory and you'll be good to go.

Paul

|||The MS documentation ( in the SQL Server Express Readme) says shared memory is enabled by default. I am trying to connect locally, and have named pipes, and tcp enabled as well as shared memory. All 3 methods fail.

Wendell

cant connect to Sql server 2000 sp3a in windows 2003 server

There is a way to find out - I think you should find out if the clients are
using "Named Pipes" or TCP/IP for connectivity.
Bring up the ODBC connection, then on the second page you should see a
button "Client connectivity". Go into this and make the bullet TCP/IP.
Please let me know if this fixed your problem.
=-Chris
On the clients that cannot connect, click the box "Client configuration"
"Rick" <ricardo.blanco@.indap.com.mx> wrote in message
news:ur4WMjStEHA.2688@.TK2MSFTNGP14.phx.gbl...
> hi guys im having troubles to connecto to sql server, my clients are win
xp
> and win 2000 pro, before i had the sql server 2000 on a windows 2000
server
> and all worked fine, but two days ago i changed to windows server 2003
> standard and the problems began...
> when i try to connect to sql by odbc or by another way its the screen that
> appears, the extrange is that in downstair there are 4 pcs and that 4 can
> connect to sql, (all winXP Sp1 in spanish), upstairs there are 3 winXp
> spanish, 2 winxp english(1 with SP2 the other Sp1) and 1 win2000 Sp4 pro
> english and just 1 XP in spanish can connect the other 2 not, i thought it
> was a problem with the server but now i connect a new laptop with winXP
> spanish SP2 and it works fine... so i dont know what to do, all pcs have
> mdac 2.8
> now i thought its problem with clients but i cant find difference between
> pcs except for the language
> what can be'
> i tried to connect by IP, i checked the port and nothing works =(
>
>Chris thanks a lot, but ive found other way to solve it, i know maybe its
not the more correct but i reinstalled the Sql server because i found at
microsofts web a tool to check the ports, its name is PortQuery, with it
you can check for Sql server if it is running on a server, or network
connectivity and other things, i saw that the server doesnt aswer to the
tool, the ports 1433 and 1434 not respond, so i decided to reinstal Sql
server, and it solved the problem =)
the tools url is http://support.microsoft.com/kb/310513/EN-US/
Rick =)
"Christopher Conner" <someone@.someplace.com> wrote in message
news:%23u554sTtEHA.3872@.TK2MSFTNGP15.phx.gbl...
> There is a way to find out - I think you should find out if the clients
> are
> using "Named Pipes" or TCP/IP for connectivity.
> Bring up the ODBC connection, then on the second page you should see a
> button "Client connectivity". Go into this and make the bullet TCP/IP.
> Please let me know if this fixed your problem.
> =-Chris
>
> On the clients that cannot connect, click the box "Client configuration"
> "Rick" <ricardo.blanco@.indap.com.mx> wrote in message
> news:ur4WMjStEHA.2688@.TK2MSFTNGP14.phx.gbl...
> xp
> server
>|||Rick, thanks for the tip about the tool.
Because you had to reinstall to fix it, tells me that next time you have
this problem, on the SQL Server itself, bring up the Server Network Utility
(located in Programs->Microsoft SQL Server) and double check that on the
enabled protocols list that TCPIP and Named Pipes is listed. I am betting
that they were not previously. If you want to stop clients from connecting,
this is the place to do so as you would just remove the protocols.
Cheers!
=-Chris
"Rick" <ricardo.blanco@.indap.com.mx> wrote in message
news:eN8LyBWtEHA.2788@.TK2MSFTNGP09.phx.gbl...
> Chris thanks a lot, but ive found other way to solve it, i know maybe its
> not the more correct but i reinstalled the Sql server because i found at
> microsofts web a tool to check the ports, its name is PortQuery, with it
> you can check for Sql server if it is running on a server, or network
> connectivity and other things, i saw that the server doesnt aswer to the
> tool, the ports 1433 and 1434 not respond, so i decided to reinstal Sql
> server, and it solved the problem =)
> the tools url is http://support.microsoft.com/kb/310513/EN-US/
> Rick =)
> "Christopher Conner" <someone@.someplace.com> wrote in message
> news:%23u554sTtEHA.3872@.TK2MSFTNGP15.phx.gbl...
win[vbcol=seagreen]
can[vbcol=seagreen]
pro[vbcol=seagreen]
have[vbcol=seagreen]
between[vbcol=seagreen]
>

Can't connect remotely to SQL Express as 'sa'

I have a remote server with SQL Server 2005 Express installed on it. I
try to connect to it from my local machine using TCP/IP. I have the
remote SQL Express set to mixed mode and enabled TCP/IP.
If I connect using a self-created login it works fine and I can run
queries against the database. However whenever I try to login with the
'sa' account, it doesn't work (Login failed). I'm 100% sure the 'sa'
account is enabled and the password is correct. When I connect using
MSSMSE from my local machine to the remote SQL Express using the 'sa'
account it works fine and I can do everything.
So, the password I use for the 'sa' account is definitely correct but
somehow it doesn't allow me to connect from anything else than MSSMSE.
E.g. the following VBscript does not work:
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB;Data
Source=10.10.10.94;DATABASE=Test;User Id=sa;Password=testing;Connect
Timeout=3;network library=DBNETLIB;"
Conn.Close
Set Conn = Nothing
It gives:
C:\test.vbs(2, 1) Microsoft OLE DB Provider for SQL Server:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
When I connect with isql, I get "Login failed for 'sa'".
It has kept me busy for almost 4 hours already and I can't figure out
what's going on. It feels like some security settings in SQL Express
prevents me from using the 'sa' account from any application except
MSSMSE.
Any help is appreciated...
Grtz,
Marco>E.g. the following VBscript does not work:
>Set Conn = CreateObject("ADODB.Connection")
>Conn.Open "Provider=SQLOLEDB;Data
>Source=10.10.10.94;DATABASE=Test;User Id=sa;Password=testing;Connect
>Timeout=3;network library=DBNETLIB;"
>Conn.Close
>Set Conn = Nothing
I fixed the problem. For anybody interested, here's how I fixed it...
The problem was most likely not related to the account. I probably
made a mistake while testing it. Or mixed results might have been
caused by the fact that I tested with both dynamic ports and fixed
ports.
The problem was fixed by changing the "Data Source". I changed it to:
Data Source=SERVER\SQLINSTANCE
So, e.g.:
Data Source=10.10.10.94\SQLEXPRESS1
This is with Dynamic ports.
Cheers,
Marco