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 the 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.:

Moreover, to decrypt SSL traffic the low-level OpenSSL API has to be used. In its turn, it requires from us to keep the SSL sniffer constantly updated.

At the same time, the client needs to install a server key on the proxy’s (firewall’s) side. It is one more obstacle to data protection, as the client must entrust server protection to us or do not use official MsSQL client software (e.g. Microsoft SQL Server Management Studio) in sniffer mode.

As for ephemeral keys, they can be turned off on MsSQL’s server side. The Windows Cryptographic Service Provider allows to do it under normal conditions.

Thus, in spite of the obvious protocol restrictions on the part of Microsoft, the sniffer for MsSQL Server is included in the firewall. We’ve made every effort to make it easy for our customers to use it.

DataSunrise Database Security offers a complete security solution for Microsoft SQL Server or Azure SQL. DataSunrise includes database firewall, database activity monitoring and audit, discovery of sensitive data and more. DataSunrise supports also all other databases and data warehouses such as Oracle, IBM DB2, IBM Netezza, MySQL, MariaDB, Greenplum, Amazon Aurora, Amazon Redshift, Microsoft SQL Server, Azure SQL, Teradata and more. You are welcome to download a free trial if would like to install on your premises. In case you are a cloud user and run your database on Amazon AWS or Microsoft Azure you can get it from AWS market place or Azure market place.