SQL Passwords: Encrypted Between Client and Server?

,

It depends. 🙂

Tabular Data Stream (TDS)—the protocol used for client-to-Microsoft® SQL Server® communications—gives both client and server three options in regards to encryption: require, support or doesn’t support. Whether SQL Authentication credentials are encrypted depends on which encryption options are in use.

Encrypted

If both client and server are configured to require encryption, the entire connection, except for a small amount of initialization information, is encrypted. Included in the encrypted portion are the SQL Authentication credentials.

If either client or server is configured to require encryption and the other side supports encryption, the connection is encrypted, just as above.

Encrypted (Hopefully)

If both client and server support encryption but neither side requires it, the connection isn’t encrypted. Instead, just the first TDS packet of the login message sent from client to server is encrypted. The rest of the connection, including any additional login message packets, is sent unencrypted.

A properly designed database driver will ensure that SQL credentials are always included in the first TDS packet of the client’s login message. However, nothing in the protocol or on the server mandates this. From the technical standpoint, if the client splits the login message into multiple TDS packets (i.e. because it contains a large amount of settings data), the SQL password could end up in packet two. If this occurs, the password won’t be encrypted. A good database driver will ensure that SQL credentials are only sent in the first login packet, aborting if this is not possible.

Not Encrypted

If either client or server doesn’t support encryption, the entire connection, including the login exchange, will be unencrypted.

If you watch a non-encrypted login using a packet sniffer, you might notice that the password field looks like it’s encrypted. Don’t be deceived. It’s not. Regardless of whether encryption is enabled, the login message’s password is always obfuscated—but this scrambling can easily be reversed to reveal the original password.

Connection Terminated

Encryption required and not supported are mutually exclusive. If either client or server is configured to require encryption and the other side doesn’t support it, the connection is terminated before the login exchange takes place.

Conclusion

The only way to ensure that login credentials are encrypted on the wire is for either client or server (or both) to require encryption. If encryption is not required, the possibility exists that the login request will be sent unencrypted. There’s no option to disallow completely unencrypted connections without requiring full encryption.

If ensuring that passwords are encrypted while in transit is important to you (it should be!), you must require encrypted connections.

If you’d like to learn more about TDS, including more about what information is exchanged during login, you may enjoy watching T-SQL on the Wire: What takes place between client and server?

Leave a Reply

Your email address will not be published. Required fields are marked *