Encryption in Microsoft SQL Server

As other DBMS vendors, Microsoft uses SSL protocol for its clients’ protection. However, few are aware that unlike other DBMS, MsSQL encapsulates SSL handshake within TDS.

The trick might be done deliberately to reduce the number of potential threats to MS servers, as today vendors use SSL in simple protocol layer combination as full encapsulation. It is easier and more universal because turning SSL on/off does not affect the main protocol.

Encryption in MsSQL is effected via two modes: authorization stage protection and complete connection protection. In both cases, the dialogue begins with the client-server exchange of settings: the client sends its PRELOGIN packet and receives the same packet from the server. Then the client directs the first handshake packet after having encapsulated it in TDS as PRELOGIN. All the following SSL packets are encapsulated the same way in TDS until the handshake is completed.

Though, the first and all the subsequent SSL application packets are sent as they are without being encapsulated in TDS. The packet encapsulates LOGIN7-authorization query. Here is where modes start to differ. SSL encryption of the first mode is completed and the following traffic between the client and the server is no more encrypted. During the second mode, encryption is implemented up to closing of the session.

It’s worth noting that the server, in fact, can function without encryption, but MSSQL official clients do not provide this option. Why is it important? Encryption in the channel in one way or the other prevents passive traffic collection and analysis, i.e. sniffing, that represents one of our main functions.

In case it is impossible to modify traffic, sniffer functionality is limited. Firstly, it refers to ephemeral key-based ciphers. All ECDHE- and DHE-based ciphers fall under this category, e.g.: