Microsoft® SQL Server™ can operate in one of two security (authentication) modes:
Mixed Mode allows users to connect using Windows NT Authentication or SQL Server Authentication. Users who connect through a Microsoft Windows NT® user account can make use of trusted connections (connections validated by Windows NT) in either Windows NT Authentication Mode or Mixed Mode. After successful connection to SQL Server, the security mechanism is the same for both modes.
Security systems based on SQL Server logins and passwords (SQL Server Authentication) may be easier to manage than security systems based on Windows NT user and group accounts, especially for databases that are not mission critical and applications without sensitive and confidential information. For example, a single SQL Server login and password can be created for all users of an application, rather than creating all the necessary Windows NT user and group accounts. This does, however, remove the ability to track and control the activities of individual users and is therefore not recommended.
When a user connects through a Windows NT user account, SQL Server verifies that the account name and password were validated when the user logged on to Windows NT or Microsoft® Windows® 95/98.
SQL Server achieves login security integration with Windows NT by using the security attributes of a network user to control login access. A user’s network security attributes are established at network login time and are validated through a sophisticated password encryption mechanism. When a network user tries to connect, SQL Server uses Windows NT-based facilities to determine the validated network username. SQL Server then permits or denies login access based on that network username alone, without requiring a separate login name and password.
Login security integration operates over network protocols that support authenticated connections between clients and servers. Such connections are referred to as trusted connections and are supported by SQL Server using any available network library.
Note If a user attempts to connect to SQL Server providing a blank login name, SQL Server automatically uses Windows NT Authentication. Additionally, if a user attempts to connect to a SQL Server configured for Windows NT Authentication Mode, using a specific login, the login is ignored and Windows NT Authentication is used.
Windows NT Authentication has certain benefits over SQL Server Authentication, primarily due to its integration with the Windows NT security system. Windows NT security provides more features, such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.
Because Windows NT users and groups are maintained only by Windows NT, SQL Server reads information about a user's membership in groups when the user connects. If changes are made to the accessibility rights of a connected user, the changes become effective the next time the user connects to SQL Server or logs on to Windows NT (depending on the type of change).
Note Windows NT Authentication Mode is not available when SQL Server is running on Microsoft Windows 95/98.
Security account delegation allows clients connected to one server using Windows NT Authentication to access another server by using a remote procedure call (RPC) with his or her security credentials, rather than the security credentials of the first server, or predefined security credentials set up on the first server. This allows more appropriate auditing because the user’s login, rather than a mapped login, is used for all connections, including RPCs. It also allows for a simpler security environment because server to server login mapping for users is not required. SQL Server can make use of security delegation, if available, when making RPCs, such as executing a distributed query using a SQL Server-linked server.
For more information about security account delegation, see your Windows NT documentation.
When a user connects with a specified login name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error.
SQL Server Authentication is provided for backward compatibility because applications written for earlier versions of SQL Server may require the use of SQL Server logins and passwords. Additionally, SQL Server Authentication is required when SQL Server is running on Windows 95/98 because Windows NT Authentication Mode is not supported on Windows 95/98. Therefore, SQL Server uses Mixed Mode when running on Windows 95/98 (but supports only SQL Server Authentication).
Application developers and database users may prefer SQL Server Authentication because they are familiar with the login and password functionality. SQL Server Authentication is also required for connections with internet and clients other than Windows NT clients.
Note When connecting to a SQL Server running on Windows NT using Named Pipes, the user must have permission to connect to the Windows NT Named Pipes IPC, \\<computername>\IPC$. If the user does not have permission to connect, it is not possible to connect to SQL Server using Named Pipes unless either the Windows NT guest account on the computer is enabled (disabled by default), or the permission “access this computer from the network” is granted to everyone.
To set up Windows NT Authentication Mode security
To set up Mixed Mode security