Recommendations and Guidelines on Configuring the Authentication Mode for SQL Server

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 R2 Datacenter

Summary


SQL Server has two authentication modes: Windows Authentication and Mixed Mode Authentication. In Windows Authentication mode, specific Windows user and groups are trusted to log in to SQL Server. Windows credentials are used in the process, either via NTLM or Kerberos.

It is a best practice to use only Windows logins whenever possible. Using Windows logins with SQL Server achieves single sign-on and simplifies login administration. Password management uses the ordinary Windows password policies and password change APIs. Users, groups, and passwords are managed by Windows system administrators; SQL Server database administrators are only concerned with which users and groups are allowed access to SQL Server and with authorization management. 

SQL logins should be confined to legacy applications, mostly in cases where the application is purchased from a third-party vendor and the authentication cannot be changed. Another use for SQL logins is with cross-platform client-server applications in which the non-Windows clients do not possess Windows logins. Although using SQL logins is discouraged, there are security improvements for SQL logins in SQL Server 2005 and later. 


More Information


For detailed information on advantages of using Windows Authentication when compared to Mixed Mode Authentication, please refer to the Whitepaper
http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

Additional Resources

Choosing an Authentication Mode
How to: Change Server Authentication Mode

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated 
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)





Authentication Mode







The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect when an instance of SQL Server is configured for Mixed Mode Authentication. The current Authentication Mode can be identified by selecting the Security page under the Server Properties window from the SQL Server Management Studio.

If you run the BPA tool and encounter a Warning with the title ofEngine - Authentication Mode, then your SQL Server is configured to run under Mixed Mode Authentication.
SQL Server 2008
SQL Server 2008 R2







SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)






Authentication Mode







The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect when an instance of SQL 
Server is configured for Mixed Mode Authentication. The current Authentication Mode can be identified by selecting the Security page under the Server Properties window from the SQL Server Management Studio.

If you run the BPA tool and encounter a Warning with the title of Engine - Authentication Mode, then your SQL Server is configured to run under Mixed Mode Authentication.
SQL Server 2012