Windows (Trusted) Authentication Vs SQL (Mixed-Mode) Authentication

Just a quick post for my future reference on the differences between Trusted authentication and Mixed-mode Authentication used by SQL Server

Windows Authentication

  • When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows.
  • SQL Server does not ask for the password, and does not perform the identity validation.
  • Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication.
  • Windows Authentication
    • uses Kerberos security protocol,
    • provides password policy enforcement with regard to complexity validation for strong passwords,
    • provides support for account lockout,
    • and supports password expiration.
  • A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

SQL Authentication

  • When using SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts.
  • Both the user name and the password are created by using SQL Server and stored in SQL Server.
  • Users connecting using SQL Server Authentication must provide their credentials (login and password) every time that they connect.
  • When using SQL Server Authentication, you must set strong passwords for all SQL Server accounts.
  • Three optional password policies are available for SQL Server logins.
    • User must change password at next login
    • Enforce password expiration
    • Enforce password policy
  • SQL Server Authentication cannot use Kerberos security protocol.
  • Supports environments with mixed operating systems, where all users are not authenticated by a Windows domain.

Source: http://msdn.microsoft.com/en-us/library/ms144284.aspx