(working) TCP network monitor trace explained.
Today I’ll do something different, I will describe something that works. In this case, how to understand a network trace for an TCP/IP connection
Basically this is a short description on how a TCP/IP connection works when connecting to SQL Server. Note that I am in no way an expert, this is the basics only.
To do this, you need download Network Monitor and run it when connecting to SQL Server that is on remote machine.
"Microsoft Network Monitor"
Then create simple application that connects to a SQL Server via TCP, for example:
static void Main(string[] args)
{
string cs = @"Data Source=tcp:<your server>;Initial Catalog=Northwind;Integrated Security=True";
string sql = "SELECT ShipperID, CompanyName, Phone FROM Shippers";
try
{
using (SqlConnection connection = new SqlConnection(cs))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("{0} {1} {2}", rdr[0], rdr[1], rdr[2]);
}
}
connection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
Start a new trace in Network Monitor and run your code. This should generate something like below, this is truncated from traffic on other protocols etc.
Also, I usually reorder the columns so that they show Source IP:port, Destination IP:port, protocol and Description. But this is just how I like to read it.
I’ve color coded it like this:
xxx.xxx.xxx.121 – Client IP
xxx.xxx.xxx.187 – Server IP
SourceIP SrcPort DestinatioIP DstPort Protcol Description
.1 xxx.xxx.xxx.121 50397, 50397 xxx.xxx.xxx.187 1434, 1434 SQLBrowser SQLBrowser
.2 xxx.xxx.xxx.187 1434, 1434 xxx.xxx.xxx.121 50397, 50397 SQLBrowser SQLBrowser
.3 xxx.xxx.xxx.121 57682 xxx.xxx.xxx.187 61393 TCP TCP: Flags=.S......, SrcPort=57682, DstPort=61393, Len=0, Seq=4117361997, Ack=0
.4 xxx.xxx.xxx.187 61393 xxx.xxx.xxx.121 57682 TCP TCP: Flags=.S..A..., SrcPort=61393, DstPort=57682, Len=0, Seq=457964143, Ack=4117361998
.5 xxx.xxx.xxx.121 57682 xxx.xxx.xxx.187 61393 TCP TCP: Flags=....A..., SrcPort=57682, DstPort=61393, Len=0, Seq=4117361998, Ack=457964144
.6 xxx.xxx.xxx.121 57682 xxx.xxx.xxx.187 61393 TCP TCP: Flags=...PA..., SrcPort=57682, DstPort=61393, Len=56, Seq=4117361998 - 4117362054, Ack=457964144
xxx.xxx.xxx.187 61393 xxx.xxx.xxx.121 57682 TCP TCP: Flags=...PA..., SrcPort=61393, DstPort=57682, Len=43, Seq=457964144 - 457964187, Ack=4117362054
...
.7 xxx.xxx.xxx.121 57682 xxx.xxx.xxx.187 61393 TCP TCP: Flags=F...A..., SrcPort=57682, DstPort=61393, Len=0, Seq=4117363403, Ack=457965946
.8 xxx.xxx.xxx.187 61393 xxx.xxx.xxx.121 57682 TCP TCP: Flags=....A..., SrcPort=61393, DstPort=57682, Len=0, Seq=457965946, Ack=4117363404
.9 xxx.xxx.xxx.187 61393 xxx.xxx.xxx.121 57682 TCP TCP: Flags=F...A..., SrcPort=61393, DstPort=57682, Len=0, Seq=457965946, Ack=4117363404
.10 xxx.xxx.xxx.121 57682 xxx.xxx.xxx.187 61393 TCP TCP: Flags=....A..., SrcPort=57682, DstPort=61393, Len=0, Seq=4117363404, Ack=457965947
So what is going on here? Let’s check step by step.
.1 When running SQL Server, it uses what is called dynamic ports, this means that SQL Server claims a free port on startup, this may not be the same that it used when it was last running.
For example, when the server was shut down another application took that port. So how does the client know what port to connect on? The answer is the SQL Browser service.
This service is responsible for informing the clients on what port to connect on. And it listens to port 1434 on UDP. So, the first thing that happens is that the client connects to the SQLBrowser service
and provides the server name, if checking the payload (in the HEX details view) you will see that your server name is sent as an argument.
.2 The SQLBrowser service now returns the information that the client should use, in this case if we check the HEX details again, we can see that for this particular server the port to connect
to is: tcp;61393. So now the initial information is gathered. So now what is known as the 3 way handshake starts.
.3 The client now knows that the connection should happen on TCP port 61393, as we can see in the trace, the client now sends SYN, the S flag, to that port on the server.
Basically this is a request to the server to synchronize the sequence number (SYN: 4117361997 in this case) and it also sends Acknowledgment as 0 (ACK: 0 in this case) since this is the
first part of the 3 way handshake.
4. The server now responds with the S and A flags. The A flag is set to let the client know that the server is acknowledging the synchronization, and the ACK is set to what was passed to it as the
sequence number + 1, in other words the next number in the sequence, in this case 4117361997 + 1 = 4117361998. The server also sends its own sequence number to the client for synchronization.
In this case the server sends SEQ = 457964143.
.5 Now the client in short does the same thing. It acknowledges the request coming from the server by passing back the incoming SEQ + 1, 457964143 + 1 = 457964144 as the ACK
and it has the SEQ to the same that was agreed on earlier, 4117361998.
Step 3 to 5 is what is known as the 3way handshake. When this is done, the client and server have both acknowledged the connection.
.6 This is the data flowing back and forth between the client and the server. In short, we can first see that client sends a sequence that is 56 long, so it the SEQ range is 4117361998 – 4117362054.
Which is 56 (4117362054 – 4117361998 = 56) it also sends its SEQ number (457964144) as ACK to the server.
Next the server sends back a package that is 43 long starting at was sent from the server in the ACK argument 457964144, you can see the SEQ no increased by 43 to 457964187.
It also sends back its SEQ number (4117362054) as ACK to the server. Which is was where the last SEQ ended. And so it continues until all the data has been transferred.
.7 Now it is time to tear down the connection. First the client sends a FIN and an ACK, the F and A flags. The FIN flag tells the server that the client has no more data to send.
The ACK flag is needed so that the server knows what particular connection we are closing. After all, there could be many connections from the client to the server, for example a web application.
.8 The server now acknowledges the FIN in step 7. It could potentially have a lot more data to send to the client, even if the client will not send any.
At this stage the port on the client will be in CLOSE_WAIT and the port on the server will be in FIN_WAIT_2.
.9 Now the server has sent all its data to the client, so now it sends its own FIN and an ACK to the client. Again, FIN means that the server has no more data to send, ACK is to let the client
know what particular connection to close.
10. The client acknowledges the FIN sent from the server, and the connection is gracefully closed.
As mentioned, this is just a brief explanation on how to works, and there shouldn’t be an need to trace network traffic from a troubleshooting point if all works well.
But if this makes anyone a little bit more understanding on what is going on, then this short text has at least done some good.
"SQL Server Browser Service"
https://msdn.microsoft.com/en-us/library/ms181087(SQL.90).aspx
"Transmission Control Protocol"