Normal Login Using SQL Authentication
Frame Time Offset Source IP Dest IP Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
6127 116.5776698 10.10.10.10 10.10.10.120 TCP:Flags=......S., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702293, Ack=0, Win=8192 ( Ne
6128 116.5776698 10.10.10.120 10.10.10.10 TCP:Flags=...A..S., SrcPort=1433, DstPort=60123, PayloadLen=0, Seq=4095166896, Ack=4050702294, Win=
6129 116.5786458 10.10.10.10 10.10.10.120 TCP:Flags=...A...., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702294, Ack=4095166897, Win=
6130 116.5786458 10.10.10.10 10.10.10.120 TDS:Prelogin, Version = 7.1 (0x71000001), SPID = 0, PacketID = 0, Flags=...AP..., SrcPort=60123, Ds
6131 116.5805998 10.10.10.120 10.10.10.10 TDS:Response, Version = 7.1 (0x71000001), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=1433, Dst
6132 116.5835288 10.10.10.10 10.10.10.120 TLS:TLS Rec Layer-1 HandShake: Client Hello. {TLS:328, SSLVersionSelector:327, TDS:326, TCP:325, IP
6133 116.5845058 10.10.10.120 10.10.10.10 TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate. Server Hello Done. {TLS:328, SSLVersionSe
6134 116.5864588 10.10.10.10 10.10.10.120 TLS:TLS Rec Layer-1 HandShake: Client Key Exchange.; TLS Rec Layer-2 Cipher Change Spec; TLS Rec La
6135 116.5923178 10.10.10.120 10.10.10.10 TLS:TLS Rec Layer-1 Cipher Change Spec; TLS Rec Layer-2 HandShake: Encrypted Handshake Message. {TL
6136 116.5932948 10.10.10.10 10.10.10.120 TLS:TLS Rec Layer-1 SSL Application Data {TLS:328, SSLVersionSelector:327, TDS:326, TCP:325, IPv4:3
6137 116.5962248 10.10.10.120 10.10.10.10 TDS:Response, Version = 7.1 (0x71000001), SPID = 96, PacketID = 1, Flags=...AP..., SrcPort=1433, Ds
6138 116.5991538 10.10.10.10 10.10.10.120 TDS:SQLBatch, Version = 7.1 (0x71000001), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=60123, Ds
6139 116.5991538 10.10.10.120 10.10.10.10 TDS:Response, Version = 7.1 (0x71000001), SPID = 96, PacketID = 1, Flags=...AP..., SrcPort=1433, Ds
6266 116.8032558 10.10.10.10 10.10.10.120 TCP:Flags=...A...., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702956, Ack=4095168204, Win=
6362 116.9097008 10.10.10.10 10.10.10.120 TCP:Flags=...A...F, SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702956, Ack=4095168204, Win=
6363 116.9097008 10.10.10.120 10.10.10.10 TCP:Flags=...A...., SrcPort=1433, DstPort=60123, PayloadLen=0, Seq=4095168204, Ack=4050702957, Win=
6364 116.9097008 10.10.10.120 10.10.10.10 TCP:Flags=...A...F, SrcPort=1433, DstPort=60123, PayloadLen=0, Seq=4095168204, Ack=4050702957, Win=
6366 116.9106778 10.10.10.10 10.10.10.120 TCP:Flags=...A...., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702957, Ack=4095168205, Win=
Frame Time Offset Source IP Dest IP Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
--- TCP 3-Way Handshake
6127 116.5776698 10.10.10.10 10.10.10.120 TCP:Flags=......S., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702293, Ack=0, Win=8192 ( Ne
6128 116.5776698 10.10.10.120 10.10.10.10 TCP:Flags=...A..S., SrcPort=1433, DstPort=60123, PayloadLen=0, Seq=4095166896, Ack=4050702294, Win=
6129 116.5786458 10.10.10.10 10.10.10.120 TCP:Flags=...A...., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702294, Ack=4095166897, Win=
--- Driver Handshake
6130 116.5786458 10.10.10.10 10.10.10.120 TDS:Prelogin, Version = 7.1 (0x71000001), SPID = 0, PacketID = 0, Flags=...AP..., SrcPort=60123, Ds
6131 116.5805998 10.10.10.120 10.10.10.10 TDS:Response, Version = 7.1 (0x71000001), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=1433, Dst
--- SSL/TLS Handshake
6132 116.5835288 10.10.10.10 10.10.10.120 TLS:TLS Rec Layer-1 HandShake: Client Hello. {TLS:328, SSLVersionSelector:327, TDS:326, TCP:325, IP
6133 116.5845058 10.10.10.120 10.10.10.10 TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate. Server Hello Done. {TLS:328, SSLVersionSe
6134 116.5864588 10.10.10.10 10.10.10.120 TLS:TLS Rec Layer-1 HandShake: Client Key Exchange.; TLS Rec Layer-2 Cipher Change Spec; TLS Rec La
6135 116.5923178 10.10.10.120 10.10.10.10 TLS:TLS Rec Layer-1 Cipher Change Spec; TLS Rec Layer-2 HandShake: Encrypted Handshake Message. {TL
--- Login Packet
6136 116.5932948 10.10.10.10 10.10.10.120 TLS:TLS Rec Layer-1 SSL Application Data {TLS:328, SSLVersionSelector:327, TDS:326, TCP:325, IPv4:3
--- Login Confirmation
6137 116.5962248 10.10.10.120 10.10.10.10 TDS:Response, Version = 7.1 (0x71000001), SPID = 96, PacketID = 1, Flags=...AP..., SrcPort=1433, Ds
--- Execute a Command and Read the Response
6138 116.5991538 10.10.10.10 10.10.10.120 TDS:SQLBatch, Version = 7.1 (0x71000001), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=60123, Ds
6139 116.5991538 10.10.10.120 10.10.10.10 TDS:Response, Version = 7.1 (0x71000001), SPID = 96, PacketID = 1, Flags=...AP..., SrcPort=1433, Ds
6266 116.8032558 10.10.10.10 10.10.10.120 TCP:Flags=...A...., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702956, Ack=4095168204, Win=
--- TCP 4-Way Closing Handshake
6362 116.9097008 10.10.10.10 10.10.10.120 TCP:Flags=...A...F, SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702956, Ack=4095168204, Win=
6363 116.9097008 10.10.10.120 10.10.10.10 TCP:Flags=...A...., SrcPort=1433, DstPort=60123, PayloadLen=0, Seq=4095168204, Ack=4050702957, Win=
6364 116.9097008 10.10.10.120 10.10.10.10 TCP:Flags=...A...F, SrcPort=1433, DstPort=60123, PayloadLen=0, Seq=4095168204, Ack=4050702957, Win=
6366 116.9106778 10.10.10.10 10.10.10.120 TCP:Flags=...A...., SrcPort=60123, DstPort=1433, PayloadLen=0, Seq=4050702957, Ack=4095168205, Win=
All TCP conversations start with a SYN packet (S flag set) sent from the client to the server. In Frame 6127, the client's IP address is 10.10.10.10 and the server IP address is 10.10.10.120. The client uses an ephemeral port (dynamically assigned by the OS) and connects to the server port, in this case port 1433. The server replies with it's own SYN packet with the ACK flag also set. finally, the client responds with an ACK packet to let the server know it received its SYN packet.
This establishes a basic TCP connection, the same way a TELNET command would. This part of the conversation is mediated by the operating system. At this point, the client and server know nothing about each other.
Both the client driver and SQL Server need to know a bit about each other. In this handshake, the driver sends some information and requirements to the server, such as whether to encrypt data packets, whether to use Multiple Active Resultsets (MARS), it's version number, whether to use Federated Authentication, the connection GUID, etc.
The server responds with it's information, such as whether it requires authentication, etc. All this is before we perform any sort of security negotiation.
The SSL Handshake begins with the Client Hello packet and then the Server Hello packet, plus some additional packets related to Secure Channel. This is where the security key is negotiated for encrypting packets. Normally, just the login packet is encrypted, but the client or the server could require data packets to be encrypted as well. Choosing TLS 1.0 or 1.2 happens at this stage of the login. The client or server may close the connection at this stage if the TLS versions don't line up or they do not have any cipher suites in common.
This packet is encrypted and may show as "SSL Application Data" or "TDS:Data" depending on your network parser. If all the packets after this also show as "SSL Application Data", then this means the connection is encrypted.
Otherwise, you will see a Response packet, which will either confirm the login (has the Login ACK token) or return a "Login Failed" error message to the client.
Example of what you might see in the packet HEX data for a good login:
.C.h.a.n.g.e.d. .d.a.t.a.b.a.s.e. .c.o.n.t.e.x.t. .t.o. .'.A.C.C.O.U.N.T.I.N.G'
Commands are sent as either a TDS:SQLBatch or a TDS:RPCRequest packet. The former executes plain SQL Statements and the latter executes stored procedures. You may see TCP Continuation packets if the command is lengthy or in the Response packet if more than a few rows are returned.
Microsoft drivers use the 4-way handshake to close connections.
Many 3rd-party drivers, just reset the connection to close it, making it more difficult to distinguish between a normal and abnormal close.
The 4-way handshake consists of the client sending a FIN packet to the server, which the server responds to with an ACK. The server then sends its own FIN packet, which the client ACKs.
If the server sends a FIN packet first, it is an abnormal closing, most commonly seen in the SSL Handshake if the client and server cannot negotiate the secure channel.