The TDS wire protocol
Babelfish implements SQL Server behavior over the TDS wire protocol on a PostgreSQL server. Babelfish supports connection over the following networking types with supporting protocols:
- VIA (Virtual Interface Architecture)
- Transport Layer Security (TLS) with Secure Socket Layer (SSL)
Basic message flow
The message flow starts with the client reaching out to the server with a connection request; when the server responds, the client replies with an authentication request. After successfully authenticating, further client requests and server responses are passed back and forth between the client and server. TDS relies on network transmission order between the client and server to maintain the handshake.
TDS client communications
Each client request or server response is made up of one or more packets. Each packet contains a TDS header with the following elements:
- Type: The type of message
- Status: State of the message
- Length: Length of the message from the start of the packet. This is an unsigned short header to the end of the token data (up to 65535).
- SPID: Session ID of the current connection.
- PacketID: For each packet sent, PacketID is incremented. It is especially important for messages spanning multiple packets. The value is sent as “modulo 255”, which means that 1 byte is needed.
- Window: Currently unused. The value sent is 0x0.
Before a connection is established, the TDS protocol requires PRELOGIN communication to negotiate some important connection attributes. Vital info is exchanged, and the system can terminate the connection attempt if the client and server do not understand each other.
After successful PRELOGIN communication, the client can facilitate an SSL/TLS handshake. The client negotiates authentication and encryption based on the SSL configuration of your Babelfish server.
Once past PRELOGIN, the client can use LOGIN7 to define the authentication rules that will be used between client and server.
SSL/TLS support for TDS connections
Babelfish uses native PostgreSQL support for using SSL connections to encrypt client/server communications. See the PostgreSQL documentation for details about configuring server-side SSL functionality.
To use SSL encryption, compile the PostgreSQL server with support for SSL, and set the
ssl parameter to
on in the postgresql.conf file. The server will listen for SSL and non-SSL connections on the same TCP port, and will negotiate with any connecting client about using SSL. By default, a client can choose whether to require an SSL connection or not. The server will listen for both unencrypted and encrypted connections on the same TCP port, and negotiate the connection type with the connecting client. If
babelfishpg_tds.tds_ssl_encrypt is set to
true, end-to-end encryption is enforced for all connections. In that case, if a client requests an unencrypted connection, the connection is rejected.
The following table shows how Babelfish interprets SSL settings when a client connects:
|Client SSL setting||Babelfish SSL setting||Connection allowed?||Value returned to client|
|ENCRYPT_OFF||tds_ssl_encrypt=false||Allowed, the login packet is encrypted||ENCRYPT_OFF|
|ENCRYPT_OFF||tds_ssl_encrypt=true||Allowed, the entire connection is encrypted||ENCRYPT_REQ|
|ENCRYPT_ON||tds_ssl_encrypt=false||Allowed, the entire connection is encrypted||ENCRYPT_ON|
|ENCRYPT_ON||tds_ssl_encrypt=true||Allowed, the entire connection is encrypted||ENCRYPT_ON|
|ENCRYPT_NOT_SUP||tds_ssl_encrypt=true||No, connection closed||ENCRYPT_REQ|
|ENCRYPT_REQ||tds_ssl_encrypt=false||Allowed, the entire connection is encrypted||ENCRYPT_ON|
|ENCRYPT_REQ||tds_ssl_encrypt=true||Allowed, the entire connection is encrypted||ENCRYPT_ON|
|ENCRYPT_CLIENT_CERT||tds_ssl_encrypt=false||No, connection closed||Unsupported|
|ENCRYPT_CLIENT_CERT||tds_ssl_encrypt=true||No, connection closed||Unsupported|