Defaults surround us in SQL Server.  This is both a good thing and a bad thing.  Part of the SQL Server installation process is the choice of mixed mode security.  Mixed mode in SQL Server means both SQL Authentication and Windows Authentication can be used.  By default, SQL Server has Windows Authentication selected.  Changing this default to mixed mode should be taken seriously.

Mixed Mode vs. Windows Authentication

When operating in Mixed Mode, both Windows Authentication and SQL Authentication can be utilized for connections to SQL Server.  Sometimes the choice is not up to us on using one or the other.  Applications that are built to use SQL Authentication force mixed mode over the Microsoft recommended, Windows Authentication.  Windows Authentication is deemed more secure due to no password validation at the SQL Server level.  This is all handled in Windows and the principal token.  With SQL Authentication the passwords are validated and held on SQL Server.

Why would I ever use SQL Authentication?

Controlling security based on SQL Authentication isn’t a bad thing.  The loss of the domain and failures to login by means of Windows Authentication alone can push to enabling mixed mode.  One tip that should be noted with configuring mixed mode security: sa is a known system administrator account on SQL Server and is commonly part of malicious attacks.  Leaving this account enabled is not recommended.  To read more on enabling or disabling sa, To SA or not to SA. 

I didn’t enable mixed mode and need it now

Luckily, if mixed mode was not selected and the need for SQL Authentication arises, it is a configuration change that can be performed without a major effect on SQL Server.  Restarting SQL Server is not required for the change and the user activities are not directly impacted at the time the change is made.

To change from Windows Authentication to Mixed Mode, use SSMS under the Server Properties and Security page.  Change Server Authentication as needed.

Resources

Change Server Authentication Mode

Choosing an Authentication Mode

Security Considerations SQL Server

SQL Server 2005 (applicable for 2008) Best Practices