April 4, 2002 at 12:41 am
I have a strange problem. My application binds to port 5000. sometimes the application fails to start detecting port bound by someother application. Netstat output shows the port bound by MSSQL. Possibly a remote connection to the SQL server, the SQL server would have alloter this port for the client...is there a possible entry where I can mention MSSQL not to use port 5000 or use only ports from this to that...
Jesus My Saviour
April 4, 2002 at 1:06 am
levi,
use server network utility that comes with sqlserver to set the listening port.
--
-oj
April 4, 2002 at 5:38 am
MS uses Overlapped IO Ports to handle connections in SQL as it does with IIS. There to my knwoledge is no way to specify a range and it is actually the OS that controls this allocation.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 9:53 am
Is the problem that SQL Server sometimes runs on 5000? Is this an instance besides the default? The server network utility mentioned above allows you to limit SQL Server to a specific port.
Can you limit your application?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2002 at 8:01 pm
Steve that setting is only the listening port not the actual port that communicatrion with connected clients takes place on.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 8:33 pm
Is that true? I thought all comms were across that port. Need to check this out.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2002 at 10:15 pm
This is what I got from the Microsoft site.
When the client establishes a TCP/IP connection, a three-way handshake is done. The client opens a source port and sends traffic to a destination port, which by default is 1433. The client source port in use is random, but is greater than 1024. By default, when an application requests a socket from the system for an outbound call, a port between the values of 1024 and 5000 is supplied
I have also seen a document which gives few registry entries where in you can specify the range of ports to be used. I missed that, can anybody help me .
Jesus My Saviour
April 5, 2002 at 5:05 am
Well, I guess we still learn something everyday. After what levi said about the registry entry I got to looking and the best match I found was MaxUserPort (sorry I had never had issues with the regular settings when I am building server apps, so never looked). Anyway some info on it can be found at http://www.siesystems.com/w2k_registry_tcp.htm for Win2k. And here is the Microsoft KB Article on it http://support.microsoft.com/default.aspx?scid=kb;EN-US;q196271. Hope this points you where you want to go.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 6, 2002 at 2:58 am
Hi Steve,
Looked over the netstat thread you sent me. Looks like you guys have some major confusion over the different SQL server network APIs (there is a lot of bad/wrong information in the thread). Maybe it is a topic we can coauthor in some future article. Anyway to make a long story short.......
-Answer:
He is using both "Multiprotocol" and "Sockets" over IP. Specifically, his client connection is utilizing "Multiprotocol" not sockets . This is why he is seeing the weird random port selection.
-Explanation
Multiprotocol:
Multiprotocol uses RPC network library. The connection is a two stage process. First a socket is established to TCP 135 to negotiate a TCP port for the actual session (1024-5000). The second process actually creates the connection to the mutually agreed upon port. This is why he never sees the connection binding to port 1433 (which is only used by socket library connections).
TCP/IP Sockets
Sockets uses standard BSD sockets mechanisms. Client initiates connection form and available port > 1024 to the server port at 1433. There is no negotiation PERIOD. The connection will ALWAYS be to the SQLServer port at 1433.
Named Pipes (just in case you were curious)
Name resolution / service availability is handled through traditional Microsoft (netbios) mechanisms such as WINS(udp 137), broadcast(also UDP 137), or static file(lmhosts). The session is handled in the same manner as sockets except the server listens on port TCP 139.
Note: All three APIs can run over IP (that does not make them the same), but they each behave differently because of their underlining designs. They are completly different APIs running over the same layer three protocol (IP).
-Comment
Both guys are mixing and matching both tcp/ip multiprotocol and sockets configurations (This is why making MS apps so easy to configure is dangerous. People are not aware of the implications of selecting one Network API over another). I would settle on one (explicitly define it on your client and server config) or the other unless there is a specific requirement to use both. I prefer sockets because it is more predictable and more easily supported by existing IP network infrastructures (firewalls, name resolution, etc.). If encryption is a requirement they can use ssh or ssl to tunnel the 1433 connection (like I set up for you at sqlservercentral).
April 8, 2002 at 7:56 pm
Ok first I am not using multiprotocol and have TCP/IP defined at my clients.
Any connections made to any server will always interogate port 135, the OS is doing a check on the available servers an shares on the remote machine (happens no matter what you do, even IE does it).This is actually how when you have dynamically determine SQL port finds it (You didn't actually think it scanned every port did you? That would take the better part of several minutes every time a connection occurrs.)
Now from the begining,
When SQL server starts it creates a socket address and binds to port 1433 (default but can be changed). Then it places 1433 in a state of LISTEN with a maximum number of backlog spaces available and waits for connections.
Now when a client comes a three way hand shake occurrs and the following occurr.
SQL 1433 - REC (ASSIGNPORT SEND) ACK
CLIENT - REC (CONNECT AND SENDTONEWPORT) ACK
SQL NEWPORT - REC ACK
1433 answers and establishes a connection accepting any incoming data that is sent by the client during the connect (usually login type stuff or negotiation data)
An empty socket is open and the client is transfered to it (new port these are the ones you see across from 1433 in netstat -an.)
Then 1433 goes back to LISTENing to handle the next connection which causes all the last few things to occurr again and again until the server runs out of available ports.
Now that the client has a connection been made, data is sent to and received from the client to the server on the port that was assigned during the handshake. As new clients join they go thru the same process.
Now when a client disconnects or the connections times out the port changes to a TIMEWAIT to keep the port from being assigned again. After the TIMEWAIT expires (usually 30 to 120 seconds later) the port is available for use again.
Also a PORT can only exist in 3 states, CLOSED, ESTABLISHED/TIMEWAIT (Open in other words), LISTENING. It cannot listen and process at the same time this is why it creates new ports to talk to the clients on so it can continue to accept new connections.
I will post the raw details of socket programming under Windows using winsock when I get thru working on it, along with serveral links. But contrary to what jgottlieb states this is how SQL Server connections work and it does use multiple ports otherwise it could only handle 1 connection at a time.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 9, 2002 at 1:12 am
Ok Antares686,
First, my intention is not to start a Networking Jihad here, just to correct your understanding of TCP/IP (which is flawed). Let me address a couple of your points(please read the whole thread before responding):
#1 The multiprotocol configuration I was talking about was for the netstat output I received from user Levi directed at user Levi. Given the description of the problem and netstat output I was provided with, it is clear that he is using multiprotocol. I am making this assertion because when using the socket library the SQL Server side of the socket pair will always be 1433 (unless manually configured to be something else). If the server is manually configured to bind to a different address (let say address $A), than $A will always be the port used by the server in the socket pair. Levi stated (backed by his netstat output) that the connection to the SQL server was to random ports (sometimes to tcp 5000). This behavior is exhibited only in multiprotocol.
The output I received for your configuration is clearly sockets. Since all your socket pairs are clearly connecting to port 1433 on your server (notice how often I use the term socket pair, but I'll get to this later). My mistake was to use "Both Guys" in my last writing (and this is the only mistake I made). I was assuming you might be using multiprotocol (though your netstat output did not show it) because you kept insisting that your SQL server port was being assigned dynamically (which is never true for the Socket library only multiprotocol). So I assumed you did not have an active multiprotocol connection when you ran netstat.
#2 YOU SAY >>> "Any connections made to any server will always interrogate port 135, the OS is doing a check on the available servers an shares on the remote machine (happens no matter what you do, even IE does it).This is actually how when you have dynamically determine SQL port finds it (You didn't actually think it scanned every port did you? That would take the better part of several minutes every time a connection occurrs.)"
NOT Correct: Try to connect (by using telnet 127.0.0.1 [port]) to port 135 on your local machine (or your sqlserver). Successful right(blank telnet session). Now try to do it to http://www.microsoft.com, http://www.yahoo.com, http://www.dell.com, http://www.compaq.com, qa.sqlservercentral.com, etc (timed out right...) Any sane network engineer will block this port to the outside world (this is why you can not connect to it). How do you propose your browser or any other application will interrogate port 135? Additionally, the many many many Unix boxes I have configured do not have any processes bound to this port and all socket based servers work just fine. This is because most IP clients are configured with the "Well Known Port" for the destination service (by default your browser goes to tcp 80 for http and tcp 443 for https). There is no scanning required.
So what does TCP port 135 do? TCP Port 135 (DCE endpoint resolution) is used as a port-mapper port for RPC (remote procedure calls) based applications (like multiprotocol client, exchange client/admin tools, NT 4 Admin tools, and more). It is not used by pure Socket API based apps. In the Unix world we have something very similar running at UDP/TCP 111 (sunrpc or portmap) to support services such as NFS (but it has nothing to do with non RPC apps).
Continued below in next thread.....
April 9, 2002 at 1:14 am
#3 Again not to start a flame war here, but your understanding of how sockets work is not quite correct (I commend you for your effort<honestly>):
YOU SAY >>> "1433 answers and establishes a connection accepting any incoming data that is sent by the client during the connect (usually login type stuff or negotiation data)
An empty socket is open and the client is transferred to it (new port these are the ones you see across from 1433 in netstat -an.)
Then 1433 goes back to LISTENing to handle the next connection which causes all the last few things to occurr again and again until the server runs out of available ports."
NOT Correct: The first thing that happens when a server receives a connection attempt is the invoking of a new process to handle to connection request (not a new port). This can be handled by either forking a process (old style UNIX deamons) or by creating a thread (most new implementations including MS). The socket pair handled by the new process will be the original client IP Address:Source port and server IP Address:Destination Port (this remains constant from the begining of the connection to the end. No new ports). The client is NEVER assigned a new port. The client choses its outgoing port (generally random available > 1024) when it initiates a connection to the server.
I'm not sure what you mean by "login stuff" or "negotiation data". Login stuff is strictly a function of the Application Layer of the OSI or DARPA network models and has nothing to do with sockets. You'll have to explain what you mean by "negotiation data" because it is not standard network nomenclature.
What happens next is the three way handshake (SYN,SYN-ACK,ACK) after which data may be transferred over the connection (only at this point can authentication happen as part of the data passed between the two network nodes). No application data is passed during the three way handshake. I can go on and on and on about TCP operation so I'll direct you to some excellent references instead (TCP/IP Illustrated Volume 1 by Stevens and Internetworking with TCP/IP Volume 1 by Comer. These are considered the authoritative references on TCP/IP by every network engineer I know).
The server will not run out of ports since it only uses one port (when using the socket library). The server is always using either 1433 or a manually configured port (again within the context of the socket library). The server CAN run out of available threads,processes,memory,etc. to handle a new connection, but it would never get to the point where it ran out of ports (more specifically unique socket pairs). There are about 2^32 * 2^16 (or 281,474,976,710,656) possible (give or take a couple of billion<grin>) unique socket pairs for a single listening (server) port. What makes a connection unique is not a single port but the whole socket pair(src-ip:src-port dst-ip:dst-port). This is the piece I think you are missing.
It is conceivable that a single client can run out of outgoing ports. But the client would have to have more than 2^16 (It is highly likely that it would exhaust some other resource like memory, processes, threads,file handles). The only place I have ever seen this happen is in Firewalls/Routers/Nat Devices that use PAT (or port address translation) in order to enable multiple machines to use the internet behind one IP address.
Continued Below.....
April 9, 2002 at 1:17 am
#4 YOU SAY>>>"Also a PORT can only exist in 3 states, CLOSED, ESTABLISHED/TIMEWAIT (Open in other words), LISTENING. It cannot listen and process at the same time this is why it creates new ports to talk to the clients on so it can continue to accept new connections"
NOT Correct: Actually a client port can also be in SYN_SENT, FIN_WAIT_1, and FIN_WAIT_2 (I can only demonstrate the first. The latter is difficult to catch). Look what happens when I try to telnet into http://www.yahoo.com (a service that is not available to me). Noticed that a source port has been selected without making actual contact with the destination.
TCP 192.168.1.7:3532 66.218.71.87:23 SYN_SENT
You also missed some server states. A Server may be in SYN_RCVD, CLOSE_WAIT, and LAST_ACK (difficult to capture using netstat but I can capture some packets to demonstrate).
#5 YOU SAY>>> "I will post the raw details of socket programming under Windows using winsock when I get thru working on it, along with serveral links. But contrary to what jgottlieb states this is how SQL Server connections work and it does use multiple ports otherwise it could only handle 1 connection at a time."
I hope you read the books I suggested before you post socket programming information. I also recommend that you look at Volume II and III of either series for more detailed implementation information.
You should also check out:
-http://support.microsoft.com/default.aspx?scid=kb;EN-US;q287932 (read this one carefully it is easy to misinterpret the client port selection section).
-http://www.microsoft.com/windows2000/docs/tcpip2000.doc
-http://www.microsoft.com/siteserver/ssrk/docs/rk_fltrset.doc (good info regarding port utilization for specific applications)
-http://www.ladysharrow.ndirect.co.uk/NT/windows_nt_blackpaper_by_neon_su.htm (some info on port 135)
Did I mention that I am not a DBA, but a senior network engineer with a specific expertise in TCP/IP. I spend the better part of the day "sniffing" packets (not glue) in order to better accommodate application requirements within secure network infrastructures. It would behoove you to listen to me (the same way I listen to my DBAs when it comes to database operation since I don’t spend my day playing with Databases).
Regards,
Jordan (drop me a line if you have any questions).
April 9, 2002 at 10:33 am
Aint every day we get network jihad and glue sniffing in the same topic! I would like to see or more articles about this stuff. I work mostly on the code end of things so this pretty interesting, something new!
Andy
April 9, 2002 at 10:49 am
Well I will conceed that this information looks more correct than what I have learned. I am coming from a VC background not worried about the DBA aspect and have developed serveral servers, at the code level I talk to the port that is assigned not (for SQL sake 1433) except on initial connection from the standpoint of the packets so I don't see it working exactly as you have here and maybe there is something else in the API set I am not seeing (I will dig deeper). I deal directly with the packet itself not like the prehandled stuff VBers deal with. Anyway thanks for the info and I will research more as it seems I have been steered incorrectly or we are both missing something key.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply