Lesson 1: Reviewing the Results of Installation

3 4

After you have installed SQL Server 2000, it is important to familiarize yourself with the results of the installation. This includes reviewing the SQL Server 2000 files and folders in the file system, the SQL Server 2000 registry entries in the Windows registry, and the SQL Server 2000 programs in the Microsoft SQL Server program group on the Start menu. It also includes understanding the installation result differences between a default instance and a named instance at both the file system and the Windows registry levels. Perhaps most importantly, it is critical to understand the default security permissions set on the NTFS file structure and Windows registry keys. Improper or inadequate permissions can cause problems that you might need to troubleshoot.

---

After this lesson, you will be able to

• Use Windows Explorer to examine the files and folders added to the file system for the default or a named instance of SQL Server 2000
• Use Registry Editor to examine the registry entries that were added to the Windows 2000 registry for the default or a named instance of SQL Server 2000
• Understand the default security permissions set on folders and registry keys for the domain user account used for the SQL Server and SQL Server Agent services
• Know which SQL Server 2000 programs are available from the Start menu

Estimated lesson time: 45 minutes

---

What Files and Folders Were Added?

After a new installation of SQL Server 2000 (not an upgrade), the default folder location for all files and folders added to the Windows file system is the \Program Files\Microsoft SQL Server folder on the same partition as the Windows operating system. Within this folder, two subfolders are created. The first subfolder is called 80. This folder and its subfolders contain the shared files that are common between all instances of SQL Server 2000. The location for this folder cannot be changed. This folder contains tools, utilities, and SQL Server 2000 Books Online. Table 3.1 lists these shared folders and briefly describes their contents.

Note

---

Deleting any of the files or folders listed in Table 3.1 might cause SQL Server 2000 to lose functionality or data, and might require that you uninstall and then reinstall SQL Server 2000 to regain that functionality.

Table 3.1 Contents of the Shared Folders in the \Program Files\Microsoft SQL Server\80 Folder

Folder	Contents Description
\Program Files\Microsoft SQL Server\80\Com	Contains DLLs for COM objects, including the replication DLLs and executable programs.
\Program Files\Microsoft SQL Server\80\Com\Resources	Contains Run Length Limited (RLL) resource files used by the DLLs in the Com folder. The subfolder number within this folder will vary depending upon the localized version; 1033 is for U.S. English.
\Program Files\Microsoft SQL Server\80\Tools\Binn and \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources	Contains all of the shared SQL Server 2000 client administrative executable programs and their associated DLLs, RLLs, and Help files. It also contains a few miscellaneous shared files, such as the default SQL Server Enterprise Manager MMC console.
\Program Files\Microsoft SQL Server	Contains SQL Server 2000 Books Online files, \80\Tools\Books including SQL Server 2000 online Help files.
\Program Files\Microsoft SQL Server\80\Tools\DevTools	Contains files and sample programs for use by developers. The exact contents of this folder will vary depending upon the choices you made during installation. You can choose to install additional files and programs for developers during a custom setup.
\Program Files\Microsoft SQL Server\80\Tools\Html	Contains HTML, JScript, and Graphics Interchange Format (GIF) files used by SQL Server 2000 Books Online and by SQL Server Enterprise Manager.
\Program Files\Microsoft SQL Server\80\Tools\Scripts	Contains Transact-SQL scripts by SQL Query Analyzer for object searches.
\Program Files\Microsoft SQL Server\80\Tools\Templates\Dts	Contains the DTS demonstration template file, Template Demo.dtt. This is a partially configured DTS package for copying data between an OLE DB data source and an OLE DB data destination. Templates are read-only files for use in creating packages.
\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Profiler	Contains default template files with a variety of trace definitions for use with SQL Profiler. These template files have a .tdf file extension.
\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer	Contains boilerplate files with Transact-SQL scripts for performing a variety of administrative tasks, such as creating a table or managing linked servers. These template files have a .tql file extension.

The SQL Server 2000 Setup program also creates a second folder containing program and data files that are unique for each SQL Server 2000 instance. The default location for this folder is \Program Files\Microsoft SQL Server; however, you can change this default during setup. This is also the default location for all newly created user databases for the SQL Server instance. The folder name for the default instance is Mssql and for a named instance is Mssql$InstanceName. Program file settings and databases for each instance are unique and are contained in this separate subfolder tree.

Table 3.2 lists the program and data folders that are unique to each instance and briefly describes their contents. For convenience, the folder paths for the default instance installed in the default location are used in this table. For a named instance, substitute Mssql$InstanceName for Mssql in the folder path.

Table 3.2 Program and Data Folders That Are Unique to Each Instance of SQL Server 2000

Folder	Contents Description
\Program Files\Microsoft SQL Server\Mssql\	Contains information required to properly uninstall SQL Server 2000. It also includes the subfolders described below.
\Program Files\Microsoft SQL Server\Mssql\Backup	Contains all backup files that were saved to the default location.
\Program Files\Microsoft SQL Server\Mssql\Binn and \Program Files\Microsoft SQL Server\Mssql\Binn\Resources	Contains all of the unique SQL Server 2000 executable programs and their associated DLLs and RLLs. This folder also contains DLLs for extended stored procedures.
\Program Files\Microsoft SQL Server\Mssql\Data	Contains the SQL Server 2000 system and sample user database files. This is also the default location for all user-created databases.
\Program Files\Microsoft SQL Server\Mssql\Ftdata	Contains the SQL Server 2000 full-text search catalog files.
\Program Files\Microsoft SQL Server\Mssql\Install	Contains all of the Transact-SQL scripts used by the SQL Server 2000 Setup program. This includes the scripts to reinstall the Northwind or Pubs databases, if desired. It also contains a Setup.iss file that captured the interactive installation choices you made during setup.
\Program Files\Microsoft SQL Server\Mssql\Jobs	The storage location for temporary job output files.
\Program Files\Microsoft SQL Server\Mssql\Log	Contains the SQL Server and SQL Server Agent error log files. This folder contains the current logs, as well as a number of previous error log files.
\Program Files\Microsoft SQL Server\Mssql\Repldata	The default working location for replication tasks, including the storage of snapshot files used in replication tasks.
\Program Files\Microsoft SQL Server\Mssql\Repldata\Ftp	The working location for the storage of replication snapshot files when using the Internet and supporting anonymous subscribers.
\Program Files\Microsoft SQL Server\Mssql\Upgrade	Contains the files required for a version upgrade from SQL Server 6.5 to SQL Server 2000. Only the default instance contains this folder and these files.

Figure 3.1 shows the hierarchy of the folder structure explained in Table 3.2.

Figure 3.1

Hierarchy of the program and data folders that are unique to each instance of SQL Server 2000.

What Permissions Were Set in the NTFS File System

When SQL Server 2000 is installed on an NTFS partition, the Setup program sets access permissions to the Mssql or Mssql$InstanceName subfolder structure that holds the program and data files for each instance. The Setup program ensures that only the SQL Server and SQL Server Agent domain user accounts and members of the local Administrators group have read or write access to this folder structure. See Figure 3.2.

The SQL Server services domain user account requires Full Control permission on all files and folders in this subfolder tree for these SQL Server services to function properly. The SQL Server services using the local system account have Full Control permission because the local system account, by design, is a member of the local Administrators group. Only the SQL Server services domain user account and members of the local Administrators group have modify, write, or delete permissions to this folder structure, so unauthorized users are prevented from tampering with the program and data files. This is another one of many good reasons to use the NTFS file system.

Figure 3.2

Permissions set for the Mssql$MyNamedInstance subfolder.

Note

---

By default, authenticated users can read and execute all files in the Program Files folder tree, but have no permission to write, modify, or delete any of these files. The Setup program modifies this default, but only for the Program and Data Files folders, granting Full Control permissions on these folders to the domain user account used by the SQL Server services.

Practice: Reviewing the Files and Folders That Were Created

In this practice you use Windows Explorer to review the files and folders that were created by the SQL Server 2000 Setup program, and their permissions.

To review the files and folders that were created

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
2. Click Start, point to Programs, point to Accessories, and then click Windows Explorer.

   The Windows Explorer window appears.

3. In the console tree, expand My Computer, expand Local Disk (C:), expand Program Files, and then expand Microsoft SQL Server.

   Notice that there are three subfolders named 80, Mssql, and Mssql$MyNamedInstance. The 80 folder holds the common files. The other two folders contain the program and data files for the default instance and the named instance that we called MyNamedInstance.

4. In the console tree, expand 80, expand Tools, and then click Binn.
5. On the View menu, click Details.

   Notice that the full details of all files in the Binn folder appear in the details pane.

6. On the Tools menu, click Folder Options.

   The Folder Options dialog box appears with the General tab selected.

7. On the View tab, click the Like Current Folder button.

   The Folder Views message box appears.

8. Click the Yes button.

   All folders will now display all file details by default.

9. Click OK to close the Folder Options dialog box.
10. In the details pane, click the Type column to sort by type and then review the client administrative tools executable programs (applications).

    Most of these tools will be covered in detail later in this book.

11. In the console tree, expand Mssql and then click Binn.
12. In the details pane, click the Type column and then review the SQL Server 2000 executable programs (applications).

    Most of these programs will be covered in detail later in this book. Notice the Sqlservr.exe and Sqlagent.exe programs. These are the executable versions of the SQL Server and the SQL Server Agent services.

13. In the console tree, expand Mssql$MyNamedInstance.

    Compare this folder structure to the folder structure for Mssql. Notice that there is no Upgrade folder. This folder only exists for the default instance.

14. In the console tree, right-click Mssql and then click Properties.

    The Mssql Properties dialog box appears with the General tab selected.

15. Click the Security tab.

    Notice that only the SQLService domain user account that we are using as the service account for the SQL Server and SQL Server Agent services for this instance and members of the local Administrators group have permissions to this folder and its subfolders.

16. Click Cancel to close the Mssql Properties dialog box.
17. In the console tree, right-click 80 and then click Properties.

    The 80 Properties dialog box appears with the General tab selected.

18. Click the Security tab.

    Notice that all authenticated users have permission to read, list, and execute files in this folder and all subfolders, but have no permission to write, modify, or delete files. This includes the SQL Server services domain user account. Only members of the Administrators and Server Operators local group, the CREATOR OWNER group, and the SYSTEM group have additional rights to this folder and its subfolders.

19. Click Cancel to close the 80 Properties dialog box, and then close Windows Explorer.

What Registry Keys Were Added

When you install SQL Server 2000, the Setup program adds registry keys to the Windows registry related to the shared files and services, and related to the unique program and data files for each instance. Registry keys related to the shared files and services are added to the following locations for all SQL Server 2000 instances:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServerADHelper
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client

Registry keys relating to the unique program and data files for the default instance are added at and under the following locations:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer

Registry keys relating to the unique program and data files for a named instance are added at and under the following locations:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$Instancename

Figure 3.3 shows some of these registry keys.

Figure 3.3

The MSSQLServer registry key added by the SQL Server Setup program.

What Permissions Were Set on Registry Keys

When adding these registry keys, the SQL Server Setup program generally limits read or write access to these keys to the SQL Server services domain user account and members of the local Administrators group (and sometimes the SYSTEM group). For certain keys, read access is granted to authenticated users and members of the local Power Users group. In addition, owners of objects may have access to subkeys through the CREATOR OWNER group if they are granted permission to create objects. By default, the SQL Server services domain user account and members of the local Administrators group have Full Control access to these registry keys and their subkeys. For the default instance, the Setup program specifically limits access to all registry keys at or under the following registry keys:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Replication
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Tracking
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer

For a named instance, the Setup program similarly limits access to all registry keys at or under the following registry keys:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ Microsoft SQL Server\InstanceName
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$InstanceName

Note

---

Because the registry structure for the default instance is different than for a named instance (for compatibility with previous versions of SQL Server), the Setup program has to set permissions on more individual keys for the default instance than for a named instance. For a named instance, it only has to set permissions on two keys and let the permissions flow down to all keys under those keys.

Figure 3.4 shows permissions on the MYNAMEDINSTANCE registry key.

Figure 3.4

Permissions on the MYNAMEDINSTANCE registry key.

Note

---

You must use the Regedt32.exe version of the Registry Editor to view the permissions on registry keys, rather than the Regedit.exe version of Registry Editor.

Finally, for any instance of SQL Server 2000, the SQL Server services domain user account requires read and write access to the following existing registry keys. The Setup program grants the SQL Server services domain user account read and write permissions to all registry keys at or under the following existing registry keys:

• HKEY_LOCAL_MACHINE\SOFTWARE\Clients\Mail
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib

Inadequate permissions to these registry keys will cause mail-related and performance monitoring-related failures.

Practice: Reviewing Permissions on Registry Keys

In this practice you use Registry Editor to verify the permission set by the SQL Server 2000 Setup program on certain registry keys.

To review permissions on registry keys

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
2. Click Start and then click Run.

   The Run dialog box appears.

3. In the Open drop-down combo box, type regedt32 and then click OK.

   The Registry Editor appears.

4. On the Window menu, click HKEY_LOCAL_MACHINE on Local Machine.
5. In the console tree, expand SOFTWARE, expand Microsoft, and then expand Microsoft SQL Server.

   Notice the 80 registry key. This key is common to the default and all named instances. This key relates to the shared files. Also notice the MyNamedInstance key. This key relates to the unique program and data files for your named instance.

6. In the console tree, click MyNamedInstance.
7. On the Security menu, click Permissions.

   The Permissions For MyNamedInstance dialog box appears. Notice that only the SQLService domain user account and the local Administrators group have full control access to this registry key.

8. Click Cancel.
9. In the console tree under SOFTWARE\Microsoft, expand Windows NT, expand CurrentVersion, and then click Perflib.
10. On the Security menu, click Permissions.

    Notice that only the local Administrators group, the SQLService domain user account, and the System group have permission to write to this registry key. The Interactive and Server Operators group has permission to read this registry key. In addition, owners of objects may have permission on subkeys through the CREATOR OWNER group.

11. Click Cancel.
12. Close the Registry Editor.

What Programs Were Added to the Start Menu

When you install SQL Server 2000, the Setup program adds the SQL Server 2000 programs used most frequently to the Start menu. See Figure 3.5. Most of these programs were introduced in Chapter 1. Chapter 2 covered the use of the Client Network Utility and the Server Network Utility. SQL Server Service Manager, SQL Server Enterprise Manager, and SQL Query Analyzer are covered in Lessons 2 and 3 of this chapter. Configuring SQL XML support will be discussed in Chapter 12. Importing and exporting of data will be discussed in Chapter 7, and SQL Profiler will be discussed in Chapter 14.

Figure 3.5

SQL Server 2000 programs added to the Start menu by the Setup program.

Lesson Summary

The SQL Server 2000 Setup program installs SQL Server 2000 files into a number of different folder structures. One folder structure (\Program Files\Microsoft SQL Server\80) contains the files common to all SQL Server 2000 instances on the computer. Another folder structure (either \Program Files\Microsoft SQL Server\MSSQL or \Program Files\Microsoft SQL Server\Mssql$InstanceName ) contains the program and data files unique to the particular SQL Server 2000 instance. On an NTFS partition, the Setup program secures the folder structure containing the program and data file folders by limiting access permissions to this folder structure to the SQL Server services domain user account and members of the local Administrators group. In addition, the Setup program adds and limits access to a number of keys in the Windows registry. It also ensures that the SQL Server services domain user account has write access to two existing registry keys. Finally, the Microsoft SQL Server program group is created containing shortcuts to the most commonly used SQL Server 2000 executable programs and wizards, as well as to Books Online.