SQL Server的TDS协议详解：Tabular Data Stream

需积分: 48 133 浏览量更新于2024-07-21 收藏 5.2MB PDF 举报

"[MS-TDS]:Tabular Data Stream Protocol - SQL Server的数据传输协议，由Microsoft Corporation在2015年6月30日发布，详细介绍了TDS协议的知识产权声明和技术文档。" Tabular Data Stream (TDS)协议是微软SQL Server用于客户端与服务器之间进行数据交换的一种协议。它允许应用程序与SQL Server实例进行通信，执行查询、事务管理、数据库对象操作等。TDS是基于TCP/IP的，但也可以通过其他网络协议进行封装。 TDS协议的主要组成部分包括： 1. **连接建立**：客户端首先发起一个连接请求，服务器响应并建立连接。这通常涉及到认证过程，如SQL Server身份验证或Windows集成身份验证。 2. **请求与响应**：一旦连接建立，客户端可以发送TDS包到服务器，这些包可能包含SQL查询、存储过程调用或其他数据库命令。服务器处理这些请求后，会返回结果集、错误信息或状态更新。 3. **数据传输**：TDS协议支持多种数据类型，包括数值、字符串、日期时间、二进制等，能够高效地传输这些数据。结果集通过TDS包分块发送，允许大结果集的流式处理，而不会过度消耗内存。 4. **事务管理**：TDS协议支持ACID（原子性、一致性、隔离性和持久性）事务特性，允许客户端和服务器协调事务的开始、提交、回滚和保存点。 5. **批处理**：客户端可以将多个SQL语句作为一个批处理发送到服务器，这样可以减少网络开销，提高性能。 6. **预编译的批处理**：通过使用游标（Cursor），TDS允许客户端对结果集进行迭代处理，支持向前和向后导航，以及数据的增删改查。 7. **错误处理和通知**：TDS协议定义了错误报告机制，当发生错误时，服务器会发送一个错误包给客户端。此外，还可以设置服务器推送通知，当数据库中的某些事件发生时，服务器主动通知客户端。 8. **安全特性**：TDS协议支持加密，确保数据在传输过程中的安全性。此外，还有权限控制和审计功能，确保只有授权的用户和应用能访问数据。 9. **版本兼容性**：随着SQL Server的不同版本发布，TDS协议也在不断演进。例如，[MS-TDS]文档可能包含了特定版本的TDS协议细节，如v20150630，以适应新功能和优化。 10. **开放规格文档**：尽管TDS协议是SQL Server的核心部分，但微软也发布了开放规格文档，允许第三方开发者实现与SQL Server的互操作性，例如ODBC驱动程序、JDBC驱动程序和其他数据库连接工具。 TDS协议是SQL Server生态系统的关键组件，它为各种应用提供了一种标准化的方式，来与SQL Server进行高效且可靠的数据交互。通过理解和掌握TDS协议，开发者可以更好地优化数据库应用程序，提升系统性能，以及实现跨平台的数据库连接。

16 / 194

[MS-TDS] - v20150630

Tabular Data Stream Protocol

Release: June 30, 2015

1.6 Applicability Statement

The TDS protocol is appropriate for use to facilitate request/response communications between an

application and a database server in all scenarios where network or local connectivity is available.

1.7 Versioning and Capability Negotiation

This document covers versioning issues in the following areas.

 Supported Transports: This protocol can be implemented on top of any network transport

protocol as discussed in section 2.1.

 Protocol Versions: The TDS protocol supports the TDS 7.0, TDS 7.1, TDS 7.2, TDS 7.3, and TDS

7.4 explicit dialects. The dialect version is negotiated as part of the LOGIN7 message data stream,

which is defined in section 2.2.6.4.

Note After a protocol feature is introduced, subsequent versions of the TDS protocol support that

feature until that feature is removed.

 Security and Authentication Methods: The TDS protocol supports SQL Server User

Authentication (SQLAUTH). The TDS protocol also supports SSPI authentication and indirectly

supports any authentication mechanism that SSPI supports. The use of SSPI in TDS is defined in

sections 2.2.6.4 and 3.2.5.1. The TDS protocol also supports federated authentication.<3> The

use of federated authentication in TDS is defined in sections 2.2.6.4 and 3.2.5.

 Localization: Localization-dependent protocol behavior is specified in sections 2.2.5.1.2 and

2.2.5.6.

 Capability Negotiation: This protocol does explicit capability negotiation as specified in this

section.

In general, the TDS protocol does not provide facilities for capability negotiation because the complete

set of supported features is fixed for each version of the protocol. Certain features such as

authentication type are not usually negotiated but rather are requested by the client. However, the

protocol supports negotiation for the following two features:

 Channel encryption: The encryption behavior that is used for the TDS session is negotiated in

the initial messages exchanged by the client and the server.

 Authentication mechanism for integrated authentication identities: The authentication

mechanism that is used for the TDS session is negotiated in the initial messages exchanged by the

client and the server.

For more details about encryption behavior and about how the client and server negotiate between

SSPI authentication and federated authentication, see the PRELOGIN description in section 2.2.6.5.

Note that the cipher suite for TLS/SSL and the authentication mechanism for SSPI and federated

authentication are negotiated outside the influence of TDS in [RFC2246] and [SSL3].

1.8 Vendor-Extensible Fields

None.

1.9 Standards Assignments

Parameter

TCP port value

Reference

Default SQL Server instance TCP port

1433

[IANAPORT]

17 / 194

[MS-TDS] - v20150630

Tabular Data Stream Protocol

Release: June 30, 2015

2 Messages

The formal syntax of all messages is provided in Augmented Backus-Naur Form (ABNF), as specified in

[RFC4234].

2.1 Transport

The TDS protocol does not prescribe a specific underlying transport protocol to use on the Internet or

on other networks. TDS only presumes a reliable transport that guarantees in-sequence delivery of

data.

The chosen transport can be either stream-oriented or message-oriented. If a message-oriented

transport is used, any TDS packet sent from a TDS client to a TDS server MUST be contained within a

single transport data unit. Any additional mapping of TDS data onto the transport data units of the

protocol in question is outside the scope of this specification.

The current version of the TDS protocol has implementations over the following transports:<4>

 TCP [RFC793].

 A reliable transport over the Virtual Interface Architecture (VIA) interface [VIA2002].<5>

 Named Pipes [PIPE].

 Optionally, the TDS protocol has implementations for the following two protocols on top of the

preceding transports:

 Transport Layer Security (TLS) [RFC2246]/Secure Socket Layer (SSL), in case TLS/SSL

encryption is negotiated.

 Session Multiplex Protocol (SMUX) [MC-SMP], in case the Multiple Active Result Sets

(MARS) feature [MSDN-MARS] is requested.

2.2 Message Syntax

Character data, such as SQL statements, within a TDS message is in Unicode, unless the character

data represents the data value of an ASCII data type, such as a non-Unicode data column. A character

count within TDS is a count of characters, rather than of bytes, except when that character count is

explicitly specified as a byte count.

2.2.1 Client Messages

Messages sent from the client to the server are as follows:

 Pre-Login

 Login

 Federated Authentication Token<6>

 SQL Batch

 Bulk Load

 Remote Procedure Call

 Attention

18 / 194

[MS-TDS] - v20150630

Tabular Data Stream Protocol

Release: June 30, 2015

 Transaction Manager Request

These messages are briefly described in the sections that follow. Detailed descriptions of message

contents are in section 2.2.6.

2.2.1.1 Pre-Login

Before a login occurs, a Pre-Login handshake occurs between client and server, setting up contexts

such as encryption and MARS-enabled. For more details, see section 2.2.6.5.

2.2.1.2 Login

When the client makes the determination to establish a TDS protocol connection with the server side,

the client sends a Login message data stream to the server. The client can have more than one

connection to the server, but each connection is established separately in the same way. For more

details, see section 2.2.6.4.

After the server receives the login record from the client and, if necessary, performs subsequent

authentication handshakes (such as when SSPI [SSPI] or federated authentication<7> is used), the

server notifies the client that it has either accepted or rejected the connection request. For more

details, see section 3.3.5.1.

2.2.1.3 Federated Authentication Token

When the client indicates in the Login record that federated authentication<8> is to be used but that

the intended client library needs additional information from the server to generate a federated

authentication token, if the server supports federated authentication that uses that client library, the

server responds with a token that the client uses to perform federated authentication. The client then

generates and sends a tokenless Federated Authentication Token message that contains binary

authentication data that is generated by the federated authentication library. For more details, see

section 2.2.6.3.

After the server receives the Federated Authentication Token message from the client, the server

notifies the client that it has either accepted or rejected the connection request. For more details, see

section 3.3.5.

2.2.1.4 SQL Batch

To send a SQL statement or a batch of SQL statements, the SQL batch, represented by a Unicode

string, is copied into the data section of a TDS packet and then sent to the database server that

supports SQL. A SQL batch can span more than one TDS packet. For more details, see section 2.2.6.7.

2.2.1.5 Bulk Load

In a bulk insert/bulk load operation, a SQL statement consists of a Unicode string that is followed by

binary data. The client sends the INSERT BULK SQL statement and then sends a COLMETADATA

token that describes the raw data. Multiple rows of binary data are then sent to the server. The data is

not formatted in storage row format but in the format described by the COLMETADATA token. The

stream is the same as if the data were being selected from the server rather than being sent to the

server. For more details, see section 2.2.6.1.

2.2.1.6 Remote Procedure Call

To execute a remote procedure call (RPC) on the server, the client sends an RPC message data stream

to the server. This is a binary stream that contains the RPC name or numeric identifier, options, and

19 / 194

[MS-TDS] - v20150630

Tabular Data Stream Protocol

Release: June 30, 2015

parameters. RPCs MUST be in a separate TDS message and not intermixed with SQL statements.

There can be several RPCs in one message. For more details, see section 2.2.6.6.

2.2.1.7 Attention

The client can interrupt and cancel the current request by sending an Attention message. This is also

known as out-of-band data, but any TDS packet that is currently being sent MUST be finished before

sending the Attention message. After the client sends an Attention message, the client MUST read

until it receives an Attention acknowledgment.

If a complete request has been sent to the server, sending a cancel requires sending an Attention

packet. An example of this behavior is if the client has already sent a request, which has the last

packet with EOM bit (0x01) set in status. The Attention packet is the only way to interrupt a

complete request that has already been sent to the server. For more information, see section 4.19.2.

If a complete request has not been sent to the server, the client MUST send the next packet with both

ignore bit (0x02) and EOM bit (0x01) set in the status to cancel the request. An example of this

behavior is if one or more packets have been sent but the last packet with EOM bit (0x01) set in

status has not been sent. Setting the ignore and EOM bits terminates the current request, and the

server MUST ignore the current request. When the ignore and EOM bits are set, the server does not

send an attention acknowledgment, but instead returns a table response with a single DONE token

that has a status of DONE_ERROR to indicate that the incoming request was ignored. For more details

about the buffer header status code, see section 2.2.3.1.2.

2.2.1.8 Transaction Manager Request

The client can request that the connection enlist in a transaction as described in [MSDN-DTC].

2.2.2 Server Messages

Messages sent from the server to the client are the following:

 Pre-Login Response

 Login Response

 Federated Authentication Information<9>

 Row Data

 Return Status

 Return Parameters

 Response Completion

 Error and Info

 Attention Acknowledgement

These messages are briefly described in the sections that follow. Detailed descriptions of message

contents are in section 2.2.6 and section 2.2.7.

2.2.2.1 Pre-Login Response

The Pre-Login Response message is a tokenless packet data stream. The data stream consists of the

response to the information requested by the client's Pre-Login message. For more details, see section

2.2.6.5.

20 / 194

[MS-TDS] - v20150630

Tabular Data Stream Protocol

Release: June 30, 2015

2.2.2.2 Login Response

The Login Response message is a token stream that consists of information about the server's

characteristics, optional information and error messages, and finally, a completion message.

The LOGINACK token data stream includes information about the server interface and the server's

product code and name. For more details, see section 2.2.7.13.

If there are any messages in the login response, an ERROR or INFO token data stream is returned

from the server to the client. For more details, see sections 2.2.7.9 and 2.2.7.12.

The server can send, as part of the login response, one or more ENVCHANGE token data streams if

the login changed the environment and the associated notification flag was set. An example of an

environment change includes the current database context and language setting. For more details, see

section 2.2.7.8.

A done packet MUST be present as the final part of the login response, and a DONE token data stream

is the last thing sent in response to a server login request. For more details, see section 2.2.7.5.

2.2.2.3 Federated Authentication Information

After the server receives a Login message that states that the client intends to use a federated

authentication<10> token from a specific client library that needs additional information from the

server to generate that token, if the server supports federated authentication that uses that client

library, the server responds to the client with a message. This message contains a Federated

Authentication Information Token that provides the information necessary for the client to generate a

federated authentication token. If the server determines that no information is required for this

particular client library, the server does not send the information token. For more details, see section

2.2.7.11.

2.2.2.4 Row Data

If the server request results in data being returned, the data will precede any other data streams

returned from the server except warnings. Row data MUST be preceded by a description of the column

names and data types. For more information about how the column names and data types are

described, see section 2.2.7.4.

2.2.2.5 Return Status

When a stored procedure is executed by the server, the server MUST return a status value. This is a

4-byte integer and is sent via the RETURNSTATUS token. A stored procedure execution is requested

through either an RPC Batch or a SQL Batch message. For more information, see section 2.2.7.17.

2.2.2.6 Return Parameters

The response format for execution of a stored procedure is identical regardless of whether the request

was sent as SQL Batch or RPC Batch. It is always a tabular result-type message.

If the procedure explicitly sends any data, then the message starts with a single token stream of rows,

informational messages, and error messages. This data is sent in the usual way.

When the RPC is invoked, some or all of its parameters are designated as output parameters. All

output parameters will have values returned from the server. For each output parameter, there is a

corresponding return value, sent via the RETURNVALUE token. The RETURNVALUE token data stream

is also used for sending back the value returned by a user-defined function (UDF), if it is called as an

RPC. For more details about the RETURNVALUE token, see section 2.2.7.18.

剩余193页未读，继续阅读

pearlzzz

粉丝: 1

SQL Server的TDS协议详解：Tabular Data Stream

Tabular技术

MS SQL TDS协议

pytds:使用纯Python TDS（表格数据流）协议实现的MSSQL的Python DBAPI驱动程序

MS-TDS协议规范最新版[v20120328]

"MS-TDS协议2023最新版及使用规范V34.0

MS SQL Server TDS协议 v4.2

TDS通信协议

TDS协议分析程序

SQL Server 2005模拟试题：微软认证70-431考试实战攻略

jdbc:sybase:Tds:10.39.192.7:4000

最新资源