The difference between Windows authentication mode and mixed mode

One day, A asked about the difference and advantages of Windows authentication mode and mixed mode verification. According to security considerations, according to this article as a reference, learn ~

During the installation process, the authentication mode must be selected for the database engine. There are two modes to choose from: Windows authentication mode and mixed mode. Windows authentication mode enables Windows authentication and disables SQL Server authentication. Mixed mode enables both Windows authentication and SQL Server authentication. Windows authentication is always available and cannot be disabled.

Configuring authentication mode

If you choose mixed mode authentication during installation, you must provide a strong password for the built-in SQL Server system administrator account named sa and confirm the password. The sa account connects by using SQL Server authentication.

If you choose Windows authentication during the installation process, the installer creates a sa account for SQL Server authentication, but disables the account. If you change to mixed mode authentication later and want to use the sa account, you must enable it. You can configure any Windows or SQL Server account as a system administrator. Because the sa account is widely known and often targets malicious users, do not enable the account unless the application requires a sa account. Do not set a blank or weak password for the sa account. To change from Windows Authentication Mode to Mixed Mode Authentication and use SQL Server Authentication, see Changing Server Authentication Mode.

Connecting with Windows Authentication

When a user connects through a Windows user account, SQL Server uses the Windows principal token in the operating system to verify the account name and password. In other words, the user identity is confirmed by Windows. SQL Server does not require a password or authentication. Windows authentication is the default authentication mode and is more secure than SQL Server authentication. Windows authentication uses the Kerberos security protocol to provide password policy enforcement for strong password complexity verification, account lockout support, and password expiration. Connections completed through Windows authentication are sometimes referred to as trusted connections because SQL Server trusts the credentials provided by Windows.

Security Instructions

Please use Windows Authentication whenever possible.

Connecting with SQL Server Authentication

When using SQL Server authentication, logins created in SQL Server are not based on Windows user accounts. Usernames and passwords are created and stored in SQL Server by using SQL Server. Users who connect through SQL Server authentication must provide their credentials (login and password) each time they connect. When using SQL Server authentication, you must set a strong password for all SQL Server accounts.

There are three password policies available for SQL Server logins to choose from.

The user must change the password the next time they log in.

Ask the user to change the password the next time they connect. The ability to change your password is provided by SQL Server Management Studio. If you use this option, third-party software developers should provide this feature.

Force Password Expiration

Enforces the computer's maximum password age policy for SQL Server logins.

Enforce password policy

Enforce the computer's Windows password policy for SQL Server logins. This includes password length and password complexity. This feature needs to be implemented through the NetValidatePasswordPolicy API, which is only available in Windows Server 2003 and later.

Determining the password policy for the local computer

On the "Start" menu, click "Run".

in the "Run" dialog box , type secpol.msc, and then click "OK".

In the "Local Security Settings" application, expand "Security Settings", "Account Policies", and then Click the "Password Policy".

password policy as shown in the results pane.

Disadvantages of SQL Server Authentication

If the user is a Windows domain user with a Windows login and password, you must also provide another (SQL Server) login and password for the connection. Keeping in mind multiple logins and passwords is difficult for many users. It is also annoying to provide SQL Server credentials each time you connect to the database.

SQL Server authentication cannot use the Kerberos security protocol.

SQL Server logins cannot use other password policies provided by Windows.

Advantages of SQL Server Authentication

Allows SQL Server to support legacy applications and applications provided by third parties that require SQL Server authentication.

Allows SQL Server to support environments with a mixed operating system in which not all users are authenticated by the Windows domain.

Allow users to connect from unknown or untrusted domains. For example, an application that a given customer connects using the specified SQL Server login name to receive the status of their order.

Allow SQL Server to support web-based applications where users can create their own logos.

Allows software developers to distribute applications by using a complex privilege hierarchy based on known default SQL Server logins.

Note

Using SQL Server Authentication does not restrict local administrator rights on the computer where SQL Server is installed.