Description of Identity Authentication in SQL Server

Description of Identity Authentication in SQL Server
Time: | read: 228

 

Microsoft SQL Server can work in one of two security (authentication) modes:

• Windows Authentication Mode (Windows Authentication)

  Windows Authentication mode allows you to connect to your account through Microsoft Windows NT 4.0 or Windows 2000.

• Hybrid mode (Windows Authentication and SQL Server Authentication)

  Hybrid mode allows you to connect to an SQL server instance using Windows authentication or SQL Server Authentication. In Windows Authentication mode or hybrid mode, users connected through Windows NT 4.0 or Windows 2000 user accounts can use trusted connections.

  SQL Server authentication is provided for backward compatibility. For example, if you create a Single Windows 2000 group and add all users to the group, you must grant the Windows 2000 group the permission to log on to SQL Server and access to any required databases.

Windows Authentication

When a user connects through a Windows NT 4.0 or Windows 2000 user account, SQL Server calls Windows NT 4.0 or Windows 2000 back to obtain information and reverifies the account name and password.

SQL Server controls logon access by using the security features of network users to achieve secure integration with Windows NT 4.0 or Windows 2000. Your network security features are created during network logon and verified by the Windows Domain Controller. When a network user tries to connect, SQL server uses the Windows-based function to determine the verified network user name. SQL Server then verifies whether this person is the one as it said, and then allows or rejects access based on the network user name, without requiring a separate login name and password.

Logon security integration runs on any supported network protocols in SQL Server.

If you attempt to connect to the SQL server instance by providing a blank Logon Name, SQL server uses Windows authentication. In addition, if a user attempts to use a specific login to connect to an SQL server instance configured as Windows Authentication mode, the login is ignored and Windows authentication is used.

 

Compared with SQL Server Authentication, Windows authentication has some advantages, mainly because of its integration with Windows NT 4.0 and Windows 2000 security systems. Windows NT 4.0 and Windows 2000 security systems provide more functions, such as security verification and password encryption, review, password expiration, minimum password length, and lock the account after multiple invalid login requests.

Because Windows NT 4.0 and Windows 2000 users and groups are only maintained by Windows NT 4.0 or Windows 2000, SQL Server reads information about the user's membership in the group. If you change the accessible permissions of connected users, the next time you connect to the SQL server instance or log on to Windows NT 4.0 or Windows 2000 (depending on the type of change ), these changes take effect.

The Windows Authentication mode is unavailable when the SQL server instance runs on Windows 98 or Microsoft Windows Millennium.

 

SQL Server Authentication

When a user uses the specified logon name and password to connect from a untrusted connection, SQL Server checks whether the SQL Server Logon account has been set, and whether the specified Password Matches the previously recorded password. Then, perform authentication on your own. If SQL server does not set a Logon account, authentication fails and the user receives an error message.

SQL Server authentication is provided for backward compatibility, because applications written for SQL Server 7.0 or earlier may require SQL Server login and password. In addition, when an SQL server instance runs on Windows 98, you must use SQL Server Authentication because Windows 98 does not support Windows Authentication mode. Therefore, SQL server uses hybrid mode when running on Windows 98 (but only supports SQL Server Authentication ).

Application developers and database users may prefer SQL Server Authentication because they are familiar with login and password functions. For clients other than Windows NT 4.0 and Windows 2000, SQL Server authentication is also required.

Note: When you use a named pipe to connect to an SQL server instance running on Windows NT 4.0 or Windows 2000, you must have the permission to connect to the Windows NT Named Pipe IPC // <computername>/IPC $. If you do not have the connection permission, you cannot use the named pipe to connect to the SQL server instance, unless the Windows NT 4.0 or Windows 2000 Guest account on your computer is enabled (disabled by default ), you can also grant the "access from the network" permission to the user account.