Microsoft SQL Server 2012 Bible

SQL Authentication

SQL Server authentication enables users to specify a login name and password to connect to a SQL Server database. The login name and password are created, stored, and managed in SQL Server.

When an instance is configured for SQL Authentication mode, SQL authentication is enabled alongside with Windows authentication. Both Windows and SQL logins are supported.

SQL Authentication mode enables the default sa account. It is important to assign a strong password to the sa account.

Best Practice

If no requirement exists for the sa account to be active, you should always assign a strong password and disable it to prevent malicious attacks that target the sa account.

SQL authentication has several advantages:

• Backward compatibility for applications that require SQL logins and passwords
• Support for environments with mixed operating systems, where not all users are authenticated by a Windows domain
• Ability to deploy SQL Server databases as part of applications that require preset SQL Server logins

SQL authentication has several disadvantages:

• Increased surface area, making it more vulnerable to attacks and exploits
• Additional login name and password required for users to remember
• Limited amount of available password policies
• Additional overhead maintaining and synchronizing SQL logins and passwords across multiple SQL Servers