TDS协议解析

文章来自：http://freetds.cvs.sourceforge.net/*checkout*/freetds/freetds/doc/tds.html

该网站是免费的专门介绍TDS协议的，网址是：http://www.freetds.org/

页面是我贴过来的，部分跳转有些问题，有兴趣的可以直接浏览原页面。

 

 

This document attempts to cover the TDS protocol for:

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 |  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 could 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, don't 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        library_length
 481   INT16       major_version2     /* program version */
 483   INT16       minor_version2     /* program version */
 485   ?           magic6[3]          /* ? last two octets are 13 and 17 */
                                      /* bdw reports last two as 12 and 16 here  */
                                      /* possibly a bitset flag  */
 488   CHAR[30]    language           /* e.g. "us-english" */
 518   INT8        language_length
 519   ?           magic7[1]          /*  mystery stuff */
 520   INT16       old_secure         /* explanation? */
 522   INT8        encrypted          /*  1 means encrypted all password fields blank */
 523   ?           magic8[1]          /*  no clue... zeros */
 524   CHAR        sec_spare[9]       /* explanation? */
 533   CHAR[30]    char_set           /* e.g. "iso_1" */
 563   INT8        char_set_length
 564   INT8        magic9[1]          /* 1 */ 
 565   CHAR[6]     block_size         /*  in text */
 571   INT8        block_size_length 
 572   ?           magic10[25]        /* lots of stuff here...no clue */

Any help with the magic numbers would be most appreciated.

---

 

TDS 7.0+ Login Packet

byte  var type  description
---------------------------
  0   INT32	total packet size
  4   INT8[4]	TDS Version	
                	0x00000070 7.0
			0x01000071 7.1
                	0x02000972 7.2 (7.2.9?)
  8   INT32	packet size (default 4096)
 12   INT8[4]	client program version
 16   INT32	PID of client
 20   INT32	connection id (usually 0)
 24   INT8	option flags 1
                0x80 enable warning messages if SET LANGUAGE issued
                0x40 change to initial database must succeed
                0x20 enable warning messages if USE <database> issued
                0x10 enable BCP
		0x08 use ND5000 floating point format (untested)
		0x04 use VAX floating point format (untested)
		0x02 use EBCDIC encoding (untested)
		0x01 use big-endian byte order (untested)
 25   INT8	option flags 2
                0x80 enable domain login security
		0x40 "USER_SERVER - reserved" 
		0x20 user type is "DQ login"
		0x10 user type is "replication login"
		0x08 "fCacheConnect"
		0x04 "fTranBoundary"
                0x02 client is an ODBC driver
                0x01 change to initial language must succeed
 26   INT8	0x04 spawn user instance (TDS 7.2)
                0x02 XML data type instances are returned as binary XML (TDS 7.2)
                0x01 password change requested (TDS 7.2)
 27   INT8	0x01 SQL Type: 0 = use default, 1 = use T-SQL (TDS 7.2)
 28   INT8[4]	time zone (0x88ffffff ???)
 32   INT8[4]	collation information
 36   INT16	position of client hostname (86)
 38   INT16	hostname length
 40   INT16	position of username
 42   INT16	username length
 44   INT16	position of password
 46   INT16	password length
 48   INT16	position of app name
 50   INT16	app name length
 52   INT16	position of server name
 54   INT16	server name length
 56   INT16	position of remote server/password pairs
 58   INT16	remote server/password pairs length
 60   INT16	position of library name
 62   INT16	library name length
 64   INT16	position of language
 66   INT16	language name (for italian "Italiano", coded UCS2)
 68   INT16	position of database name
 70   INT16	database name length
 72   INT8[6]	MAC address of client
 78   INT16	position of auth portion
 80   INT16	NT authentication length
 82   INT16	next position (same as total packet size)
 84   INT16	0
 86   UCS2LE[n] hostname
      UCS2LE[n]	username
      UCS2LE[n]	encrypted password
      UCS2LE[n]	app name
      UCS2LE[n]	server name
      UCS2LE[n]	library name
      UCS2LE[n]	language name
      UCS2LE[n]	database name
      NT Authentication packet

NT Authentication packet
  0   CHAR[8]	authentication id "NTLMSSP\0"
  8   INT32     1  message type
 12   INT32	0xb201 flags
 16   INT16	domain length
 18   INT16     domain length
 20   INT32     domain offset
 24   INT16     hostname length
 26   INT16     hostname length
 28   INT32     hostname offset
 32   CHAR[n]   hostname
      CHAR[n]   domain
See documentation on Samba for detail (or search ntlm authentication for IIS)

For mssql 2005 before hostname (byte 86) you have
 86   INT16     next position,  or 
                position of file name for a database to be 
                attached during the connection process
 88   INT16     database filename length
 90   INT16     new password position
 92   INT16     new password length
 94   UCS2LE[n] hostname
      ... (as above)