Läs på engelska Redigera

Dela via


Trace the network authentication process to the Database Engine

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 section describes how you can determine a MARS connection from the following packets:

  • SMP:SYN starts a new session
  • SMP:ACK acknowledges data packets
  • SMP:FIN terminates a session

The following trace examples show the various packets.

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

Open a new MARS connection

The following example output assumes that the following steps are already complete, using NTLM authentication. MARS can also be used with Kerberos and SQL authentication.

  1. TCP three-way handshake
  2. Driver handshake
  3. SSL/TLS handshake
  4. Login packet exchange
  5. NTLM challenge and response packets
  6. Login confirmation
Frame Time Offset Source IP   Dest IP     Description
----- ----------- ----------- ----------- ---------------------------------------------------------------------------
 6704 568.0608108 10.10.10.10 10.10.10.22 TCP:Flags=CE....S., SrcPort=52965, DstPort=1433, PayloadLen=0, Seq=66183290
 6713 568.0608483 10.10.10.22 10.10.10.10 TCP: [Bad CheckSum]Flags=.E.A..S., SrcPort=1433, DstPort=52965, PayloadLen=
 6754 568.0613015 10.10.10.10 10.10.10.22 TCP:Flags=...A...., SrcPort=52965, DstPort=1433, PayloadLen=0, Seq=66183290
 6777 568.0615479 10.10.10.10 10.10.10.22 TDS:Prelogin, Version = 7.4 (0x74000004), SPID = 0, PacketID = 1, Flags=...
 6786 568.0616817 10.10.10.22 10.10.10.10 TDS:Response, Version = 7.4 (0x74000004), SPID = 0, PacketID = 1, Flags=...
 6833 568.0622426 10.10.10.10 10.10.10.22 TLS:TLS Rec Layer-1 HandShake: Client Hello. {TLS:165, SSLVersionSelector:1
 6873 568.0627953 10.10.10.22 10.10.10.10 TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate. Server Key Exchan
 6900 568.0632639 10.10.10.10 10.10.10.22 TCP:Flags=...A...., SrcPort=52965, DstPort=1433, PayloadLen=0, Seq=66183319
 6977 568.0643795 10.10.10.10 10.10.10.22 TLS:TLS Rec Layer-1 HandShake: Client Key Exchange.; TLS Rec Layer-2 Cipher
 7045 568.0655160 10.10.10.22 10.10.10.10 TLS:TLS Rec Layer-1 Cipher Change Spec; TLS Rec Layer-2 HandShake: Encrypte
 7233 568.0679639 10.10.10.10 10.10.10.22 TDS:Data, Version = 7.4 (0x74000004), Reassembled Packet {TDS:162, TCP:160,
 7275 568.0684467 10.10.10.22 10.10.10.10 NLMP:NTLM CHALLENGE MESSAGE {TDS:162, TCP:160, IPv4:1}
 7331 568.0692389 10.10.10.10 10.10.10.22 NLMP:NTLM AUTHENTICATE MESSAGE Version:NTLM v2, Domain: CONTOSO, User: joe1
11791 568.1295675 10.10.10.22 10.10.10.10 TCP: [Bad CheckSum]Flags=...A...., SrcPort=1433, DstPort=52965, PayloadLen=
17978 568.2162145 10.10.10.22 10.10.10.10 TDS:Response, Version = 7.4 (0x74000004), SPID = 255, PacketID = 1, Flags=.

Create a new MARS session

After the connection is established, create a new MARS session (Sid = 0).

Diagram of new MARS session.

Frame Time Offset Source IP   Dest IP     Description
----- ----------- ----------- ----------- ---------------------------------------------------------------------------
18024 568.2170301 10.10.10.10 10.10.10.22 SMP:SYN, Sid = 0, Length = 16, SeqNum = 0, Wndw = 4 {SMP:190, TCP:160, IPv4

Execute various commands on the session

Frame Time Offset Source IP   Dest IP     Description
----- ----------- ----------- ----------- ---------------------------------------------------------------------------
18028 568.2170301 10.10.10.10 10.10.10.22 TDS:SQLBatch, Version = 7.4 (0x74000004), SPID = 0, PacketID = 1, Flags=...
18031 568.2170676 10.10.10.22 10.10.10.10 TCP: [Bad CheckSum]Flags=...A...., SrcPort=1433, DstPort=52965, PayloadLen=
18038 568.2173641 10.10.10.22 10.10.10.10 TDS:Response, Version = 7.4 (0x74000004), SPID = 255, PacketID = 1, Flags=.
18079 568.2178650 10.10.10.10 10.10.10.22 TDS:SQLBatch, Version = 7.4 (0x74000004), SPID = 0, PacketID = 1, Flags=...

Example of the SMP:ACK packet

Diagram of MARS SMP:ACK packet.

Frame Time Offset Source IP   Dest IP     Description
----- ----------- ----------- ----------- ---------------------------------------------------------------------------
40874 568.5121135 10.10.10.22 10.10.10.10 TDS:Response, Version = 7.4 (0x74000004), SPID = 255, PacketID = 1, Flags=.
40876 568.5121237 10.10.10.22 10.10.10.10 TDS:Continuous Response, Version = 7.4 (0x74000004), SPID = 255, PacketID =
40911 568.5124644 10.10.10.10 10.10.10.22 SMP:ACK, Sid = 0, Length = 16, SeqNum = 34, Wndw = 40 {SMP:190, TCP:160, IP
40950 568.5128422 10.10.10.22 10.10.10.10 TDS:Continuous Response, Version = 7.4 (0x74000004), SPID = 255, PacketID =