Questions on Choosing an Authentication Mode

When you install SQL Server 2005, the database engine can be set to any of two types of Authentication modes, Windows Authentication mode and Mixed mode.

Windows Authentication Mode:

• It is default authentication mode.
• It provides high level of security.
• It mandates that only users who are previously authenticated to windows OS can connect to SQL Server.

Mixed Authentication Mode:

• Client applications submits user name and password to connect to SQL Server.
• When using 'Mixed mode' you can use either 'Windows authentication' or 'SQL Server authentication' to connect to SQL Server.
• Non-windows users (unix, macintosh) use this mode.

If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to Mixed mode, the sa login remains disabled. To enable the sa login:
Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '';
GO


Adhering to Password Policies:
To ensure SQL login security, SQL Server 2005 lets you to use AD's password policy and password complexity. You can do the following.

• You can force password policies.
• You can use password complexity.
• You can force password expiration.
• You can force users to change their password at next logon.

Passwords entered on the Authentication page must meet strong password policy requirements. Strong passwords are not readily guessed by any one, and are not easily hacked by using a computer program.

Follow these guidelines when you set passwords:

1. Strong passwords cannot use prohibited conditions or terms, including:

• A blank or NULL condition
• "Password"
• "Admin"
• "Administrator"
• "sa"
• "sysadmin"

2. A strong password cannot use the following terms associated with the installation computer:

• The name of the user currently logged onto the computer.
• The computer name.

3. A strong password must be at least six characters in length and satisfy at least three of the following four criteria:

• It must contain uppercase letters.
• It must contain lowercase letters.
• It must contain numbers.
• It must contain non-alphanumeric characters – e.g., #, %, or ^.

Questions:

1. You are tasked to implement a password policy for users connecting to your database. You must also provide access to non-windows clients. Which authentication modes you will use?
(a) Windows Authentication mode.
(b) Mixed Authentication mode.

2. Which of the following are true when talking about SQL Server 2005 password policies?
(a) You can force password policies.
(b) You can use password complexity.
(c) You can force password expiration.
(d) You can force users to change their password at next logon.

Show Answers:

1. b
2. a,b,c,d
Hide Answers (X).