Choosing a SQL Server Authentication Mode

Microsoft SQL Server 2016 offers administrators two choices for implementing how the system will authenticate users: Windows authentication mode or mixed authentication mode.

Windows authentication means that SQL Server validates a user's identity using only his Windows username and password. If the user has already been authenticated by the Windows system, SQL Server does not ask for a password.

Mixed mode means that SQL Server enables both Windows authentication and SQL Server authentication. SQL Server authentication creates user logins unrelated to Windows.

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

For our discussion of SQL Server authentication modes, the critical point is in the fourth step above: the point at which 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.

Let's explore these two modes a little further:

Windows authentication mode requires users to provide a valid Windows username and password to access the database server. If this mode is chosen, SQL Server disables the SQL Server-specific login functionality, and the user's identity is confirmed solely through his Windows account. This mode is sometimes referred to as integrated security because of SQL Server's dependence on Windows for authentication.

Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator creates and maintains within SQL Server. The user's username and password are both stored in SQL Server, and users must be re-authenticated each time they connect.

Microsoft’s best practice recommendation is to 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. Because the user's identity is confirmed by Windows, specific Windows user and group accounts can be configured to log in to SQL Server. Further, Windows authentication uses encryption to authenticate SQL Server users.

SQL Server authentication, on the other hand, allows usernames and passwords to be passed throughout the network, making them less secure. This mode can be a good choice, however, if users are connecting from different non-trusted domains or when possibly less secure Internet applications are in use, such as ASP.NET.
For example, consider the scenario in which 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 in which the DBA may know the password. That’s a lot of work!

In summary, the mode you choose affects both the level of security and the ease of maintenance of your organization’s databases.