Choosing a SQL Server Authentication Mode

Microsoft SQL Server 2008 offers administrators two choices of performing user authentication: Windows authentication mode and mixed authentication mode. Making the proper choice affects both the security and maintenance of your organization’s databases.

Authentication Basics

Authentication is the process of confirming a user or computer’s identity. The process normally consists of four steps:

1. The user makes a claim of identity, usually by providing a username. For example, I might make this claim by telling a database that my username is “mchapple”.
2. The system challenges the user to prove his or her identity. The most common challenge is a request for a password.
3. The user responds to the challenge by providing the requested proof. In this example, I would provide the database with my password
4. The system verifies that the user has provided acceptable proof by, for example, checking the password against a local password database or using a centralized authentication server

For our discussion of SQL Server authentication modes, the critical point is in the fourth step above: when the system verifies the user’s proof of identity. The choice of an authentication mode determines where SQL Server goes to verify the user’s password.

SQL Server Authentication Modes

SQL Server 2008 offers two authentication mode options:

• Windows authentication mode requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.
• Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server.

Selecting an Authentication Mode

Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight.

For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work!

In conclusion, I suggest that you heed Microsoft’s advice and implement Windows authentication mode for your SQL Server databases whenever possible.