Before we go into SQL Server 2008 Authentication modes, it is better to have quick look at Active Directory Services.
Active Directory (AD) is a directory service created by Microsoft for Windows domain networks. It is included in most Windows Server operating systems. Server computers on which Active Directory is running are called domain controllers. There can be more than one Domain Controllers in a Organization which are replicated internally. Active Directory serves as a central location for network administration and security. It is responsible for authenticating and authorizing all users and computers within a network of Windows domain type, assigning and enforcing security policies for all computers in a network and installing or updating software on network computers.
Authentication/Logging in: Say if a domain is created with name Development and user Will.Smith is created on it. When he tries to connect to any computer, he is checked to see if he is given privileges to logon on that computer through AD Service. The account he will use DevelopmentWill.Smith with his password.
This windows account is used in Below SQL Server Windows Authentication
SQL Server Authentication Modes:
During SQL Server setup, one can chose an authentication mode for the Database Engine.
There are two possible modes:
1.Windows Authentication mode: Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication.
2.Mixed Authentication mode: Mixed mode enables both Windows Authentication and SQL Server Authentication.
Note:Windows Authentication is always available and cannot be disabled.
Windows Authentication Mode: In this Authentication mode, user connects through a Windows user account which can be a local user account or domain account as explain earlier using AD Services, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation. Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.
SQL Server Authentication Mode: In This Authentication Mode, logins are created in SQL Server that are not based on Windows user accounts. Both the user name and the password are created by using SQL Server and stored in SQL Server. Users connecting using SQL Server Authentication must provide their credentials (login and password) every time that they connect. When using SQL Server Authentication, you must set strong passwords for all SQL Server accounts.
Three optional password policies are available for SQL Server logins.
•User must change password at next login: Requires the user to change the password the next time that the user connects. The ability to change the password is provided by SQL Server Management Studio.
•Enforce password expiration: The maximum password age policy of the computer is enforced for SQL Server logins.
•Enforce password policy: The Windows password policies of the computer are enforced for SQL Server logins. This includes password length and complexity. This functionality depends on the NetValidatePasswordPolicy API, which is only available in Windows Server 2003 and later versions.
To determine the password policies of the local computer
1. On the Start menu, click Run-> type secpol.msc and then click OK->In the Local Security Settings application->expand Security Settings -> expand Account Policies -> and then click Password Policy