Share via

Authentication Mode (SQL Server Express)

  • Article

Use the Authentication Mode page of the Microsoft SQL Server Installation Wizard to choose the form of authentication used to validate connections to this instance of SQL Server Express. If you select Mixed Mode, you must enter and confirm the SQL Server system administrator (sa) password. After a device establishes a successful connection to SQL Server, the security mechanism is the same for both Windows Authentication and Mixed Mode.

Options

Option

Description

Windows Authentication Mode

When a user connects through a Microsoft Windows user account, SQL Server validates the account name and password using information in the Windows operating system. This is the default authentication mode, and is much more secure than Mixed Mode. Windows Authentication uses Kerberos security protocol, provides password policy enforcement in terms of complexity validation for strong passwords, provides support for account lockout, and supports password expiration.

ms143705.security(en-US,SQL.90).gifSecurity Note:

When possible, use Windows Authentication.

If you select Windows Authentication, Setup creates an sa account that is disabled by default. To use Mixed Mode Authentication and activate the sa account after Setup completes, see the How to: Change Server Authentication Mode and ALTER LOGIN (Transact-SQL) topics in SQL Server 2005 Books Online.

Important   Do not use a blank password. Use a strong password.

Mixed Mode (Windows Authentication and SQL Server Authentication)

Allows users to connect by using Windows Authentication and SQL Server Authentication. Users who connect through a Windows user account can use trusted connections that are validated by Windows.

If you choose Mixed Mode Authentication you must set strong passwords for all SQL Server logins. This is of particular importance for sa, and for logins that are members of the sysadmin fixed server role.

Note   SQL Server Authentication is provided for backward compatibility only. When possible, use Windows Authentication.

Specify the sa logon password below

Enter and confirm the system administrator (sa) password when you choose Mixed Mode authentication. Setting strong passwords is essential to the security of your system. Never set a blank or weak sa password.

Note   SQL Server passwords can contain from 1 to 128 characters, including any combination of letters, symbols, and numbers. If you choose Mixed Mode authentication, you must enter a strong sa password before you can continue to the next page of the Installation Wizard

Strong Password Guidelines

Strong passwords are not readily guessed by a person, 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:
    1. A blank or NULL condition
    2. "Password"
    3. "Admin"
    4. "Administrator"
    5. "sa"
    6. "sysadmin"
  2. A strong password cannot use the following terms associated with the installation computer:
    1. The name of the user currently logged onto the computer.
    2. 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:
    1. It must contain uppercase letters.
    2. It must contain lowercase letters.
    3. It must contain numbers.
    4. It must contain non-alphanumeric characters – e.g., #, %, or ^.

Passwords entered on the Authentication page must meet strong password policy requirements. If you have any automation that uses SQL Server Authentication, make sure that the password meets strong password policy requirements.

See Also

Other Resources

SQL Server 2005 Books Online Download Center

Help and Information

Getting SQL Server 2005 Assistance