Tabular Data Stream (TDS) Protocol

The Tabular Data Stream (TDS) Protocol is an application-level protocol used for the transfer of requests and responses between clients and database server systems. In such systems, the client will typically establish a long-lived connection with the server. Once the connection is established using a transport-level protocol, TDS messages are used to communicate between the client and the server.

TDS session is directly tied to the transport-level session, meaning that a TDS session is established when the transport-level connection is established and the server receives a request to establish a TDS connection. It persists until the transport-level connection is terminated. TDS assumes the transport protocol supports reliable, in-order delivery of the data.

TDS includes facilities for authentication and identification, channel encryption negotiation, issuing of SQL batches, stored procedure calls also known as a remote procedure call (RPC), returning data, and transaction manager requests.

The following diagram shows a (simplified) typical flow of communication in the TDS Protocol

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

• TCP.
• A reliable transport over the Virtual Interface Architecture (VIA) interface.
• Named Pipes.
• Optionally, the TDS protocol has implementations for the following two protocols on top of the preceding transports:
  • Transport Layer Security (TLS)/Secure Socket Layer (SSL), in case TLS/SSL encryption is negotiated.
  • Session Multiplex Protocol (SMUX), in case the Multiple Active Result Sets (MARS) feature is requested.

TDS Packet Header

Type – The type of message being sent.
Status – The state of the message.
Length – The length of the packet – as an unsigned short – from the start of the packet header to the end of the token data.
SPID – The session id for the connection.
PacketID – The index of the packet in the message. For messages spread across multiple packets, this would be incremented for each packet sent (mod 255).
Window – Unused. This will always be zero.

Below is sample of TDS packet captured in Microsoft Network Monitor

The TDS PRELOGIN Request & Response

When connecting to a SQL Server, the first thing that happens after connecting is a PRELOGIN exchange. This happens before any authentication or encryption. It’s a knock on the door, which allows the SQL Server and the client to check each other out to see if they are compatible with each other. If the SQL Server can’t understand what you send, it kills the connection right then and there – long before any authentication is attempted.

The TDS PRELOGIN packet header contains six pieces of information, stored in eight bytes.

1. Type – The client will always send 0x12 (PRELOGIN) and the server will always send 0x04 (TABULAR RESULT).
2. Status – Both the client and server will always send 0x01 (EOM or end of message), meaning this is the last packet in the message.
3. Length – The length of the packet – as an unsigned short – from the start of the packet header to the end of the token data.
4. SPID – In this case, a session id is not assigned until after authentication occurs, this will always be zero.
5. PacketID – Since we are only sending an receiving a single packet, this will always be zero. This is largely unused and can be ignored by always setting to zero.
6. Window – Unused. This will always be zero.

For the PRELOGIN packet, there are total of nine different tokens supported which are:

VERSION
ENCRYPTION
INSTOPT
THREADID
MARS
TRACEID
FEDAUTHREQUIRED
NONCEOPT
TERMINATOR