ms sqlserver & sap ase 数据库server-client通讯协议 TDS

简介：

Tabular Data Stream (TDS) 是 ms sqlserver 和 SAP ASE 客户端和数据库服务器之间通讯的协议，TDS 是一个在不断“进化”的协议，目前微软 sql server 和 SAP ASE 使用的最新版本如下

DBMS Engine Versions	TDSVER or -V Value	TDS Protocol Version in use	TDS Protocol Version preferred by DBMS Engine
Sybase ASE 16.x	5.0	5.0	5.0
Microsoft SQL Server 2017 (v14.00.x)	10.0 or 7.3	7.3	7.4

版本的更新通常意味着增加新的数据类型，或者增加新的协议 token.
在使用 freetds 时，如果使用 sap ase 需要制定版本为 5.0,或者使用 auto，自动选择版本。

TDS & PDU:

一个 TDS 的请求或者响应可能包含多个 PDU，但是每一个 PDU 都是 PDU Header + PDU Data(可无) 构成。

A TDS packet that is longer than 512 bytes is split on the 512 byte boundary and the "more packets" bit is set.(default setting, TDS 7.0+, default 4096 bytes)

A TDS request or response may span multiple PDUs. The size of the PDU sent over the 
transport connection is negotiated at dialog establishment time. Each PDU contains a 
header, which is usually followed by data.

PDU Header

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   |  status     |   size   |                    |
+----------+-------------+----------+--------------------+
A PDU header contains information about the size and contents of the PDU as well as an 
indication if it is the last PDU in a request or response.

packet type:
TDS 是一个基于 token的协议， packet type 表示这个 packet的类型，例如：

Define	Value	Description
TDS_BUF_LANG	1	The buffer contains a language command. TDS does not specify the syntax of the language command. e.g. “select * from t1”
TDS_BUF_ERROR	10	A resource error was detected while attempting to setup or use a logical channel. This buffer is a header only and does not contain any data
TDS_BUF_NORMAL	15	This packet contains a tokenized TDS request or response.
…	…	…

packet status:

Define	Value	Description
TDS_BUFSTAT_EOM	0x01	This is the last buffer in a request or a response.
TDS_BUFSTAT_SEAL	0x01	The buffer is encrypte
…	…

packet length:

Length is the size of the buffer including the eight bytes in the buffer header. 
It is the number of bytes from the start of this header to the start of the next buffer 
header. For example, if there are 504 bytes of data in the buffer, Length will be 512. 
Length is a two-byte, unsigned integer. Regardless of the hardware architecture of 
either the server or the client, Length is represented by <MSB, LSB>. The most 
significant byte is first, followed by the least significant byte

header 中其他字节可用于 multiplexing，因为 sql 的一个连接通常表示一个session，client 发送请求后，一般需要等待 server 返回结果，这时，该连接就不能用于其他请求发送。
但是，当应用层协议增加 channel，packet no., window 等字段时，就可以通过在应用层将多个连接通过channel 区分，实现多路复用。header中其他字段，就可以表示 channel, packet no. 等等。

PDU Data:

根据 header 中的 packet type 来解释其为哪一种 packet，如果该 packet 包含 token，再根据不同的 token 来解释其中的数据, 例如：
Packet Type 为 0x0F，PDU Data Token 为 0x21，表示其为一个command，长度为20，内容为 “select * from t333”

PDU Header
  TDS Packet Type [1]:          BUF_NORMAL (0x0F)
  Status [1]:                   BUFSTAT_EOM (0x01)
  Length [2]:                   33
  Channel [2]:                  0
  Packet No. [1]:               0
  Window [1]:                   0

LANGUAGE Token (0x21); variable length.
  Length [4]:                   20
  Status [1]:                   UNUSED (0x00)
  Text Length [0]:              [19]
  Text [19]:                    "select * from t333

这是对应的 response:

c1 (int)	c2 (text)
9	null

PDU Header
  TDS Packet Type [1]:          BUF_RESPONSE (0x04)
  Status [1]:                   BUFSTAT_EOM (0x01)
  Length [2]:                   60
  Channel [2]:                  0
  Packet No. [1]:               0
  Window [1]:                   0

ROWFMT Token (0xEE);
  Length [2]:                   33
  Number of Columns [2]:        2
  Column 1
    Name Length [1]:            2
    Name [2]:                   "c1"
    Status [1]:                 ROW_UPDATABLE + ROW_NULLALLOWED (0x30)
    User Type [4]:              7
    Data Type [1]:              INTN
    Length [1]:                 4
    Locale Length [1]:          0
  Column 2
    Name Length [1]:            2
    Name [2]:                   "c2"
    Status [1]:                 ROW_UPDATABLE + ROW_NULLALLOWED (0x30)
    User Type [4]:              19
    Data Type [1]:              TEXT
    Length [4]:                 65536
    Locale Length [1]:          0

ROW Token (0xD1); variable length.
  Column 1
    Length [1]:                 4
    Row data [4]:               9 (0x00000009)
  Column 2
    Length [4]:                 0
    Row data [0]:               [null]

DONE Token (0xFD); fixed length.
  Length [0]:                   [8]
  Status [2]:                   DONE_COUNT (0x0010)
  TranState [2]:                TDS_TRAN_SUCCEED (0x0002)
  Count [4]:                    1

当 packet type 为 0x02 时，其 PDU Data 就全部解释为 login 的数据，没有 token，例如 :

PDU Header
  TDS Packet Type [1]:          BUF_LOGIN (0x02)
  Status [1]:                   BUFSTAT_BEGIN (0x00)
  Length [2]:                   512
  Channel [2]:                  0
  Packet No. [1]:               0
  Window [1]:                   0

PDU Header
  TDS Packet Type [1]:          BUF_LOGIN (0x02)
  Status [1]:                   BUFSTAT_EOM (0x01)
  Length [2]:                   107
  Channel [2]:                  0
  Packet No. [1]:               0
  Window [1]:                   0

Login Record; fixed length.
  Host Name [30]:               "linux-zcrv"
  Host Name Length [1]:         10
  User Name [30]:               "sa"
  User Name Length [1]:         2
  Password [30]:                "<masked>"
  Password Length [1]:          <masked> (0x00)
  Host Process [30]:            "13235"
  Host Process Length [1]:      5
  Byte Ordering - int2 [1]:     3
  Byte Ordering - int4 [1]:     1
  Character Encoding [1]:       6
  Float Format [1]:             10
  Date Format [1]:              9
  lusedb [1]:                   0x01
  ldmpld [1]:                   1
  linterfacespare [1]:          0x00
  Dialog Type [1]:              0
  lbufsize [1]:                 0
  spare [3]:                    0x000000
  Application Name [30]:        "isql"
  Application Name Length [1]:  4
  Service Name [30]:            "testserver4"
  Service Name Length [1]:      11
  Password Length [1]:          <masked> (0x00)
  TDS Version [4]:              5.0.0.0
  Prog Name [10]:               "CT-Library"
  Prog Name Length [1]:         10
  Prog Version [4]:             16.0.3.9
  Convert Shorts [1]:           0
  4-byte Float Format [1]:      13
  4-byte Date Format [1]:       17
  Language [30]:                "us_english"
  Language Length [1]:          10
  Notify when Changed [1]:      0
  Old Secure Info [2]:          0x0000
  Secure Login Flags [1]:       UNUSED (0x00)
  Bulk Copy [1]:                0
  HA Login Flags [1]:           0x08
  HA Session ID [6]:            0x000000000000
  Spare [2]:                    0x0000
  Character Set [30]:           "utf8"
  Character Set Length [1]:     4
  Notify when Changed [1]:      0
  Packet Size [6]:              512
  Packet Size Length [1]:       3