At present, SQL Server 2008 continues to support two modes for validating connections and authenticating access to database resources: (Windows Authentication Mode) and (SQL Server and Windows Authentication Mode) also known as "Mixed Mode". Both of these authentication methods provide access to SQL Server 2008 and its resources. Lets first examine the differences between the two authentication modes.
Windows Authentication Mode
Windows Authentication Mode is the default and recommended authentication mode. It tactfully leverages Active Directory user accounts or groups when granting access to SQL Server. In this mode, Database Administrators are given the opportunity to grant domain or local server users access to the database server without creating and managing a separate SQL Server account. Also worth mentioning, when using Windows Authentication mode, user accounts are subject to enterprise wide policies enforced by the Active Directory domain such as complex passwords, password history, account lockouts, minimum password length, maximum password length and the Kerberos protocol. These enhanced and well defined policies are always a plus to have in place.
SQL Server and Windows Authentication (Mixed) Mode
SQL Server and Windows Authentication Mode uses either Active Directory user accounts or SQL Server accounts when validating access to SQL Server. SQL Server 2005 introduced a means to enforce password and lockout policies for SQL Server login accounts when using SQL Server Authentication. SQL Server 2008 continues to do so. The SQL Server polices that can be enforced include password complexity, password expiration, and account lockouts. This functionality was not available in SQL Server 2000 and was a major security concern for most organizations and Database Administrators. Essentially, this security concern played a role in helping define Windows Authentication as the recommended practice for managing authentication in the past. Today, SQL Server and Windows Authentication Mode may be able to successfully compete with Windows Authentication mode.
Which Mode should be Used to Harden Authentication?
Once the Database Administers are aware of the authentication methods, the next step is choosing one to manage SQL Server security. Although, SQL Server 2008 now has the ability to enforce policies, Windows Authentication Mode is still the recommended alternative for controlling access to SQL Server because this mode carries added advantages; Active Directory provides an additional level of protection with the Kerberos protocol. As a result, the authentication mechanism is more mature, robust and administration can be reduced by leveraging Active Directory groups for role based access to SQL Server.
Nonetheless, this mode is not practical for everything out there. Mixed Authentication is still required if there is a need to support legacy applications or clients coming in from a platform other than windows and there exist a need for separation of duties. To summarize it is common to find organizations where the SQL Server and Windows team do not trust one another. Therefore, a clear separation of duties are required as SQL Server accounts are not managed via Active Directory.
Using Windows authentication is a more secure choice, however, if Mixed Mode authentication is required then make sure to leverage complex passwords and the SQL Server 2008 password and lockout policies to further bolster security.
Ross Mistry, SQL Server MVP, MCTS, MCDBA, MCSE, is a principal consultant and partner with Convergent Computing. As a lead architect, Ross focuses on designing and implementing SQL Server, Windows Server, Active Directory, and Exchange solutions for Fortune 500 organizations with a global presence. His specialties include high availability, migrations, security, consolidation and virtualization. Ross has also been involved with many bestselling books, his latest titles authored include SQL Server 2008 Management and Administration and Windows Server 2008 Unleashed. As a SQL Server MVP, Ross assists the global community by freely sharing his knowledge of SQL Server via posting for newsgroups, blogs and speaking engagements around the world. He frequently speaks at Microsoft, SQL Server North American PASS, SQL Server Europe PASS and Dev Connections.
SQL Server 2008 Management and Administration was selected as the January, 2009, book giveaway on Microsoft Subnet. Each month, Microsoft Subnet and Pearson Education gives away 15 copies of a hot Microsoft title. Click here to read a chapter excerpt. Click here to enter the monthly book giveaway contest.