Determining Which Authentication Mode to Utilize

Windows Authentication works best if the SQL Server will be accessed from within the organization and all user accounts needing access reside in Active Directory. For example, Windows Authentication can be used when deploying SharePoint if both the SharePoint and SQL Server reside in the same domain or in separate domains that are trusted. On the other hand, SQL Server mixed-mode authentication works best if users or applications require access to SQL Server and are not associated with the domain that SQL Server resides in. For example, SQL Server authentication should be leveraged if the SharePoint server is not in the same domain as the SQL Server and a trust does not exist between the two environments.

Even though SQL Server now has the ability to enforce policies such as SQL Server account password complexity, password expiration, and account lockouts, Windows Authentication mode is still the recommended alternative for controlling access to SQL Server. The added advantage of Windows Authentication is that Active Directory provides an additional level of protection with the Kerberos protocol and administration is reduced by leveraging Active Directory groups when providing access to SQL Server.