What are the authentication modes in SQL Server?

SQL Server provides two security (authentication) modes.

  • Windows Authentication Mode (Windows Authentication):- Windows Authentication mode allows a user to connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account.
  • Mixed Mode (Windows Authentication and SQL Server Authentication):- Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication.

Windows Authentication Mode or Mixed Mode uses trusted connection to connect to SQL Server.

SQL Server Authentication is provided for backward compatibility. For example, if you create a single Windows 2000 group and add users to that group you will need to grant the Windows 2000 group login rights to SQL Server and access to databases.