Choosing a SQL Server Authentication Mode

• Microsoft SQL Server offers administrators two choices of performing user authentication: Windows authentication mode and mixed authentication mode. Making the proper choice affects both the security and maintenance of your organization’s databases
• SQL Server Authentication ModesSQL Server 2008 offers two authentication mode options:
• Windows authentication mode requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.
• Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server.
• Selecting an Authentication Mode Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight. For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! In conclusion, I suggest that you heed Microsoft’s advice and implement Windows authentication mode for your SQL Server databases whenever possible.