Choosing a SQL Server Authentication Mode

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

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 tool dramatically reduces the chances of permissions errors. 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 connect from different non-trusted domains or when possibly less-secure internet applications are in use, such as ASP.net.