Just a quick post for my future reference on the differences between Trusted authentication and Mixed-mode Authentication used by SQL Server
- 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.
- 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.