How a proper physical TCP/IP connection to SQL Server works. Network Monitor
Not about a problem today, today will be about what a successful physical connection from client to SQL Server looks like in Network Monitor.
First download Network Monitor (currently version 3.3):
“Microsoft Network Monitor 3.3”
. Install it on the client machine and on the SQL Server machine.
. Start it.
. Select “New Capture”
. Make sure you select the “Capture Filter” tab and not the “Display Filter” tab. Otherwise you will capture all network traffic, not just the TCP we are interested in.
. Set the “Capture Filter” to:
tcp.Port == xxxxx
Where you replace xxxx with the port number your SQL Server is listening on. If you do not know what port it is listening on, the easiest way is to open the SQL Server log, normally found here:
C:\Program Files\Microsoft SQL Server\<your server instance>\MSSQL\Log
At the start of the log, you should see something like this:
2009-11-04 09:06:40.73 Server Server is listening on [ 'any' <ipv6> 60847].
2009-11-04 09:06:40.73 Server Server is listening on [ 'any' <ipv4> 60847].
So here we would replace the xxxx with 60847.
. Then when the filter is set, do not forget to click Apply! This will change <No Active Filter> to tcp.Port == 60847 using the example above.
. Then Start the capture (F5), connect to your SQL Server, execute your query, Stop (F7) the capture on both machines.
. This should produce an output like this (here I have a display filter set to !SSL):
TimeOfDay SourceIP SourcePort DestinationIP DestinationPort Description
3 14:57:51.505 xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP TCP:Flags=......S., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663164647, Ack=0, Win=8192
4 14:57:51.506 xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP TCP:Flags=...A..S., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468829443, Ack=663164648,
5 14:57:51.509 xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663164648, Ack=3468829444,
15 14:57:51.801 xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP TCP:Flags=...A...., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468830489, Ack=663165781,
19 14:57:52.503 xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663165897, Ack=3468830947,
22 14:57:59.513 xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663165957, Ack=3468831154,
24 14:58:05.287 xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP TCP:Flags=...A...., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468831154, Ack=663166035,
26 14:58:05.523 xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663166035, Ack=3468831465,
27 14:58:12.845 xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP TCP:Flags=...A...F, SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663166035, Ack=3468831465,
28 14:58:12.845 xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP TCP:Flags=...A...., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468831465, Ack=663166036,
29 14:58:12.846 xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP TCP:Flags=...A...F, SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468831465, Ack=663166036,
30 14:58:12.846 xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663166036, Ack=3468831466,
So here we see following chain of events (3, 4 and 5):
.The client (xxx.xx.162.187:51712) sends a SYN (S) to the server (xxx.xx.162.174:60847).
.The server (xxx.xx.162.174:60847) sends an ACK (A) and a SYN (S) to the client (xxx.xx.162.187:51712).
.The client (xxx.xx.162.187:51712) sends an ACK (A) to the server (xxx.xx.162.174:60847).
This is the 3way handshake and once this is complete the physical connection between the client and the server has been setup.
Still, this does not mean that the SQL Server itself has, for example, denied a Login but it proves that the physical connection has been setup.
If there was, for example, a firewall blocking or a router between the client and server that was misrouting, we would not see the S/SA/A sequence.
After this handshake, we will see few packages being sent, this is the data traffic.
Finally we have the following events (27, 28, 29 and 30):
.The client (xxx.xx.162.187:51712) sends an ACK (A) and a FIN (F) to the server (xxx.xx.162.174:60847). FIN tells the server that we will not send more data, ACK to let the server what connection to close.
.The server (xxx.xx.162.174:60847) sends an ACK (A) to the client (xxx.xx.162.187:51712). This acknowledges the FIN received in the previous step. It is not closing yet since the server may have more data to transfer.
.The server (xxx.xx.162.174:60847) sends an ACK (A) and a FIN (F) to the client (xxx.xx.162.187:51712). FIN tells the client that the server will not send more data.
.The client (xxx.xx.162.187:51712) sends an ACK (A) to the server (xxx.xx.162.174:60847). ACK means that the client has acknowledged that the server will send more data.
This is the teardown of the physical connection.
So, if you have a trace then what you should look for is the S/SA/A sequence and the AF/A/AF/A sequence.
If these sequences exist, then you know that the physical network is working. But as mentioned, there could be issues after the physical connection has been setup, for example,
the server is performing slowly but responds, the login fails etc.
As an example, if you run a trace on the client and a trace on the SQL Server and you in the client trace can see the S being in the trace taken on the client, but you do not see
the S in the trace on the server. Then the first step would be to check the firewall so that it is not blocking the port in question. The port in question is clearly seen in the trace.
NOTE; I you are running with connection pooling enabled, which is default from, for example, a .Net client, the S/SA/A will only show on the first connection.
And the AF/A/AF/A sequence will only be shown when the last connection is closed. This is the nature (and purpose) of connection pooling.