Authentication Modes in SQL Server

SQL Server supports two types of authentication modes: Windows authentication and Mixed Modes.

The default authentication mode for SQL Server is Windows authentication, which mandates that the only users who can connect to the SQL Server instance are users who previously authenticated to the Window OS.

The alternative authentication, Mixed Mode, means that SQL Server support users who authenticate using either of two authentication methods. The first method is to rely on the Window OS to authenticate users. The second method is for SQL Server to authenticate users directly based on the submission of a user name and password to SQL Server by the client application that is attempting to gain access.

For most SQL Server environments, you should use Windows authentication mode because it provides the highest level of security. However, legacy applications often do not user accounts, so they must forward a user name and password to connect to SQL Server. In this case, Mixed Mode authentication lets the legacy application use SQL Server login to access the instance or if the database environment includes clients on OSs that cannot   authenticate with the Windows OS such as Macintosh or UNIX clients. DBA using Mixed Mode should be aware that using this authentication mode requires the creation of a strong password for the SA accounts. This account is highly privileged of SQL Server and its password critical factor when deciding to use Mixed Mode.