The packet format
All packets have an 8 byte header
1 byte 1 byte 2 bytes 4 bytes
+----------+----------+---------+--------------------+
| packet | last | packet | |
| type | packet | size | unknown |
| |indicator | | |
+----------+----------+---------+--------------------+
Fields:
packet type
0x04 for responses from server,
0x02 for login packet,
0x0F for query.
(could be a bitwise adding of flags)
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
Login Packet
Packet type (first byte) is 2. The following is from tds.h the numbers on the left are offsets including the packet header.
/* 8 */ DBCHAR host_name[30];
/* 38 */ DBTINYINT host_name_length;
/* 39 */ DBCHAR user_name[30];
/* 69 */ DBTINYINT user_name_length;
/* 70 */ DBCHAR password[30];
/* 100 */ DBTINYINT password_length;
/* 101 */ DBCHAR host_process[30];
/* 131 */ DBTINYINT host_process_length;
/* 132 */ DBCHAR magic1[6]; /* here were most of the mystery stuff is */
/* 138 */ DBTINYINT bulk_copy;
/* 139 */ DBCHAR magic2[9]; /* here were most of the mystery stuff is */
/* 148 */ DBCHAR app_name[30];
/* 178 */ DBTINYINT app_name_length;
/* 179 */ DBCHAR server_name[30];
/* 209 */ DBTINYINT server_name_length;
/* 210 */ DBCHAR magic3; /* 0, dont know this one either */
/* 211 */ DBTINYINT password2_length;
/* 212 */ DBCHAR password2[30];
/* 242 */ DBCHAR magic4[223];
/* 465 */ DBTINYINT password2_length_plus2;
/* 466 */ DBSMALLINT major_version; /* TDS version */
/* 468 */ DBSMALLINT minor_version; /* TDS version */
/* 470 */ DBCHAR library_name[10]; /* Ct-Library or DB-Library */
/* 480 */ DBTINYINT library_length; /* Ct-Library or DB-Library */
/* 481 */ DBSMALLINT major_version2; /* program version */
/* 483 */ DBSMALLINT minor_version2; /* program version */
/* 485 */ DBCHAR magic6[3]; /* ? last two octets are 13 and 17 */
/* bdw reports last two as 12 and 16 here */
/* possibly a bitset flag */
/* 488 */ DBCHAR language[30]; /* ie us-english */
/* second packet */
/* 14 */ DBTINYINT language_length; /* 10 in this case */
/* 15 */ DBCHAR magic7; /* no clue... has 1 in the first octet */
/* bdw reports 0x0 */
/* 16 */ DBSMALLINT old_secure; /* explaination? */
/* 18 */ DBTINYINT encrypted; /* 1 means encrypted all password fields blank */
/* 19 */ DBCHAR magic8; /* no clue... zeros */
/* 20 */ DBCHAR sec_spare[9]; /* explaination */
/* 29 */ DBCHAR char_set[30]; /* ie iso_1 */
/* 59 */ DBTINYINT char_set_length; /* 5 */
/* 60 */ DBTINYINT magic9; /* 1 */
/* 61 */ DBCHAR block_size[6]; /* in text */
/* 67 */ DBTINYINT block_size_length;
/* 68 */ DBCHAR magic10[25]; /* lots of stuff here...no clue */
} TDSLOGIN;
Any help with the magic numbers would be most appreciated.
Server Responses
(Needs work)
Responses from the server start with a single octet (token) indentifying its type.
If variable length, they generally have the length as the second and third bytes
Markers encountered thus far: (All number are decimal)
0x21 33 'Language packet?' **bdw**
0x71 113 'Logout Packet?' **bdw**
0x79 121 return status
0xAE 174 unknown (has one byte for each column,
comes between result(238) and first row(209), I believe computed
column info is stored here, need to investigate)
0xD1 209 data row
0xD7 215 'param packet?' **bdw**
0xE2 226 'capability packet?' **bdw**
0xE3 227 environment change, used for database change, packet size, etc...
0xE5 229 message (handed to dberrhandle() func)
0xE6 230 'DBRPC?' **bdw**
0xEC 236 'param format packet?' **bdw**
0xEE 238 result set
0xFD 253 result set complete
0xFE 254 end of data seen instead of 253 when executing stored proc
0xFF 255 unknown (second byte has been 'A' or 'Q')
Result Set (0xEE 238)
1 byte 2 bytes 2 bytes variable size
+----------+---------+---------+--------------------+
| | | number | |
| token | length | of | column info |
| | | columns | |
+----------+---------+---------+--------------------+
Fields:
token 0xEE (238 decimal)
length length of message following this field
number of columns number of columns in the result set, this many column
information fields will follow.
Column Information
1 byte n bytes 1 byte 2 bytes 2 bytes 2 bytes
+----------+-----------------+---------+---------+---------+---------+
| column | column | | user | | column |
| name | name | unknown | type | unknown | type |
| length | | | | | |
+----------+-----------------+---------+---------+---------+---------+
1 byte 1 byte 1 byte 1 byte 1 byte 1 byte
+----------+----------+----------+----------+----------+----------+
| column | | | | | |
| size |precision | scale | length | table | unknown |
|(optional)|(optional)|(optional)|(optional)|(optional)| |
+----------+----------+----------+----------+----------+----------+
column name length
column name column name in result set, not necessarily db column name
unknown unknown (0, 16 ?)
user type usertype column from syscolumns
unknown always 0's
column type (need an appendix for discussion of column types)
column size not present for fixed size columns
precision present only for SYBDECIMAL and SYBNUMERIC
scale present only for SYBDECIMAL and SYBNUMERIC
table name length present only for SYBTEXT and SYBIMAGE
table name present only for SYBTEXT and SYBIMAGE
unknown always 0x00
Row Data (0xD1 209)
1 byte variable size +----------+--------------------+ | | | | token | row data | | | | +----------+--------------------+Row data starts with one byte (decimal 209), for variable length types, a one byte length field preceeds the data, for fixed length records just the data appears.
For example: sp_who
the first field is spid, a smallint
the second field is status a char(12)
the row would look like this: (unquoted numbers are decimal)
offset 0 209 // marker offset 1 1 // low order byte of smallint 1 offset 2 0 // high order byte of smallint 1 offset 3 12 // length of 'recv sleep ' offset 4 'r' offset 4 'e' offset 4 'c' offset 4 'v' offset 4 32 // space offset 4 's' offset 4 'l' offset 4 'e' offset 4 'e' offset 4 'p' offset 4 32 // space offset 4 32 // spaceResult Set Complete (0xFD 253)
1 byte 1 byte 1 byte 2 bytes 4 bytes +----------+---------+---------+---------+---------+ | | | | | row | | token | flags | unknown | unknown | count | | | | | | | +----------+---------+---------+---------+---------+ Fields: token 0xFD (253 decimal) flags unknown 0 /* a second flags byte? */ unknown 2,0 /* something to do with block size perhaps */ row count number of rows returned in the result set. Bit settings for byte 2: bit 0: set indicates more results coming bit 1: success (set if invalid sql, else clear) bit 4: set if succeeded ?
Note: the following things are courtesy of Brian Wheeler. I need to put them in a format similar to the rest of the document, but here they are for now. *begin bdw* 'Language packet' - I have no idea what this is...it is sent by client, I think... ubyte 33 int (1+length of next string) ubyte (status 1 or 0) string (data) **bdw** ERROR packet? ushort (discarded?) int (msg number) ubyte (state) ubyte (class) ubyte (-length of next string-) string (SQL state) ubyte (status) short (tranState) Transaction state? short (-length of next string-) string (message) ubyte (-length of next string-) string (server name) ubyte (-length of next string-) string (proc name) short (line number) **bdw** Logout packet (sent by client) byte 113 byte 0 **bdw** Param format packet (sent by client?) byte 236 short (datasize+2) short (number of parameters) list of formats. I imagine it uses the column format structure. **bdw** Param packet (sent by client?) byte 215 ??? beats me. *end bdw*Miscellaneous Notes
Acknowledgements
The following people have contributed to this document:
Brian Bruns (me)
Brian Wheeler
(short list)