Lesson 2: Deciding SQL Server 2000 Setup Configuration Options | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

During the installation of SQL Server 2000, you must make a number of decisions in determining the configuration of your SQL Server 2000 installation. This lesson discusses each of these decisions, and provides the information required to understand and choose the configuration option most appropriate for your SQL Server 2000 environment.

After this lesson, you will be able to

Determine the appropriate user account for the SQL Server and SQL Server Agent services
Choose an authentication mode for SQL Server 2000
Determine when to modify default collation settings
Select the network libraries to use with SQL Server 2000
Decide on a client licensing mode

Estimated lesson time: 15 minutes

Determining the Appropriate User Account for the SQL Server and SQL Server Agent Services

Each SQL Server 2000 service runs in the security context of a user account. During SQL Server 2000 setup, you will be asked to specify the user account for the SQL Server and SQL Server Agent services. Two choices are available. You can select either the local system account or a domain user account. Generally, each of these services will use the same user account. Rarely can a case be made for using different accounts for each of these services.

The local system account is a Windows 2000 or Windows NT 4.0 operating system account with full administrator rights on the local computer. However, this account has no network access rights. This account is appropriate for use in many testing environments, as well as in production environments where SQL Server 2000 does not need to be integrated with other Microsoft server applications such as Microsoft Exchange Server or need to access any network resources, such as file shares. This account is used by default for the Distributed Transaction Coordinator service and the Microsoft Search service. Most Windows 2000 and Windows NT 4.0 operating system services also use this account.

However, in most client/SQL Server 2000 production environments, you will create and use a dedicated domain user account for the SQL Server and SQL Server Agent services. Selecting a dedicated domain user account allows these SQL Server 2000 services to communicate with other SQL Server installations, access Microsoft Exchange Server, and access network resources (such as file shares) on other computers in your domain environment. In addition, you should generally use the same domain user account for all SQL Server installations that will need to communicate with each other. This will simplify the administration of all SQL Server 2000 computers in your domain.

Using a dedicated user account means creating a domain user account that is used solely for the SQL Server and SQL Server Agent services. This domain user account should be configured with the Password Never Expires option. The domain user account you create needs certain special access rights on the local computer, but does not need to be a member of the Administrators local group and does not need to be a domain administrator. These special access rights include the right to log on as a service, the right to access and change the SQL Server folder, the right to access and change database files, and read and write access to certain keys in the Windows registry. The SQL Server 2000 Setup program grants these rights automatically to the domain user account you specify. Certain additional rights might be required for specific tasks, such as performing certain types of jobs or registering your SQL Server 2000 installation with Active Directory directory services. These rights, and the tasks that require them, will be covered in Chapters 12 and 13. You will need to grant these additional rights manually, if you require these features.

Note

Windows ME and Windows 98 do not support the use of Windows services. SQL Server and SQL Server Agent run as executable programs on these Windows platforms. A domain user account is not required in these environments.

Practice: Creating a Dedicated Windows 2000 User Account

In this practice you use Active Directory Users And Computers to create and configure a dedicated Windows 2000 user account.

To configure a dedicated domain user account

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Programs, point to Administrative Tools, and then click Active Directory Users And Computers.
The Active Directory Users And Computers window appears.
In the console tree, expand SelfPacedSQL.MSFT.
Right-click Users, point to New, and then click User.
The New Object - User dialog box appears.
In the First Name text box, type SQLService.
In the User Logon Name text box, type SQLService , and then click Next.
In the Password and Confirm Password text boxes, type sql.
Select the Password Never Expires check box, and then click Next.
Click the Finish button.
In the console tree, click Users.
The details pane displays a list of all users in the Users container.
Verify that the SQLService domain user account has been created.
Close Active Directory Users And Computers.

Choosing an Authentication Mode

SQL Server 2000 supports two authentication modes, Windows Authentication Mode and Mixed Mode. The default authentication mode for SQL Server 2000 is Windows Authentication Mode. When this mode is used, the only users who can connect to SQL Server 2000 are users who have been previously authenticated by the Windows operating system. This is called Windows Authentication. When Windows Authentication is used, SQL Server accepts trusted connections from the Windows operating system.

The alternative authentication mode is Mixed Mode. This means that SQL Server 2000 will use one of two methods of authentication. The first method is to rely on the Windows operating system to authenticate users. The second method is for SQL Server 2000 to authenticate users directly based on the submission of a user name and password to SQL Server 2000 by the client application attempting to gain access. This is called SQL Server Authentication.

Note

When the Personal Edition of SQL Server 2000 is installed on Windows ME or Windows 98, SQL Server authentication must be used. This is because the server side of the trusted connection API is not supported on these Windows operating systems.

For most SQL Server 2000 environments, Windows Authentication Mode should be used. It provides the highest level of security, including password encryption, and is easier to administer in a domain environment. The SQL Server 2000 authentication mode can be changed after installation, as well as during setup. Managing SQL Server 2000 security is covered in Chapter 10. This includes a discussion of environments in which the use of Mixed Mode security might be necessary.

Determining Collation Settings

The default collation for SQL Server 2000 is defined during setup. A collation is used to determine how non-Unicode character data is stored and also governs how SQL Server 2000 sorts and compares Unicode and non-Unicode data. A SQL Server 2000 collation consists of a Windows collation and a SQL collation.

Note

Unicode allows data from most languages to be recorded, but takes twice as much space as non-Unicode data. Non-Unicode data only supports 256 characters, and is specific to a particular language.

The place to start in understanding SQL Server 2000 collations is the Windows operating system. When you install a Windows operating system, you install a version for the language you want to use, such as English, Greek, or Russian. Different languages require different characters, and therefore different code pages (also referred to as character sets), to support these character sets and associated keyboard layouts. Based on the version of the Windows operating system that you have installed, a Windows locale is set. The Windows locale also determines settings for numbers, currencies, times, and dates. When you install SQL Server 2000, the Setup program determines a default Windows collation for SQL Server 2000 based on the Windows locale of the Windows operating system on which you are installing SQL Server 2000.

Although this might initially seem complicated, determining the Windows collation to use for SQL Server 2000 is generally quite straightforward. Allow the SQL Server 2000 Setup program to determine the default Windows collation based on the Windows locale of the Windows operating system unless:

The primary language being supported by the SQL Server 2000 instance you are installing is different from the Windows locale of the computer on which you are installing SQL Server 2000, or
The SQL Server 2000 instance you are installing will be participating in a replication scheme with SQL Server 2000 instances supporting a different collation. You need to ensure, in this case, that the collation selected by Setup uses the same code page as the other instances of SQL Server included in the replication scheme. In this environment, setting collation at the database level is also an option.

The next step is to understand that SQL Server 2000 uses a SQL collation to match settings used in previous versions of SQL Server. This is necessary because earlier versions of SQL Server specified code page number, character sort order, and Unicode collation separately. A SQL collation specifies three collation attributes:

The non-Unicode code page—Tells SQL Server 2000 how to store non-Unicode character data.
The non-Unicode sort order—Tells SQL Server 2000 how to sort non-Unicode character data types, such as sensitivity to capitalization.
The Unicode sort order—Tells SQL Server 2000 how to sort Unicode data types.

When the SQL Server 2000 Setup program detects that SQL Server 2000 is being installed on a computer with an English locale setting in the Windows operating system and on which no prior version of SQL Server has been installed, the Setup program automatically chooses the dictionary order, case-insensitive character sort order, for use with the 1252 character set. These match the default settings for SQL Server 7.0.

If you are installing an instance of SQL Server 2000 on a computer that has SQL Server 6.5 or 7.0 installed, the SQL Server 2000 Setup program will determine a SQL collation for SQL Server 2000 based on the code page, sort order, and Unicode collation settings in the earlier version of SQL Server (including collations that are obsolete). Most combinations of these settings are supported, but not all. Use a Custom setup to modify the collation setting selected by the SQL Server 2000 Setup program.

The final step is to understand that SQL Server collations are settable at multiple levels. You set a default collation (which consists of a Windows collation and a SQL collation) for an instance of SQL Server 2000. This default collation is the collation setting for all system databases. The model database, all user databases, and all other SQL Server 2000 objects inherit this default collation setting, unless a different collation is specified for the database or object. In international environments, having different collations for different databases can solve numerous issues involving the use of multiple languages and character sets.

Practice: Determining Your Windows Collation

In this practice, you use Control Panel to determine your Windows collation.

To determine your Windows collation

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Settings, and then click Control Panel.
The Control Panel window appears.
Double-click the Regional Options icon.
The Regional Options dialog box appears with the General tab selected.
In the Your Locale (Location) drop-down list, determine your Windows locale.
English (United States) is the locale used by the author.
Click OK to close the Regional Options dialog box. Do not change your Windows locale.
Close the Control Panel window.

Selecting Network Libraries

SQL Server 2000 uses network libraries to send network packets between clients and a server running SQL Server 2000. These network libraries are implemented as DLLs and perform network operations using specific interprocess communication (IPC) mechanisms (such as shared memory, named pipes, and RPCs). There are client-side network libraries and server-side network libraries. These client and server network libraries support specific underlying network protocols. For example, the TCP/IP sockets network library allows SQL Server 2000 to communicate using Windows sockets as the IPC mechanism across the TCP/IP network protocol. The installation of these network protocols is part of your Windows operating system configuration and should be completed prior to installing SQL Server 2000.

A server running SQL Server 2000 monitors all configured network libraries simultaneously for incoming communication from clients seeking to gain access. By default, SQL Server 2000 installed on any Windows 2000 or Windows NT 4.0 operating system monitors TCP/IP sockets and named pipes (and shared memory for local clients). All additional network libraries are installed during setup, but are not configured for use, unless specified during a Custom setup. For a complete list of supported network libraries, see Chapter 1. If you need to configure server network libraries after setup, use the Server Network Utility from the Microsoft SQL Server program group or from the SQL Server properties dialog box in SQL Server Enterprise Manager. Configure additional server network libraries only if they are needed in your network environment, such as to support clients on a Novell network.

Note

Server-side named pipes are not supported on Windows ME and Windows 98. Clients must use TCP/IP sockets to connect to SQL Server 2000 running on these operating systems.

SQL Server 2000 clients initiate communication with a server running SQL Server 2000 using a specific network library. You can configure multiple network libraries on SQL Server 2000 clients, and you can define the order in which the Windows operating system will attempt to use each network library when connecting with SQL Server 2000. By default, all SQL Server 2000 clients running Windows 2000, Windows NT 4.0, Windows ME, or Windows 98 use the TCP/IP sockets as the primary network library and named pipes as the secondary network library. If you need to manually configure a client-side network library to communicate with a specific SQL Server installation, use the Client Network Utility from the Microsoft SQL Server program group (provided the client software has been installed on the computer).

The SQL Server 2000 client software handles the complexities of establishing a connection with a server running SQL Server 2000 automatically. The network name of the computer on which the SQL Server 2000 instance is running (and instance name if applicable) is all that is needed to establish the connection, assuming matching network libraries exist.

Note

If a SQL Server 2000 client needs to connect to a named instance of SQL Server 2000, TCP/IP sockets, named pipes, or NWLink IPX/SPX must be used. Named instances are covered in Lesson 4 of this chapter.

You can use the Server Network Utility to enable SSL encryption over all enabled network libraries. SQL Server 2000 can then use the SSL to encrypt all data transmitted over any network library between a SQL Server 2000 client and a server running SQL Server 2000. The encryption level, 40-bit versus 128-bit, depends on the level of encryption supported by the Windows operating system involved. The multiprotocol network library supports its own encryption using the Windows RPC encryption API rather than SSL. This encryption mechanism is primarily for backward compatibility and multiprotocol encryption does not support named instances. Use the more secure SSL, rather than multiprotocol encryption, whenever possible.

Deciding on a Client Licensing Mode

SQL Server 2000 supports two client licensing modes: per processor and per seat. With per-processor licensing, you need a license on the server running SQL Server 2000 for each processor on the computer. If this client licensing mode is selected, no additional licenses are needed for any clients connecting to this installation of SQL Server 2000. This client licensing mode is intended to be most cost-effective for installations with large numbers of users, particularly anonymous Internet users.

With per-seat licensing, each client connecting to an installation of SQL Server 2000 must have a separate Client Access License (CAL). This client licensing mode is intended to be most cost-effective for installations with a small to medium number of defined users connecting to more than one server.

Lesson Summary

During the installation of SQL Server 2000, you must decide the appropriate user account to use for the SQL Server and the SQL Server Agent services. Generally, a domain user account is used if either of these services must communicate across the network. You must also decide the authentication mode for SQL Server 2000. Window Authentication Mode is the default configuration, where SQL Server 2000 will only accept trusted connections from the Windows operating system. In addition, you must decide if the default collation determined by the SQL Server 2000 Setup program is appropriate for your environment. In most cases, you will not modify the default collation. You might also want to configure additional network libraries in certain environments. Finally, you must elect to use per-processor or per-seat licensing for your SQL Server 2000 installation. Your choice will generally be determined based on the type and quantity of clients accessing your SQL Server 2000 installation.