While analyzing SQL Server's network protocol, I came across a weird fact: when a database client logs in using SQL Server authentication (as opposed to Windows authentication), it has to send the user's password to the server, in blatant violation of common security guidelines. At first, I couldn't believe it; SQL Server generally does an excellent job with security, and this seemed completely out of character -- but there it was.

I am certainly not the first person to discover this. In fact, Microsoft has a warning of sorts in its documentation:

Disadvantages of SQL Server Authentication

The encrypted SQL Server Authentication login password, must be passed over the network at the time of the connection.

I just can't believe it hasn't been fixed.

Let's look at how the password is transmitted, and the reasons why this is a bad idea.

What is the problem?

First, a little background. SQL Server supports two types of authentication:

• Windows authentication is the most common form of authentication into SQL Server. It uses the same authentication mechanism as other Windows components. No problem there: this is done right.

• SQL Server authentication is when you don't want to use a Windows domain account, and you just want to log in as a SQL Server user. It's commonly used by clients that are not on Windows, or Windows clients that just don't want to use a domain account. This form of authentication is where the problem lies.

Now, when a SQL Server client opens a connection to a SQL Server instance, it starts with a handshake to agree on protocol, feature levels, and so on. After that, there is a short exchange to open a TLS connection. So far, so good.

Then, if SQL Server authentication is being used, the client sends a packet containing the password in essentially clear form. It's actually slightly mangled, but an eight-year-old programmer would probably figure it out in less than five minutes.

Now, to be clear, the packet containing the password is encrypted using TLS, so you might think -- what's the harm? It's still encrypted, right?

Why this is a bad idea

TLS is quite secure per se, but only if it's enforced rigorously. It's common for SQL Server instances to use a self-signed certificate, or a certificate that is not easily verifiable. This means that it's quite common for database users to overlook any certificate problems, which means in turn that a man-in-the-middle attack is entirely possible (in fact, that's how Gallium Data works, though it's an authorized man-in-the-middle).

So if your connection is compromised (which is not as hard as it may sound), or (shudder) if your database server gets compromised, your password can be stolen.

Like most modern credential systems, SQL Server should use a method that does not transmit the password itself, but instead uses a single-use token, or something along these lines. A lot of very smart people have spent a lot of time figuring this stuff out. This problem was solved many years ago.

Some may argue that database authentication should not be used, that everyone knows they should use Windows authentication, etc... This is irrelevant. If you support an authentication method, and there is an obvious and well-accepted way to do it right, why do it wrong? And besides, most non-Windows clients are stuck with database authentication, so it's not going away.

And frankly, that Mickey Mouse mangling of the password stinks. It tells me that someone at Microsoft knew that this was a bad idea, but they couldn't be bothered. This was perhaps excusable twenty years ago, but not in 2022.

So here we are, with a world-class enterprise database that features an authentication method that is arguably less secure than HTTP basic authentication. Would you want to bet your life savings on that?

Come on, Microsoft. I'm not the only one who thinks this is broken. Database authentication is a useful feature, but can we please bring it into the 21st century?