Microsoft SQL Server 7.0 System Administration Training Kit

Microsoft SQL Server version 7 supports two authentication modes, Windows NT Authentication Mode and Mixed Mode. This lesson describes the process of authentication in each mode and the steps that you must take in implementing authentication.

NOTE

After this lesson, you will be able to

• Describe how users are authenticated when connecting to SQL Server
• Understand and choose between Windows NT Authentication Mode and Mixed Mode security
• Implement your chosen authentication mode

Estimated lesson time: 30 minutes

Authentication Processing

In any secure computer environment, users must be identified and validated. This process is known as authentication.

Authentication is similar to using a cash card at an ATM. Your card identifies you as an account holder at the bank. You then enter your PIN to prove that you are the rightful owner of the card.

Each SQL Server user is identified by a SQL Server login account, a Windows NT user account, or by membership in a Windows NT group. These are all known in SQL Server as logins. Validation that the user is allowed to use the login is performed either by SQL Server or by Windows NT.

How SQL Server Processes Logins That Windows NT Authenticates

The following steps (shown in Figure 11.1) describe how SQL Server processes logins that Windows NT authenticates:

1. When a user connects to a Windows NT Server, she enters her username and password.
2. The user's Windows NT security attributes are established and validated through a sophisticated password encryption mechanism.
3. When connecting, the client opens a trusted connection, and Windows NT_based facilities are used to pass the user's security attributes to SQL Server. SQL Server does not need to revalidate the password, because Windows NT has already validated it.
4. SQL Server checks the user's Windows NT security attributes to see if a Windows NT user account or a Windows NT group account defined there matches an entry in the SQL Server syslogins system table.
5. If one is found, the connection is accepted. The user's login account is the account that matched a syslogins entry—the Windows NT user account or one of the group accounts to which the user belongs.

Figure 11.1 Steps when Windows NT authenticates logins

Windows NT user and group account names are not actually listed in the security attributes; rather, a unique identifier represents each account. These unique identifiers are known as SIDs (Security Identifiers). SQL Server stores the SIDs of Windows NT users and groups that are granted access to the SQL Server in the syslogins table. These are compared to the SIDs listed in the user's security attributes. For this reason, SQL Server will not recognize users or groups that you have dropped and re-created in Windows NT. You must drop the group from SQL Server and add it again, because SQL Server uses the Windows NT SID to identify the group.

Note the following with respect to Windows NT authentication:

• If multiple SQL Server computers participate in a domain or a group of trusted domains, logging on to a single network domain is sufficient to enable access to all SQL Servers.
• Most of the graphical tools do not require that you enter a username and password when using NT authentication. The SQL Server command-line utilities support options that allow you to connect using a trusted connection.
• SQL Server running under Windows 95 or Windows 98 does not support Windows NT authentication mode.

How SQL Server Processes Logins That It Authenticates

The following steps (shown in Figure 11.2) describe how SQL Server processes logins that it authenticates:

1. When connecting, the client opens a non-trusted connection and passes a SQL Server login account and password.
2. SQL Server verifies that a login exists in the syslogins table and that the specified password matches the previously recorded password.
3. If SQL Server finds that the login and password are correct, the user is connected. If SQL Server does not have a matching login account or if the password is incorrect, authentication fails and the connection is refused.

Figure 11.2 SQL Server authentication login steps

Choosing an Authentication Mode

In Windows NT Authentication Mode, SQL Server accepts only logins authenticated by Windows NT. In Mixed Mode, SQL Server accepts both Windows NT_authenticated logins and logins that it authenticates. There is no mode that accepts only SQL Server_authenticated logins.

The security needs of your server and network environments will determine the authentication mode that you use for your SQL Server. You can use SQL Server Enterprise Manager to set the authentication mode of your server.

Advantages of Windows NT Authentication Mode

Use Windows NT Authentication Mode in network environments in which all clients support trusted connections. Windows NT authentication offers several advantages over SQL Server authentication, including

• Providing more features, such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after an invalid password.
• Enabling you to add groups of users to SQL Server by adding a single login account.
• Enabling users to connect to SQL Server quickly, without having to enter another login account and password.

Advantages of Mixed Mode

Use Mixed Mode to connect non-trusted or Internet clients only. Mixed Mode, and the SQL Server authentication mechanism in particular, offers the following advantages:

• Mixed Mode enables non_Windows NT clients, Internet clients, and mixed client groups to connect to SQL Server.
• SQL Server authentication allows you to add another layer of security over Windows NT.

Steps in Implementing One of the Authentication Modes

Perform the following tasks from a system administration account to implement your chosen authentication mode. For Windows NT Authentication Mode, perform steps 1 through 5; for Mixed Mode, perform steps 1 through 6:

1. Use the SQL Server Network Utility (found under Microsoft SQL Server 7.0 on the Start menu) to verify that a protocol that supports trusted connections (Multi-Protocol, Named Pipes, or TCP/IP Sockets) is in place for clients that will use Windows NT authentication (see Figure 11.3).

Figure 11.3 The SQL Server Network Utility

2. Right-click your server in the SQL Server Enterprise Manager and select Properties to open the SQL Server Properties dialog box. Select the Security tab (see Figure 11.4). Set the login security mode of SQL Server to SQL Server And Windows NT (Mixed Mode) or to Windows NT Only (Windows NT Authentication Mode).

Figure 11.4 SQL Server properties and security

3. Stop and then restart the MSSQLServer service for the security option to take effect.
4. Create the Windows NT groups and users that are authorized to connect to SQL Server over trusted connections. If you do not have permission to administer Windows NT groups and users, have a Windows NT administrator perform this task for you.
5. Use SQL Server Enterprise Manager or sp_grantlogin to grant Windows NT groups and users access to SQL Server.
6. To allow access to the server for those users who are not connecting to the server over trusted connections, use SQL Server Enterprise Manager or sp_addlogin to create a SQL Server login for each user.

• To verify which authentication mode is being used

1. Right click on your server in the SQL Server Enterprise Manager, and select Properties from the context menu.
2. Click on the Security tab.
3. Confirm that the Authentication is set to the authentication mode you set in step 2 of the previous exercise.

Lesson Summary

In this lesson you learned about the two authentication modes supported by SQL Server: Windows NT Authentication Mode and Mixed Mode. You can now describe the process of authentication in each mode and the steps that you must take to implement authentication. The two modes allow you maximum flexibility in choosing the best security architecture for your organization.