SQL Server 20,052 Security Authentication Modes

(1) Windows Authentication Mode

Windows Authentication mode refers to a user who connects to SQL Server through a Windows user account, that is, the user's identity is verified by the Windows system. SQL Server uses the information in the Windows operating system to verify the account name and password. This is the default authentication mode and is much more secure than the hybrid authentication mode. Windows authentication uses the Kerberos security protocol, which provides password policy enforcement with strong password complexity validation, provides account lockout support, and supports password expiration.

Users and groups are maintained by Windows, so when a user makes a connection, SQL Server reads the membership information about the user in the group. In general, clients support trusted connections, and it is recommended to use Windows authentication. Using Windows authentication has the following features: 1) Windows Authentication mode is managed by Windows Admin login account, database administrator mainly uses this account, 2) Windows has powerful tools and techniques to manage user's login account, 3) can be in SQL You can use user groups by adding user groups to the server.

SQL Server supported logins can be found in system table syslogins or catalog view sys.syslogins.

(2) Mixed authentication mode (also known as SQL Server authentication mode)

Hybrid authentication mode allows users to connect using Windows identity and SQL Server identity. Users who connect through a Windows logon account can use Windows authentication for trusted connections. When a user makes a non-trusted connection using the specified login name and password, SQL Server detects whether the entered login and password are the same as recorded in the System syslogins table and authenticates accordingly. If the user's logon account does not exist, authentication fails. The user can only authenticate with SQL Server by providing the correct login name and password.

SQL Server authentication is provided to consider non-Windows client compatibility and backward compatibility, and early SQL Server applications may require the use of SQL Server logins and passwords. When the instance of SQL Server is running on Windows 98/windows Professional, Windows Authentication mode is not supported due to Windows 98/windows Professional. You must use Mixed mode. Non-Windows clients must also use SQL Server authentication.

The hybrid authentication mode has the following features: 1) Mixed mode allows non-Windows customers, Internet customers, and mixed customer groups to connect to SQL Server; 2) adds complete choice.

If you must select Mixed authentication mode and require SQL login to accommodate legacy applications, you must set strong passwords for all SQL accounts. This is especially important for accounts that belong to the sysadmin role, especially the SA account.

In simple terms, Windows login is logged in with Windows account. SQL Sever logins can be logged on with SQL Server or using Windows logon, which provides a variety of ways to sign in.

(GO) SQL Server 20,052 Security authentication Mode