We have two possible modes..
1. Windows Authentication Mode
2. Mixed Mode
1. Windows Authentication Mode:
- It enables Windows authentication mode and disables the SQL Server authentication.
- "sa" account for SQL Server authentication also created and disabled.
- When we connect through a Windows user account, SQL Server validates the account name using the Windows principal token in the operating system. The identity validated by the Windows Operating system.
- This is default authentication mode.
- The connection made using the Windows authentication is called as Trusted Connection. So, The Windows authentication is more secure than SQL Server authentication.
2. Mixed Mode:
- It enables both Windows and SQL Server authentication. But, Windows authentication can not be disabled.
- SQL Server authentication logins not based on Windows user account.
- SQL Server logins are stored / validated by the SQL Server.
Advantages of SQL Server authentication:
- It allows to connect with third party application / tools.
- It supports mixed operating system.
- It can be connected with untrusted/unknown domain.
Disadvantages of SQL Server authentication:
- If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect.