SQL Server 2005: How Secure is SQL Server Authentication?

Question

If you use SQL Server Authentication (2005), are the login details sent in clear text over the wire?

Answer 1

As secure as you want to make it...

you can configure SSL fairly easily, and if you don't have a trusted cert, if you force encryption, SQL Server can create/issue it's own self signed cert for your use...from this write-up

Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security but it does not provide protection against identity spoofing by the server. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the self-signed certificate

Answer 2

Whether or not the login credentials are encrypted depends on the encryption capability/configuration of the client and server.

At the protocol level, completely unencrypted SQL logins are allowed, though my guess is that these are rare because I suspect most modern database drivers do not support them.

Details

Clients communicate with Microsoft SQL Server using the Tabular Data Stream (TDS) protocol.

Shortly after a client opens a TDS connection to the server, it informs the server of its encryption capability. The server compares this announcement with its own configuration/capability to determine the encryption state for the connection.

In a nutshell, the encryption state is determined as follows:

• If client or server announces that they do not support encryption and the other side does not require encryption, the entire connection—including login—will be unencrypted.
• If both client and server announce that they support encryption but do not require it, just the first TDS packet of the login request will be encrypted. The remainder of the connection, including any additional login request packets, will be unencrypted. A properly-designed database driver will ensure that the SQL authentication password is placed in first login packet, but this isn't required at the protocol level.
• If either client or server announces that they require encryption, the entire connection will be encrypted (except for a small amount of preliminary data) unless the other side does not support encryption. In that case, the connection will be terminated.

The only way to ensure that login requests are always encrypted is to set the 'require encryption' option on either client or server. There’s no option to disallow completely unencrypted connections without requiring full encryption.

Regardless of whether or not the login or connection is encrypted, the SQL authentication password is always obfuscated but the scrambling is easily reversible.

Further Reading:

• Technical details on connection encryption states - MS-TDS 2.2.6.5 PRELOGIN (under heading Encryption)
• Password obfuscation formula - MS-TDS 2.2.6.4 LOGIN7 (see last paragraph)
• Slightly more in-depth write-up on the topic - SQL Passwords: Encrypted Between Client and Server? (disclaimer: this is a post on my blog)