Learn more about the TDS protocol

In the evening, I carefully read the content about the TDS protocol. This is one of Microsoft's undisclosed protocols, but many people are studying this! The plaintext transmission and simple encryption or overflow vulnerabilities of passwords emerge one after another. The following is a TDS protocol document from abroad.

This document attempts to cover the TDS protocol:
TDS Version Supported Products
4.2 Sybase SQL Server <10 and Microsoft SQL Server 6.5
5.0 Sybase SQL Server> = 10
7.0 Microsoft SQL Server 7.0
7.1 Microsoft SQL Server 2000
7.2 Microsoft SQL Server 2005

Contents
Common Terms
Typical Usage Sequences
The Packet Format
Login Packet
TDS 7.0 Login Packet
Collation structure
Client requests
Server Responses
OCBC stored procedures (by jtds)

Common Terms


TDS protocol versions
TDS 5.0 tds version 5.0
TDS 7.0 tds version 7.0
TDS 7.0 + tds version 7.0, 7.1 and 7.2
TDS 5.0-tds version 5.0 and previous

Variable types used in this document:
CHAR 8-bit char
CHAR [6] string of 6 chars
CHAR [n] variable length string
XCHAR single byte (TDS 5.0-) or ucs2le (TDS 7.0 +) characters
INT8 8-bit int
INT16 16-bit int
INT32 32-bit int
UCS2LE Unicode in UCS2LE format

Note: FreeTDS uses TDS_TINYINT for INT8 and TDS_SMALLINT for INT16.

Typical Usage sequences
These are TDS 4.2 and not meant to be 100% correct, but I thought they might be helpful to get an overall view of what goes on.

--> Login
<-- Login acknowledgement

--> Insert SQL statement
<-- Result Set Done

--> Select SQL statement
<-- Column Names
<-- Column Info
<-- Row Result
<-- Row Result
<-- Result Set Done

--> Call stored procedure
<-- Column Names
<-- Column Info
<-- Row Result
<-- Row Result
<-- Done Inside Process
<-- Column Names
<-- Column Info
<-- Row Result
<-- Row Result
<-- Done Inside Process
<-- Return Status
<-- Process Done

The packet format
Every informations in TDS protocol (query, RPCs, responses and so on) is splitted in packets.

All packets start with the following 8 byte header.

INT8 INT8 INT16 4 bytes
+ ---------- + ------------- + ---------- + -------------------- +
| Packet | last packet | unknown |
| Type | indicator | size |
+ ---------- + ------------- + ---------- + -------------------- +

Fields:
Packet type
0x01 TDS 4.2 or 7.0 query
0x02 TDS 4.2 or 5.0 login packet
0x03 RPC
0x04 responses from server
0x06 cancels
0x07 Used in Bulk Copy
0x0F TDS 5.0 query
0x10 TDS 7.0 login packet
0x11 TDS 7.0 authentication packet
0x12 TDS 8 prelogin packet
Last packet indicator
0x00 if more packets
0x01 if last packet
Packet size
(In network byte order)
Unknown?
Always 0x00
This has something to do with server to server communication/rpc stuff

The remainder of the packet depends on the type of information it is providing. as noted above, packets break down into the types query, login, response, and cancels. response packets are further split into multiple sub-types denoted by the first byte (a.k. a. the token) following the above header.

Note: a tds packet that is longer than 512 bytes is split on the 512 byte boundary and the "more packets" bit is set. the full TDS packet is reassembled from its component 512 byte packets with the 8-byte headers stripped out. 512 is the block_size in the login packet, so it cocould be set to a different values. in Sybase you can configure a range of valid block sizes. TDS 7.0 + use a default of 4096 as block size.


--------------------------------------------------------------------------------



TDS 4.2 & 5.0 Login Packet
Packet type (first byte) is 2. The numbers on the left are decimal offsets including the 8 byte packet header.

Byte var type description
------------------------------
8 CHAR [30] host_name
38 INT8 host_name_length
39 CHAR [30] user_name
69 INT8 user_name_length
70 CHAR [30] password
100 INT8 password_length
101 CHAR [30] host_process
131 INT8 host_process_length
132? Magic1 [6]/* mystery stuff */
138 INT8 bulk_copy
139? Magic2 [9]/* mystery stuff */
148 CHAR [30] app_name
178 INT8 app_name_length
179 CHAR [30] server_name
209 INT8 server_name_length
210? Magic3 [1]/* 0, dont know this one either */
211 INT8 password2_length
212 CHAR [30] password2
242 CHAR [223] magic4
465 INT8 password2_length_plus2
466 INT16 major_version/* TDS version */
468 INT16 minor_version/* TDS version */
470 CHAR library_name [10]/* "Ct-Library" or "DB-Library "*/
480 INT8 & nbs