Microsoft SQL Server 2012 Bible

Differences Between SQL and Windows Authentication

Windows authentication is the recommended authentication method for SQL Server because it is superior to Mixed mode because the user does not need to learn yet another password, and because it leverages the security design of the network.

Using Windows authentication means that users must exist as Windows users to be recognized by SQL Server. The Windows security identifier (SID) is passed from Windows to SQL Server.

Windows authentication is robust in that it authenticates not only Windows users, but also users within Windows user groups.

When a Windows group is accepted as a SQL Server login, any Windows user who is a member of the group can be authenticated by SQL Server. Access, roles, and permissions can be assigned for the Windows group, and they apply to any Windows user in the group.

Best Practice

If the Windows users are already organized into groups by function and security level, using those groups as SQL Server users provides consistency and reduces administrative overhead.

SQL authentication is available only for backward compatibility and should be used only if legacy applications require it or when deploying SQL Server in non-Windows domain environments.