Breyta

Deila með

Trace the network authentication process to the Database Engine

  • Grein

This article presents several examples of a network trace that captures various handshakes and authentication sequences during the Transmission Control Protocol (TCP) connection establishment process between a client application and the SQL Server Database Engine (the server).

For information about closing connections, see Trace the network connection close sequence on the Database Engine.

Authentication types

You can connect to the Database Engine with Windows authentication (using Kerberos or NTLM authentication), or SQL authentication.

This article also describes Multiple Active Result Sets (MARS) connections. MARS is a feature of SQL Server, introduced with SQL Server 2005 (9.x), that allows multiple commands to be executed on a connection without having to clean up the results from the first command, before running the second command. MARS is achieved through session multiplexing (SMUX).

This process describes a normal login process using SQL authentication, showing each step of the conversation between the client and server through a detailed network trace analysis. The example network trace delineates the following steps:

  1. TCP three-way handshake
  2. Driver handshake
  3. SSL/TLS handshake
  4. Login packet exchange
  5. Login confirmation
  6. Execute a command and read the response
  7. TCP four-way closing handshake

Example network trace

This exchange is allocated 1 second regardless of the Login Timeout setting in the connection string.

  • The client IP address is 10.10.10.10
  • The server IP address is 10.10.10.120

Step 1. TCP three-way handshake

All TCP conversations start with a SYN packet (S flag set) sent from the client to the server. In Frame 6127, the client uses an ephemeral port (dynamically assigned by the operating system) and connects to the server port, in this case port 1433. The server replies with its 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 step establishes a basic TCP connection, the same way a telnet command would. The operating system mediates this part of the conversation. At this point, the client and server know nothing about each other.

Diagram of three-way handshake. 

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=

In this step, the [Bad CheckSum] warnings are benign and are an indicator that checksum offload is enabled. That is, they're added at a lower level in the network stack than the trace is taken. In the absence of other information, this warning indicates whether the network trace was taken on the client or the server. In this case, it appears on the initial SYN packet, so the trace was taken on the client.

Step 2. Driver handshake

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. This information includes whether to encrypt data packets, whether to use Multiple Active Result Sets (MARS), its version number, whether to use federated authentication, the connection GUID, and so on.

The server responds with its information, such as whether it requires authentication. This sequence happens before any sort of security negotiation is performed.

Diagram of driver handshake. 

Frame Time Offset Source IP    Dest IP      Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
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

Step 3. SSL/TLS handshake

The SSL/TLS handshake begins with the Client Hello packet and then the Server Hello packet, plus some extra packets related to Secure Channel. This step 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 the version of TLS happens at this stage of the login. The client or server can close the connection at this stage if the TLS versions don't line up, or they don't have any cipher suites in common.

Diagram of SSL/TLS handshake. 

Frame Time Offset Source IP    Dest IP      Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
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

Step 4. Login packet

This packet is encrypted and might show as SSL Application Data or TDS:Data, depending on your network parser. If all the packets after this step also show as SSL Application Data, the connection is encrypted.

Diagram of SQL login. 

Frame Time Offset Source IP    Dest IP      Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
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

Step 5. Login confirmation

Otherwise, you see a response packet, which either confirms the login (has the login ACK token), or returns a Login Failed error message to the client.

Here's an example of what you might see in the packet hexadecimal data for a successful 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.d.v.e.n.t.u.r.e.W.o.r.ks'

Diagram of SQL login confirmation. 

Frame Time Offset Source IP    Dest IP      Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
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

Step 6. Execute a command and read the response

Commands are sent as either a TDS:SQLBatch or a TDS:RPCRequest packet. The former executes plain Transact-SQL statements, and the latter executes stored procedures. You might see TCP continuation packets if the command is lengthy, or in the Response packet if more than a few rows are returned.

Frame Time Offset Source IP    Dest IP      Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
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=

Step 7. TCP four-way closing handshake

Microsoft drivers use the four-way handshake to close connections. Many third-party drivers just reset the connection to close it, making it more difficult to distinguish between a normal and abnormal close.

The four-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 acknowledges (ACK).

If the server sends a FIN packet first, it's an abnormal closing, most commonly seen in the SSL/TLS handshake if the client and server can't negotiate the secure channel.

Diagram of four-way closing handshake. 

Frame Time Offset Source IP    Dest IP      Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
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=

Related content