Skip to content

Normal Login Using a Domain Account and NTLM Authentication

Jump to bottom
Malcolm Stewart edited this page Jun 22, 2021 · 6 revisions

Normal Login Using a Domain Account and NTLM Authentication

Commented Trace

Frame Time Offset  Source IP    Dest IP      Description
----- ------------ ------------ ------------ ---------------------------------------------------------------------------------------------------
--- TCP 3-Way Handshake
76078 1181.9915832 10.10.10.120 10.10.10.55  TCP: [Bad CheckSum]Flags=......S., SrcPort=64444, DstPort=57139, PayloadLen=0, Seq=2766542083, Ack=
76079 1181.9922255 10.10.10.55  10.10.10.120 TCP:Flags=...A..S., SrcPort=57139, DstPort=64444, PayloadLen=0, Seq=3862866646, Ack=2766542084, Win
76080 1181.9924272 10.10.10.120 10.10.10.55  TCP: [Bad CheckSum]Flags=...A...., SrcPort=64444, DstPort=57139, PayloadLen=0, Seq=2766542084, Ack=

--- Driver Handshake
76081 1181.9936195 10.10.10.120 10.10.10.55  TDS:Prelogin, Version = 7.3 (0x730a0003), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=64444, Ds
76082 1181.9945238 10.10.10.55  10.10.10.120 TDS:Response, Version = 7.3 (0x730a0003), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=57139, Ds

--- SSL/TLS Handshake
76083 1181.9953108 10.10.10.120 10.10.10.55  TLS:TLS Rec Layer-1 HandShake: Client Hello. {TLS:3192, SSLVersionSelector:3191, TDS:3190, TCP:3189
76084 1181.9967001 10.10.10.55  10.10.10.120 TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate. Server Hello Done. {TLS:3192, SSLVersionS
76085 1181.9978947 10.10.10.120 10.10.10.55  TLS:TLS Rec Layer-1 HandShake: Client Key Exchange.; TLS Rec Layer-2 Cipher Change Spec; TLS Rec La
76086 1182.0010146 10.10.10.55  10.10.10.120 TLS:TLS Rec Layer-1 Cipher Change Spec; TLS Rec Layer-2 HandShake: Encrypted Handshake Message. {TL

--- Login Packet
76096 1182.0069763 10.10.10.120 10.10.10.55  TLS:TLS Rec Layer-1 SSL Application Data {TLS:3192, SSLVersionSelector:3191, TDS:3190, TCP:3189, IP

--- NTLM Challenge and Response packets
76097 1182.0093903 10.10.10.55  10.10.10.120 NLMP:NTLM CHALLENGE MESSAGE {TDS:3190, TCP:3189, IPv4:3187}
76098 1182.0102507 10.10.10.120 10.10.10.55  NLMP:NTLM AUTHENTICATE MESSAGEVersion:v2, Domain: CONTOSO, User: joe33, Workstation: 10.10.10.120 {

--- Login Confirmation
76100 1182.0274716 10.10.10.55  10.10.10.120 TDS:Response, Version = 7.3 (0x730a0003), SPID = 315, PacketID = 1, Flags=...AP..., SrcPort=57139, 

---Execute a Command
76102 1182.0324639 10.10.10.120 10.10.10.55  TDS:SQLBatch, Version = 7.3 (0x730a0003), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=64444, Ds
...

The difference between this login sequence and the SQL authentication sequence is the addition of two packets.
The NTLM Challenge packet is sent from the server to the client after the client sends the login packet.
The client then responds with credentials, which the server will confirm with the domain controller on its end.

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

Timeouts could result due to a thread-starved SQL Server, several simultaneous login requests resulting in queued authentication with the domain controller, a domain controller in a different geographical location, or a slow or non-responsive domain controller.

Note: The [Bad CheckSum] warnings are benign and are an indicator that checksum offloading is enabled, i.e. added at a lower level in the network stack than the trace is taken at. In the absence of other information, you can use this to determine 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.

Clone this wiki locally