Windows Authentication mode and blending mode

One day, a June asked about the difference between Windows Authentication mode and mixed mode verification and the advantages and disadvantages, according to the security considerations, according to this article as a reference, study ~

During the installation process, you must select the authentication mode for the database engine. There are two types of 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.

Configure authentication Mode

If you select Mixed Mode authentication during Setup, you must provide a strong password and confirm the password for the built-in SQL Server system administrator account named SA. The SA account is connected by using SQL Server authentication.

If you select Windows Authentication during Setup, Setup creates the SA account for SQL Server authentication, but it disables the account. If you later change to Mixed mode authentication and you want to use the SA account, you must enable the account. You can configure any Windows or SQL Server account as a system administrator. Because the SA account is widely known and frequently targeted by malicious users, do not enable the account unless the application requires the 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.

To connect through Windows authentication

When a user connects through a Windows user account, SQL Server verifies the account name and password using the Windows principal tag in the operating system. That is, the identity of the user is confirmed by Windows. SQL Server does not require a password and does not perform authentication. Windows authentication is the default authentication mode and is more secure than SQL Server authentication. Windows authentication uses the Kerberos security protocol, provides password policy enforcement for strong password complexity validation, also provides account lockout support, and supports password expiration. Connections that are completed through Windows authentication are sometimes referred to as trusted connections because SQL Server trusts the credentials provided by Windows.

Security instructions

Use Windows authentication if possible.

Connecting through SQL Server authentication

When you use SQL Server authentication, the logins created in SQL Server are not based on Windows user accounts. User names 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 name and password) each time they connect. When you use SQL Server authentication, you must set a strong password for all SQL Server accounts.

There are three password policies available for the SQL Server login to choose to use.

User must change password at next logon

Require users to change their passwords the next time they connect. The ability to change passwords is provided by SQL Server Management Studio. If you use this option, third party software developers should provide this functionality.

Force Password Expiration

Enforces the maximum password age policy for the computer for the SQL Server login name.

Enforcing password policies

Enforces the Windows password policy for the computer for the SQL Server logon name. This includes password length and password complexity. This feature needs to be implemented through the NetValidatePasswordPolicy API, which is provided only in Windows Server 2003 and later.

Determine 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, then account Policy, and then click Password Policy.

The password policy will be as shown in the Results pane.

Disadvantages of SQL Server authentication

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

SQL Server authentication cannot use the Kerberos security protocol.

The SQL Server logon name cannot use other password policies provided by Windows.

Advantages of SQL Server authentication

Allows SQL Server to support legacy applications and Third-party-provided applications that require SQL Server authentication.

Allows SQL Server to support environments with mixed operating systems in which not all users are authenticated by Windows domains.

Allows a user to connect from an unknown or untrusted domain. For example, an application that an established customer uses a specified SQL Server login to connect to receive their order status.

Allows SQL Server to support web-based applications in which users can create their own identities.

Allows software developers to distribute applications by using a complex permission hierarchy that is based on a known preset SQL Server logon name.

Attention

Using SQL Server authentication does not limit local administrator rights on the computer on which SQL Server is installed.