What is the difference between Windows Authentication and SQL Server Authentication?

Beginner

Answer

Windows Authentication uses the user's existing Windows domain credentials to connect to SQL Server, relying on the security already managed by the organization's Windows environment, while SQL Server Authentication uses a separate username and password stored and managed directly within SQL Server itself, which is useful for applications or users outside the Windows domain.

-- Windows Authentication login
CREATE LOGIN [DOMAIN\username] FROM WINDOWS;

-- SQL Server Authentication login
CREATE LOGIN SqlUser WITH PASSWORD = 'SecurePassword123!';

Real-world example A company primarily uses Windows Authentication for its internal staff since they are already part of the corporate domain, while using SQL Server Authentication for a specific external application that operates outside their Windows environment.

Common follow-ups: Which authentication method is generally considered more secure?;Can a SQL Server instance support both authentication methods at the same time?

Data Types & Schema Design;SQL Server Architecture & Editions

All 7 SQL Server Security & Permissions questions