Skip to content

Latest commit

 

History

History
104 lines (66 loc) · 8.98 KB

choose-an-authentication-mode.md

File metadata and controls

104 lines (66 loc) · 8.98 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords
Choose an authentication mode
Choose between Windows Authentication mode and mixed mode authentication for the SQL Server Database Engine at setup time.
VanMSFT
vanto
09/12/2024
sql
security
how-to
sql13.ins.instwizard.authenticationmode.f1
sql13.swb.passwordexpired.f1
sa account
authentication modes
trusted connection
SQL Server Installation Wizard, Authentication Mode page
choose authentication mode
authentication [SQL Server], choosing a mode
Windows authentication [SQL Server]
mixed mode authentication
mixed authentication mode
SQL authentication mode
Password Expired dialog box

Choose an authentication mode

[!INCLUDE SQL Server]

During setup, you must select an authentication mode for the [!INCLUDE ssDE]. There are two possible modes: Windows Authentication mode and mixed mode. Windows Authentication mode enables Windows Authentication and disables [!INCLUDE ssNoVersion] Authentication. Mixed mode enables both Windows Authentication and [!INCLUDE ssNoVersion] Authentication. Windows Authentication is always available and can't be disabled.

Configuring the authentication mode

If you select Mixed Mode Authentication (SQL Server and Windows Authentication mode) during setup, you must provide and then confirm a strong password for the built-in [!INCLUDE ssNoVersion] system administrator account named sa. The sa account connects by using [!INCLUDE ssNoVersion] Authentication.

If you select Windows Authentication during setup, the setup creates the sa account for [!INCLUDE ssNoVersion] authentication but it's disabled. If you later change to Mixed Mode Authentication and you want to use the sa account, you must enable the account. Any Windows or [!INCLUDE ssNoVersion] account can be configured as a system administrator. Because the sa account is well known and often targeted by malicious users, don't enable the sa account unless your application requires it. Never set a blank or weak password for the sa account. To change from Windows Authentication mode to Mixed Mode Authentication and use [!INCLUDE ssNoVersion] Authentication, see Change Server Authentication Mode.

Connecting through Windows Authentication

When a user connects through a Windows user account, [!INCLUDE ssNoVersion] 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. [!INCLUDE ssNoVersion] doesn't ask for the password, and doesn't perform the identity validation. Windows Authentication is the default authentication mode, and is much more secure than [!INCLUDE ssNoVersion] Authentication. Windows Authentication uses New Technology LAN Manager (NTLM) or 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 [!INCLUDE ssNoVersion] trusts the credentials provided by Windows.

For information on configuring Kerberos, see Register a Service Principal Name for Kerberos connections.

By using Windows Authentication, Windows groups can be created at the domain level, and a login can be created on [!INCLUDE ssNoVersion] for the entire group. Managing access at the domain level can simplify account administration.

Important

[!INCLUDE ssNoteWinAuthentication]

Connecting through SQL Server Authentication

When using [!INCLUDE ssNoVersion] Authentication, logins are created in [!INCLUDE ssNoVersion] that aren't based on Windows user accounts. Both the user name and the password are created by using [!INCLUDE ssNoVersion] and stored in [!INCLUDE ssNoVersion]. Users connecting using [!INCLUDE ssNoVersion] Authentication must provide their credentials (login and password) every time that they connect. When using [!INCLUDE ssNoVersion] Authentication, you must set strong passwords for all [!INCLUDE ssNoVersion] accounts. For strong password guidelines, see Strong Passwords.

Three optional password policies are available for [!INCLUDE ssNoVersion] 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 [!INCLUDE ssManStudioFull]. Other company software developers should provide this feature if this option is used.

  • Enforce password expiration

    The maximum password age policy of the computer is enforced for [!INCLUDE ssNoVersion] logins.

  • Enforce password policy

    The Windows password policies of the computer are enforced for [!INCLUDE ssNoVersion] logins. This includes password length and complexity. This functionality depends on the NetValidatePasswordPolicy API, which is only available in [!INCLUDE winserver2003] and later versions.

To determine the password policies of the local computer

  1. On the Start menu, select Run.

  2. In the Run dialog box, type secpol.msc, and then select OK.

  3. In the Local Security Settings application, expand Security Settings, expand Account Policies, and then select Password Policy.

    The password policies are described in the results pane.

Disadvantages of SQL Server Authentication

  • If a user is a Windows domain user who has a login and password for Windows, they must still provide another ([!INCLUDE ssNoVersion]) login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide [!INCLUDE ssNoVersion] credentials every time the user connects to the database can be annoying.

  • [!INCLUDE ssNoVersion] Authentication can't use Kerberos security protocol.

  • Windows offers additional password policies that aren't available for [!INCLUDE ssNoVersion] logins.

  • The encrypted [!INCLUDE ssNoVersion] Authentication login password must be passed over the network at the time of the connection. Some applications that connect automatically will store the password at the client. These are additional attack points.

Advantages of SQL Server Authentication

  • Allows [!INCLUDE ssNoVersion] to support older applications and applications provided by third parties that require [!INCLUDE ssNoVersion] Authentication.

  • Allows [!INCLUDE ssNoVersion] to support environments with mixed operating systems, where all users aren't authenticated by a Windows domain.

  • Allows users to connect from unknown or untrusted domains. For instance, an application where established customers connect with assigned [!INCLUDE ssNoVersion] logins to receive the status of their orders.

  • Allows [!INCLUDE ssNoVersion] to support web-based applications where users create their own identities.

  • Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset [!INCLUDE ssNoVersion] logins.

    [!NOTE]
    Using [!INCLUDE ssNoVersion] Authentication does not limit the permissions of local administrators on the computer where [!INCLUDE ssNoVersion] is installed.

Related content