SQL Authentication
SQL Authentication, also known as SQL Login, stores credentials (username and password) inside the SQL Server instance. It’s a database-managed authentication model, independent of Active Directory or Windows accounts.

Features

• Uses login name and password stored in SQL Server.
• Ideal for non-domain clients or cross-platform applications.
• Supports backward compatibility for legacy systems.

Advantages

• Works outside of domain networks.
• Simple to set up for service accounts or third-party tools.
• Enables easy app-level authentication control.

Disadvantages

• Passwords must be managed manually.
• No centralized policy enforcement (e.g., MFA).
• Slightly higher attack surface if passwords are weak.

Best Practices

• Use strong password policies and encrypt connections (TLS).
• Restrict login privileges.
• Periodically rotate credentials and audit logins.

Windows Authentication
Windows Authentication (also known as Integrated Security or Trusted Connection) uses the Windows or Active Directory identity of the user to authenticate.
It’s the most secure and preferred option for enterprise setups.

Features

• Uses Kerberos or NTLM protocols for verification.
• Credentials are not sent directly to SQL Server.
• Enables Single Sign-On (SSO) via Active Directory.

Advantages

• Centralized identity control via AD.
• Strong password and lockout policies automatically applied.
• Easy role-based access using AD groups.

Disadvantages

• Requires domain membership or trust relationships.
• Not ideal for external or Linux-based clients.

Best Practices

• Prefer Kerberos over NTLM for better security.
• Use AD groups for permission management.
• Audit AD memberships regularly.

Other Authentication Methods (Short Notes)

Summary Table