SQL Server supports two authentication mechanisms: Windows authentication and SQL Server (SQL) authentication.  With Windows authentication, SQL Server simply validates a user’s Windows identity with an identity management solution such as Active Directory.  With SQL authentication, SQL Server generates, stores, and manages instance-specific user name and password information.  While SQL Server can be configured to employ SQL authentication in addition to the Windows authentication default, it is strongly discouraged as SQL authentication is vulnerable to brute-force attacks. That’s not to say that Windows authentication is invulnerable to attack. In fact a new feature, Extended Protection, was introduced with SQL Server 2008 R2 to thwart one such attack.

Extended Protection

The man-in-the-middle (MITM) attack is executed by a malicious application which impersonates the SQL Server service to a client and the client to the service.  In doing so, the malicious application places itself in the middle of the communications channel between the client and server and from this vantage point can intercept messages transmitted between the two. (This attack is also referred to as authentication relay.)

To thwart this attack, SQL Server registers its identity as a Service Principle Name (SPN) with Active Directory (typically at the time of installation).  The client obtains the SPN as part of the connection process and validates this against the SPN held by SQL Server.  If the malicious application does not have access to the SPN, the connection is not completed.

This process is referred to as service binding and it protects against scenarios where the malicious application lures the client to it.  If the client voluntarily connects to the malicious application (usually due to spoofing), then channel binding can be employed to block the MITM attack.

With channel binding, the SQL Server service sends the connecting client the public key associated with a private key it maintains.  The client generates a value known as the Client Binding Token (CBT), encrypts it with the service’s public key, and transmits the encrypted CBT to SQL Server. Using its private key, SQL Server decrypts the CBT and then uses the CBT to encrypt the channel between it and the client.  Without the private key, the malicious application cannot decrypt the CBT and therefore cannot access the data passed between the client and server.

To turn on service binding, use the SQL Server Configuration Manager to set the Extended Protection property to either Allowed or Required.  (Allowed allows but does not require clients to use the Extended Protection features.  Required will prevent any clients from connecting unless they employ Extended Protection.) To turn on channel binding, set Extended Protection to either Allowed or Required as before but also set Force Encryption to On.

Please be aware that Extended Protection adds overhead to the authentication process and, if channel binding is employed, subsequent communications between client and server. Weigh the performance impact relative to the security benefits obtained through the feature before enabling the feature in a production environment.  In addition, please note Extended Protection works with specific operating systems and these must be configured to employ these mechanisms.  In addition, only SQL Server 2008 R2 and the associated Native (client) Provider are capable of working with these settings.  For more information on Extended Protection and its dependencies, please explore the information provided here and here.

Protecting SQL Authentication

As stated earlier, SQL authentication is susceptible to brute-force attacks and for this reason it is strongly recommended that only Windows authentication should be employed. When SQL authentication must be employed, barriers to brute-force attacks can be raised through effective password management and the disabling of well-known accounts.

To improve the security of passwords associated with SQL authenticated accounts, Windows password policies requiring the use of strong passwords and periodic password changes can be applied using the CHECK_POLICY = ON and CHECK_EXPIRATION = ON settings of the CREATE LOGIN and ALTER LOGIN statements.

For the SQL authenticated system administrator account (sa) found on every instance of SQL Server, consider applying a very, very strong password.  In addition, consider changing the account name and/or disabling it using the ALTER LOGIN statement.

Finally, avoid the re-use of SQL authenticated accounts between users, applications, and instances. Doing so creates locally well-known accounts and makes effective permissions management and monitoring very challenging.

A Note on Encryption during Authentication

In SQL Server 2000 and prior, data transmitted during authentication was unencrypted.  With Windows authentication, this wasn’t too big a deal but with SQL Server authenticated connection attempts, this meant user names and passwords were sent in plain text. 

With SQL Server 2005, SSL encryption during authentication was introduced.  This mechanism is used to protect all data transmitted during authentication, regardless of whether Windows or SQL authentication is employed. In addition, SSL encryption is applied regardless of whether SQL Server is configured for SSL encryption of all traffic or not. If SQL Server is configured for the SSL encryption of all traffic, the certificate registered with SQL Server is employed.  If no certificate is registered for this purpose, SQL Server uses a self-signed certificate which is less secure but at a minimum raises the level of sophistication required by an attacker to intercept data during this stage.

For more information on encryption during authentication, check out the appropriate section of this white paper.