Lesson 1: Understanding the Authentication Process

3 4

Before a user can perform any activities within a database or perform server-wide tasks, SQL Server 2000 must authenticate the user. In this lesson, you will learn about the two authentication mechanisms used by SQL Server 2000 for authentication. You will learn the appropriate use of each mode, including the security implications of allowing SQL Server logins. Finally, you will learn about passing user authentication information between SQL Server 2000 instances for distributed queries and file system access.

---

After this lesson, you will be able to

• Describe how SQL Server 2000 authenticates users
• Choose between SQL Server 2000 authentication modes
• Change SQL Server 2000 authentication modes
• Enable security account delegation between SQL Server 2000 instances

Estimated lesson time: 15 minutes

---

Understanding the SQL Server 2000 Authentication Process

Before a user can perform a task or access a database within SQL Server 2000, a database administrator must first create a login granting that user permission to access SQL Server 2000 (and then grant that login permissions within SQL Server 2000). A login is either linked to a specific Windows 2000 (or Windows NT 4.0) user or group, or to a security account created within SQL Server 2000 itself (a SQL Server login). Lesson 3 covers how to create logins using SQL Server 2000 security accounts and Windows 2000 users and groups. Chapter 11 covers granting and managing login permissions within SQL Server 2000.

When a user attempts to access a SQL Server 2000 instance, the user (either directly or through an application) requests a type of authentication and presents identification credentials. The user can request one of two types of authentication, Windows authentication or SQL Server authentication. SQL Server 2000 must verify that the user is a permitted user before allowing access. This means validating the presented credentials for the user.

Windows Authentication

If the user is already authenticated on the Windows domain as a valid Windows user, SQL Server 2000 can be requested to trust this authentication by the operating system and allow the user access to SQL Server 2000 based on those credentials. In this case, the user requesting access presents (this is invisible to the user) either a Kerberos ticket (Windows 2000) or access token (Windows NT 4.0) to SQL Server 2000 as verification of his or her identity. SQL Server 2000 verifies the authenticity of the Kerberos ticket or the access token and then compares the user with the list of Windows users and groups permitted (but not denied) access. Based on this information, SQL Server 2000 then either grants or denies access.

Note

---

You call a connection using Windows authentication a trusted connection.

SQL Server Authentication

If the user requesting access either has not been authenticated on the Windows domain or wants to connect using a SQL Server 2000 security account, the user can request that SQL Server 2000 directly authenticate the user based upon the submission of a user name and password (if SQL Server 2000 is configured to permit SQL Server authentication). If the user requests SQL Server authentication, SQL Server 2000 compares the user name submitted against the list of SQL Server 2000 security accounts. If SQL Server finds the submitted user name in the sysxlogins table, SQL Server 2000 then encrypts the submitted password and compares it with the encrypted password in this table. Based on this information, SQL Server 2000 then grants or denies access.

Note

---

The user name and password are submitted to SQL Server 2000 in clear text unless both the client and the server Net-Libraries are using SSL encryption for the entire session.

Comparing Windows Authentication and SQL Server Authentication

Table 10.1 compares the security capabilities of these two types of authentication.

Client Net-Libraries and Authentication

SQL Server 2000 clients use an enabled client Net-Library to communicate with a server Net-Library on a SQL Server 2000 instance. To support the desired network protocol, a matching pair of Net-Libraries must be active on the client and server computers. The TCP/IP Sockets and Named Pipes client Net-Libraries are the default client Net-Libraries for computers running Windows 4.0 or Windows 2000 and are used in that order. In most environments, you will not need to modify client Net-Library settings. However, if a user is having difficulty connecting to a SQL Server 2000 instance, verify the settings for the client Net-Libraries. On the client computer, use the SQL Server Client Network Utility to enable additional or different Net-Libraries, configure custom connection properties for each Net-Library, and specify the order in which the system will attempt to use each enabled Net-Library.

Table 10.1 Security Capabilities of Windows Authentication Versus SQL Server Authentication

Windows Authentication	SQL Server Authentication
When a user logs into the Windows domain, the user name and password are encrypted before being passed to the Windows domain controller.	The Windows operating system never authenticates the user.
When an authenticated user presents authentication credentials to a SQL Server 2000 instance, the Kerberos ticket or access token submitted is encrypted.	When a user presents authentication credentials to a SQL Server 2000 instance, the user name and password submitted are not encrypted (unless SSL is enabled for the entire session).
Windows 2000 and Windows NT 4.0 support password policies (such as enforcing complex passwords and password expirations).	SQL Server 2000 supports no password policies (passwords can be of any length or complexity, and they never expire).
Windows 2000 and Windows NT 4.0 supports account lockout policies (such as for multiple attempts using an incorrect password).	SQL Server 2000 supports no account lockout policy (a user can try an unlimited number of times until a valid name and password allow access).

You access the SQL Server Client Network Utility from the Microsoft SQL Server program group on the Start menu. See Figure 10.1.

Figure 10.1

Accessing the SQL Server Client Network Utility.

Note

---

You can force protocol encryption using SSL. If you select this check box in SQL Server Client Network Utility, the SQL Server 2000 instance you are connecting to must also be using encryption. Use the SQL Server Network Utility to force protocol encryption for the server Net-Libraries.

The Named Pipes and Multiprotocol Net-Libraries require an authenticated connection to the Windows domain before a client can connect to a SQL Server 2000 instance (using either Windows authentication or SQL Server authentication). This means that the user who is requesting authentication must be an authenticated user on the Windows domain. This is not a problem if you are using a Windows client and are logged on to the same domain (or a trusted domain) that contains the SQL Server 2000 computer on which SQL Server 2000 is running. However, if you are attempting to connect to a SQL Server 2000 instance from a computer that has not established a trusted connection to the domain, an attempted connection using either the Named Pipes or the Multiprotocol Net-Libraries will fail (however, TCP/IP sockets will succeed if valid credentials are presented).

Note

---

The simplest method for establishing an authenticated connection (other than logging on to the domain) is to connect to an existing share within the domain.

Neither the TCP/IP Sockets Net-Library nor any of the other Net-Libraries require prior authentication by the Windows domain. A user (or application) can attempt to connect to an instance of SQL Server 2000 using the TCP/IP network protocol and the TCP/IP Sockets Net-Library from any location provided that communication can be established with the SQL Server 2000 computer. For this reason, understanding how to secure your Windows domain as well as securing your SQL Server 2000 instance is extremely critical to protecting your data.

Selecting a SQL Server 2000 Authentication Mode

During installation, you selected an authentication mode for your SQL Server 2000 instance. The default mode is Windows Authentication Mode. You can also choose to operate a SQL Server 2000 instance in Mixed Mode. Selecting the appropriate authentication mode is critical to securing your SQL Server 2000 installation.

Note

---

An instance of SQL Server 2000 running on Windows Me or Windows 98 only supports SQL Server authentication because of the limitations of these two operating systems.

Windows Authentication Mode

When SQL Server 2000 is running in Windows Authentication Mode, a user can only connect to a SQL Server 2000 instance using Windows authentication (a trusted connection). This is the default security mode for SQL Server 2000. Windows 2000 (and Windows NT 4.0) provide a variety of methods to secure your Windows environment that are beyond the scope of this book, but are essential to securing your SQL Server 2000 installation. These methods include using account policies, group policies, proxy servers, firewalls, routers, and Internet Protocol Security (IPSec). Using these security mechanisms, requiring a Windows authenticated (trusted) connection provides greater security than using SQL Server 2000 for authentication of users.

Mixed Mode

When SQL Server 2000 is running in Mixed Mode, a user can connect to a SQL Server 2000 instance using either Windows authentication or SQL Server authentication. Using this mode is required for users connecting directly to SQL Server 2000 from Novell NetWare, Apple Macintosh, Banyan Vines, UNIX, and Linux clients. However, using Mixed Mode is inherently less secure than using Windows Authentication Mode, and you should only use it when absolutely necessary.

Switching Authentication Modes

After installation, you must use SQL Server Enterprise Manager to switch authentication modes. In the console tree, right-click the instance and then click Properties. On the Security tab of the SQL Server Properties dialog box, click either the SQL Server And Windows option button or the Windows Only option button to change the authentication mode, and then click OK. See Figure 10.2.

Figure 10.2

Switching authentication modes.

After you switch modes, you must stop and then restart the SQL Server service for this change to take effect. SQL Server Enterprise Manager will prompt you and ask if you want it to perform this task immediately. See Figure 10.3.

Figure 10.3

SQL Server Enterprise Manager prompt to stop and restart the SQL Server service.

Note

---

If the SQL Server Agent service is also running, SQL Server Enterprise Manager will prompt you to stop it as well. However, SQL Server Enterprise Manager will not restart the SQL Server Agent service. You will need to restart it manually.

Practice: Switching SQL Server 2000 Authentication Modes

In this practice, you switch SQL Server 2000 from Windows Authentication Mode to Mixed Mode.

To switch SQL Server 2000 authentication modes

1. In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container and then expand the SQL Server Group container.
2. Right-click the default instance and then click Properties.

   The SQL Server Properties (Configure) – SelfPacedCPU dialog box appears, with the General tab selected.

3. Click the Security tab.
4. Click the SQL Server And Windows option button and then click OK.

   A SQL Server Enterprise Manager – SelfPacedCPU dialog box appears asking if you want to stop and restart the server SelfPacedCPU.

5. Click the Yes button.

   A second SQL Server Enterprise Manager – SelfPacedCPU dialog box may appear asking if you want to stop MSSQLServer and all its dependent services.

6. Click the Yes button.

   After a few moments, the SQL Server service restarts.

Understanding Security Account Delegation

The SQL Server service has the ability to impersonate an authenticated user when accessing resources outside of the SQL Server 2000 instance (such as the file system or another server). This ability ensures that access to these resources is restricted or permitted based on the credentials of the authenticated user, rather than the credentials of the domain user account of the SQL Server service. For the SQL Server service to pass the credentials of a Windows authenticated user to another server, you must enable Windows 2000 security account delegation on both servers.

Security account delegation requires that all servers involved be running Windows 2000, with Kerberos support enabled, and be using the Active Directory directory service. Within Active Directory, the user who is attempting to use security account delegation must be trusted for delegation. You select this account option in the user's Properties dialog box, which you access with Active Directory Users And Computers. See Figure 10.4.

Figure 10.4

Enabling a computer to be trusted for delegation.

Also, the computer on which the SQL Server 2000 instance is running must be trusted for security account delegation. For a distributed query between two SQL Server 2000 instances on separate computers, each computer must be trusted. You select this option in the computer's Properties dialog box, accessed in Active Directory Users And Computers. See Figure 10.5.

Finally, to use security account delegation, the SQL Server 2000 instance must have a Service Principal Name (SPN). You establish an SPN for a SQL Server 2000 instance with the Setspn utility, which is available in the Windows 2000 Resource Kit. You can assign an SPN in two different ways. You can use the Setspn utility to have a permanent SPN assigned by a Windows 2000 domain administrator to the SQL Server service domain user account based on a port number. Multiple ports and multiple instances require multiple SPNs. To enable delegation, you must use the TCP/IP Net-Library rather than Named Pipes because the SPN targets a specified TCP/IP socket.

Figure 10.5

Property settings for trusted computer accounts.

 Setspn -a MSSQLSvc/SelfPacedCPU.SelfPacedSQL.msft:1433 sqlservice 

The preceding example adds an SPN on an instance of SQL Server 2000 named SelfPacedCPU.SelfPacedSQL.MSFT that is listening on port 1433 and using the SelfPacedSQL\SQLService domain user account.

You can also have a temporary SPN assigned by running the SQL Server 2000 service under the local system account. When you run the SQL Service service under the local system account, SQL Server will automatically register a temporary SPN at service startup. A temporary SPN expires when the SQL Server service shuts down. If you decide to change from a permanent SPN to a temporary one, you need to delete the previously registered SPN.

 Setspn -d MSSQLSvc/SelfPacedCPU.SelfPacedSQL.msft:1433 sqlservice 

The preceding example deletes an SPN on an instance of SQL Server 2000 named SelfPacedCPU.SelfPacedSQL.MSFT that is listening on port 1433 and using the SelfPacedSQL\SQLService domain user account.

Note

---

If you change the SQL Server service account, you will need to delete any existing SPNs and create new ones.

Lesson Summary

To access SQL Server 2000, a database administrator must either grant access to existing Windows users and groups, or create SQL Server security accounts. A user can specify a connection to SQL Server 2000 either using his or her existing Windows credentials or by specifying a SQL Server login ID and password. The latter method is less secure and is not enabled by default. You must specify Mixed Mode authentication to permit SQL Server logins. Finally, for Windows authentication credentials to be passed between servers, you must enable security account delegation in Windows 2000.