Debugging Stored Procedures

I have performed many tasks in numerous different roles, and one of the most frustrating has been debugging stored procedures. But no more! The ability to debug stored procedures as though we were debugging any development platform code is part of one of the enhancements to SQL Server 2000's Query Analyzer. We can insert break points, step into, step over, and so forth. This is wonderful for those of us who have tried to monitor what is happening in a stored procedure.

Previously we could do this with SQL Server 7.0 and Visual InterDev, but there was a lot of overhead in setting it up. With the new debugging tools, all we do is right- click and select Debug. How simple is that?

We have the standard debugging windows as well. We can get the values of variables from the Watch window and view the procedures that have been called and not completed in the Callstack window. This makes it easy to migrate from a development environment to using SQL Server 2000. So you Access developers out there must be getting really excited by now!

What? No More Room?

One of the trouble spots a DBA must keep an eye on is conserving a computer's most precious resources, memory and disk space.

If we have several databases on one server, we can find that we run out of disk space, and if that happens, our databases will fail.

Of course, in Spy Net's fictional scenario, that could mean World War III! But in the real world, running out of space can still cause serious problems, especially in mission-critical databases such as utilities or emergency response systems. In this section, we look at the causes of resource failure and several ways to avoid down time, including managing file and log size.

How Memory Affects Database Transactions

The memory-deprived databases will fail because tempdb is where most of the changes that you make to your data are performed before they are committed to disk. If you have enough RAM available, SQL Server 2000 will put as much of your database as it can up into RAM. After all, it is much faster to read from RAM than to scan a disk for the information. However, if RANI is a short commodity or you have concerns about the amount of disk space your database is eating, relax, because we have even have control over that.

When you are in Enterprise Manager you have the option to view how much space your data files for your database are allocated and how much is used. To see this information, simply click the SQLSpyNet (or any other) database within Enterprise Manager, and you will see a screen.

Shrinking Your Data Files to Reduce the Database

When we are talking about shrinking our data files we are not actually referring to the process of compacting them like a zip program would.

If we shrink our data files, we remove unused data pages. For example, if we had a table that had five data pages on which it stored the data, and we deleted two pages worth of the data, although our table would have only three pages that actually stored data, SQL Server 2000 still would have five pages allocated to the table.

When we shrink the data files, we just get rid of the extra two pages that the table was using. This does, however, have restrictions, but I think you get the idea.

What do we do when our data files are too large? Although we cannot shrink an entire database smaller than its original size, we can shrink our data files smaller than their original allocation sizes. We must do this by shrinking each data file individually by using the DBCC SHR I NKF I LE Transact-SQL statement. This allows us to reallocate how much space the given data file is allowed to use.

Protocol Negotiation and Session Setup

SMB has a built-in mechanism that is used by the client and server to determine the other's capabilities so that a common protocol version can be established that the two will use for the network con• nection. The first SMB message that the client sends to the server is one of the SMB_COM_NEGOTIATE type. The client uses this message to send the server a list of the dialects it understands. The server selects the most recent dialect it understands from the client's list and returns a message to it.

The response the server returns depends on the type of client. The information includes the dialect selected and can include additional information, such as buffer sizes, supported access modes, time and date values, and security information. After the client receives this response, it can continue to set up the session by using the SESSION_SETUP_ANDX message type.

If the initial server response indicates that user-level security is being used, this message type can be used to perform a user logon. The client sets a value in the message header called the UID (user ID) for the account it wants to use. It also supplies the account name and password to the server by using this message type. If these values are validated by the server, the user can continue to use the UID to make subsequent accesses.

Other setup functions that are performed by using SESSION_SETUP_ANDX include the following:

• Set the maximum values for the size of buffers that will be used in the message exchange.
• Set the maximum number of client requests that can be outstanding at the server.
• Set the virtual circuit (VC) number.

If the VC passed to the server is zero and the server has other circuits open for the client, it will abort those services, assuming that the client has rebooted without freeing those services first. To properly close a session, the client uses the message type LOGOFF_ANDX, which causes the server to close all files associated with the user's UID.

Accessing Files

Other SMB message types are used to traverse the resource directory and to open, read, write, and close files. First, the user must connect to the resource by using the TREE_CONNECT message. The mes‑

sage includes the name of the resource (server and share name) and, for earlier clients that do not perform logons, a shared password. The server responds by sending the user a value called the TID (Tree ID), which will be used in SMBs exchanged for this connection.

After the connection has been established, several basic SMB command formats can be used to manipulate files and directories that reside on the share. For example, the CREATE_DIRECTORY message is used to create a new directory in the file share's directory structure. The client passes the pathname for the new directory, and the server creates the directory, provided that the client has the appropriate access rights or permissions. The DELETE_DIRECTORY SMB message can be used to remove a directory, again based on the functions allowed for the username.

Opening and Closing Files

The OPEN message is used by a client to open a file. The path for the file is given, relative to the file share root. The client specifies the access that is desired, such as read, write, or share. If the file is successfully opened, the server returns a File ID (FID) to the client, which is used to further access the file using other SMB message types; it is similar to a file handle, which most programmers will recognize.

The server also returns data to the client indicating the actual access that was granted, which is read- only, write-only, or read/write. The CLOSE message is sent by the client to tell the server to release any locks held on the resource fileheld by the client. After this message, the client can no longer use the FID to access the file, but it must instead reopen the file and obtain a new value.

When a client does not know the exact name of a file that it wants to open, the SEARCH message can be used to perform a directory lookup. This function enables wildcards to be used, and the server response can include more than one filename that matches the request.

Microsoft Windows Share-Level Security

Earlier versions of Windows operating systems used the FAT (File Allocation Table) and FAT32 (similar to FAT, but for larger disk volumes, as well as other features) file systems. Beginning with Windows NT, the NTFS file system enabled a more secure file system. FAT and FAT32 don't provide the mechanisms to store security attributes, such as access control lists (ACLs), for files or directories, as NTFS does.

The main benefit of using NTFS is that it does allow you to store a lot more information about a file or a directory. When you use the NTFS file system to format a disk, you can apply user-level security permissions on individual files or directories. You can still create file shares using Windows sever operating systems, but NTFS allows you to further define which files/directories a user can access when using the file share. For an environment that requires a high degree of security, the NTFS partition is the choice to make. Additionally, the Windows 2000 and Server 2003 operating systems allow for other features that make NTFS a more secure choice, including the capability to encrypt and decrypt data on-the-fly when storing or retrieving it from disk. You can also choose to compress data on files so that less disk space is used to store files.

For either of these options, just right-click on a folder and select the properties page from the menu that appears. You'll see an Advanced button on the General tab. After clicking that button, you'll see two important check boxes. The first is Compress Contents to Save Disk Space. The second is Encrypt Contents to Secure Data. Select either or both of these check boxes to enable that feature for the folder.

The only reason to format a disk using FAT or FAT32 is if you are going to dual-boot the computer, and one of the earlier operating systems (such as Windows 9x) will be used. This is because Windows 9x systems are not capable of using an NTFS partition. You can create one partition and format it using FAT, and create additional partitions using NTFS for Windows NT and later operating systems, such as Windows 2000, Server 2003, and XP. However, this sort of dual-boot setup should be used only in an environment where security is not an important issue, such as a standalone computer (one not connected to a network). This is because a FAT partition does not let you set file or directory partitions and does not support encryption.

Another example is in your home, where you don't have such strict security requirements. For example, you might need to use an older software application that will not run under newer Windows operating systems. Even then, if you are connected to the Internet, you should consider the implications of using FAT or FAT32 on a home computer because many hackers regularly scan IP addresses looking for vulnerable systems. If you stay online for extended periods browsing the Internet—or if you're online all the time using a broadband connection such as a cable or DSL modem, then a FAT- based disk is wide open for planting a Trojan horse and other malicious programs. If you use NTFS instead, and set up your user accounts correctly, you can potentially head off this sort of problem. This is because on NTFS partitions you can set permissions for every file or directory on a one-by-one basis.

Single computers and small LANs typically use an out-of-the-box firewall solution, such as a DSL/cable router, which can offer some degree of protection, such as Network Address Translation (NAT). However, by applying permissions on an NTFS formatted disk, you can further enhance your security.

System Daemons and Services continue…

Delegating Authority

In a network of any size other than a simple workgroup, it is usually necessary to delegate authority to other administrators or middle-level management personnel. When you find that you must create accounts that have privileges to perform administrative functions, do not give carte blanche access to every account. Keep track of the exact functions an account will be used for, and grant only the access rights and permissions needed.

For example, if an operator will be performing backup functions on a server, he does not need to have full rights and privileges on the server. Under Windows Servers, you can place the user's account into the Backup Operators user group to give him the capabilities he needs, without compromising all files on the system. If you have users who must be able to add or modify user accounts, check the operating-system documentation and give the users access only to the resources and data files they need.

User Accounts

Generic accounts might seem like a good idea at first thought, but they provide nothing in the way auditing. If you simply let one or more users share the root account on a Unix system, or the Administrator account on a Windows server, you will have no way of determining tracking, and resolving who did what when something goes wrong. Indeed, because you can grant the same capabilities to any new account you can create, why not do so?

Give each user who requires elevated capabilities her own account, and grant the necessary privileges to the account. This way you can track each user to be sure she does not abuse her account or use it in a way you do not expect.

When you have more than one user using the same account, there is also the likelihood that the password will be compromised and someone who is not authorized to use the account will do so.

Application Servers, Print Servers, and Web Servers

One particularly common error you can make is to put all your eggs in one basket. Instead of using one server to provide print services or file services or Web services, many administrators use one server to provide all three. This is not necessarily a good idea.

Specialized servers can limit the damage that can be done by intruders and also can make it easier to delegate authority so that a particular administrator can concentrate on a limited set of functions for a certain server. Web servers are particularly prone to attempts by hackers to intrude onto your network. New applications and technologies are being developed and deployed all the time, and the newer they are, the more likely it is that they will have bugs or other loopholes that make them more risky than other applications that run on the network.

Placing sensitive data files on a Web server simply because it is convenient to use the machine's resources is not a good idea if it is also being used as a Web server. Make it more difficult to get at these files by dedicating a file server computer to them instead.

Delegating servers is almost like delegating authority to users. When you divide up resources and partition them into manageable groups, you make it less likely that an attack on one object will result in damage to all objects.

Denial of Service (DoS) attacks are very common on networks now. This kind of attack can be done by a malicious person who takes advantage of a known weakness in a protocol or an implementation of a particular service. One common mistake that administrators make when setting up an FTP site is to place it on an ordinary server.

For example, you might want to have an FTP server that allows customers to log in to your system and download information, patches, or other files. You also might want to be able to let them upload files or messages to your site. If you are going to allow anonymous FTP access, be absolutely sure that the service is configured so that it can access only a dedicated disk or set of disks. Do not allowanonymous access to an FTP service that writes to a system disk or a data disk that is important in your network. It is quite easy for an outsider to simply fill up the disk with meaningless data, causing a system to lock up or crash, depending on the operating system. If an important data disk becomes full, it can cause an extended period of downtime, putting employees out of work for hours while yo try to first determine the cause and then remedy it!

Physical Security Measures continue...

Novell NetWare provides this functionality by giving the user a logon to the network that is controlled by the Novell Directory Services. Each user is represented in the directory by a User object, the properties of which specify information about passwords and connections.

The Unix operating system does not use the concept of a domain. Instead, each Unix host maintain a password file that stores information about each user, including an encrypted password. To access resources on other network hosts, the Unix user must either log on when accessing the computer or use a proxy mechanism. TCP/IP utilities such as FTP and Telnet often send user passwords across the network in clear-text format and are easy targets for interception.

The Unix remote utilities, usually called r-commands because they all start with the letter r, are use to perform ordinary network functions such as copying or printing files or logging in to a remote s tern. This is very useful in the network environment in which a user performs functions on many( ferent machines. These utilities are not necessarily good when looked at from a security standpoint however. Although the user must have a valid user account on the remote hosts on which these co mands execute, the user does not have to provide the password.

Instead, an entry in the /etc/hosts .equv file or the. rhosts file on a remote computer is what deter mines access. The remote machine trusts the computer on which the user executes an r-command if can find an entry in either of these files for it. Each entry in the /etc/hosts . equiv file contains a hostname and a username, to identify users and the hosts that are allowed to execute these commands without providing a password. The assumption is that if you have logged in to the remote host, you have already been authenticated. The . rhosts file works in a similar manner but resides in user's home directory. The remote users entered in this file can perform functions based on the account associated with that user.

Although this sounds a lot like the Windows NT/2000/Server 2003/XP trust mechanism, it is not. It is quite easy to impersonate a remote node and gain entry into a Unix/Linux system by using the r-commands.

Resource Protections

After a user has been authenticated by the erating system, the next step to access a resource is for check to be done to see whether the resource has any access controls placed on it. Typically, an open rating system will grant access to a resource, such as a file, by granting users the right to do the folioing:

• lRead the file

• Write to the file

• Execute the file

• Take ownership of the file

• Delete the file

These concepts also can be extended to resources such as printers and modems. When granting these rights, most operating systems also enable you to specify which rights are applied to users or groups of users. For example, Windows NT enables you to group users into local or global groups. When you set the access controls on a file, you can specify the access rights by group. Using this method, one group of ordinary users might be able to read a file, while a group of users that manages the filemight be granted read and write access, as well as delete access to the file. To prevent programs from being run by unauthorized users, the execute right can be granted or denied to a user or a group of users.

It is important to understand the features of your operating system that pertain to granting rights or permissions. Rights generally enable a user to perform an action. Permissions are placed on resources and define who can access and what kind of access can be made of a resource.

After the Fact: Auditing Use

As you may be aware, there are auditing tools you can use to keep track of resource use, both attempted and successful logon attempts. Here it is important only to note that it is not enough to organize users into groups and grant them resource permissions throughout the network. A large user base, combined with multiple servers that hold valuable resources, makes it difficult at times for an administrator who is not familiar with the information resources provided by a specific server to understand the permissions needed. For example, a new user in the accounting department might or might not need access to accounts receivable files or accounts payable files. They might need access to one or the other or maybe both files. A manager in thatdepartment would probably be the likely person to make the decision about what files the usershould be able to access.

However, if the user is placed into a group, which is generally done to make administration easier,, compromises sometimes happen, and the user might be granted access through the group to resources that they do not need to access. Another reason is that sometimes mistakes are made. It is a fact of life that no one is perfect and that no system for allocating resources is going to get it right 100% of the time. When users are granted the capability to read a file, you can be sure, if the data contained in it is interesting enough, that they will do so.

Indeed, even if a user does not have appropriate access rights to a file, sometimes the user will try to get at interesting information anyway.

For these reasons, a good operating system provides auditing controls that enable you to look back after a security breach to try to determine who did what and where they did it. Unix (and its variants, such as Linux), Windows NT/2000/Server 2003, and Novell all provide features that enable you to record both successful and failed attempts to access resources. They all do it in different ways, and many of these auditing and security features are not enabled out-of-the-box; so if you have multiple operating systems on the network, it will be important that you understand each of them so that you can best enable and use these capabilities.

The Network As Target continue...

In this case, however, you need to be sure that the "hot spare" itself has not been compromised. Some malicious code can remain around for many months before causing problems. This is another good reason to use updated antivirus software on a regular, frequent basis.

Another way to protect servers is to use the tools that the operating system provides to protect some services. For example, you'd be a fool to place a directory on your system disk for use as an anonymous FTP site. The last thing you want is to have someone filling up all the space on your system disk. Most operating systems allow you to set quotas that define how much space a particular user account can use on a server's hard drives. Enforcing quotas can help prevent an attack that consists of consuming all the available space on a disk. In addition, you can set alarms to notify you when quotas are being used up at a rate that is faster than what you see during normal operations. It's then an easy matter to track down the source of the data coming into the server and to terminate the user process.

Additionally, protecting computers should also involve software that detects malicious code. Even home PC users are aware of the value of antivirus programs. There are so many vendors of this software that it would be pointless to attempt to list them here. However, when you do choose an antivirus program, there are some things you should consider when making a purchasing decision. For example, does the vendor respond quickly with updates to the software as new viruses are discovered? Does the software have the capability to remove the virus after it has been discovered? Does the software have the capability to scan floppy disks and files transferred to the computer through the network? Of these, the capability to quickly respond to new threats is perhaps the most important. However, your situation might dictate other factors that are more important. Note also that many firewall products now contain some type of virus-detection mechanism.

Using Tripwire

There are many programs you can use to help determine whether your system has been compromised. Tripwire is a very popular host-based DOS (HDOS) program that can be used for this purpose. Tripwire was originally developed in 1992 by Gene Kin and Dr. Eugene Spafford. The Academic Source Release (ASR) version of Tripwire can be downloaded for noncommercial use from Tripwire's Web site. In addition, Tripwire has created commercial versions of the software, including an enterprise manager program (Tripwire Manager) that uses SSL for communications and simplifies management of multiple servers and workstations.

Tripwire is based on the concept of taking a "snapshot" of system resources, such as files, directories, and, in the case of Windows NT, Registry settings. The information gathered by Tripwire is stored in a secure database and is used to compare a server later to determine whether changes have been made and what those changes were. A policy file allows the network administrator to control the types of data that Tripwire monitors and to prioritize certain events using a rule base. In addition, Tripwire can produce reports that make monitoring the system easier for administrators.

Currently, Tripwire runs on the following operating-system platforms:

• Windows NT 4.0, Windows 2000, Windows 2003, and Windows XP Professional
• Solaris (SPARC) versions 2.6, 7.0, 8.0, 9.0, and 10
• IBM AIX 4.3.3, 5.1, 5.2, and 5.3
• HP-UX 10.20, 11.0, llivl, 1 liuZ
• Several versions of Linux

Some of the things that Tripwire can monitor are specific to an operating system, whereas others (such as file types and sizes) can be monitored on all platforms. For example, here are a few of the items you can use Tripwire to monitor on Unix systems:

• Addition, deletion, or modification of files, along with file permissions, types, and sizes
• Inode number and number of links
• Owner and group IDs for files
• Modification timestamps and access timestamps

In addition, hash algorithms can be used to ensure the integrity of the contents of files. Tripwire supports several kinds of hashing algorithms, such as CRC-32, MD5, and the SHS/SHA algorithm, among others.

For Windows NT systems, the list that can be monitored includes the standard file components and things such as these:

• File attributes, such as archive, read-only, hidden, or offline
• Create and access times
• NTFS Owner SID, NTFS Group SID, and other NTFS attributes
• Addition, deletion, and modification of Registry keys and the values of those keys

These lists are not all-inclusive. For more information about acquiring an evaluation copy of Tripwire, visit the Web site www. . tripwire . com.

User Awareness and Training

Social engineering is a term used a lot lately to describe an easy method for gaining access into your network. Put quite simply, are the users of your network trained in security measures? A quick test is to simply have someone from your help desk call a user and ask him for his password. I would bet that in at least half of the cases the users will give out their passwords. A help-desk person shouldn't have to ask this type of question! Instead, if people at your help desk need to access a user account, they can notify the user that they are changing the password temporarily and will notify the user when to reset the password to a value known only to the user.

A password policy should also be in effect to ensure that common names and words are not used. Yet, one must be careful to avoid making passwords so difficult that users have a hard time remembering them. Most operating systems have the capability to keep a history list of passwords to prevent their reuse within a specified amount of time. You'll also find that you can usually set a minimum and maximum password length.

Social engineering also can involve dumpster diving. How secure are the printouts that you throw in the trash can? Do you have paper shredders (and a security policy dictating their use) in place? Even Hollywood stars know that much useful information can be obtained from a trash can! This goes not just for paper materials. When you decommission old tapes or old computer hard drives, do you take the time to destroy any data that is stored on them? It may be well and good to donate old computers to nonprofit organizations or schools, but it's also a good idea to reformat the hard drives and reinstall the operating systems before you do so. Tapes can be made useless by various means, including bulk tape erasers that zap the contents in just a few seconds.

Staying on Top of Security Issues

Your network will never be secure unless you make an effort to keep up-to-date with the latest discoveries concerning security issues. There are many good sites on the Web that you can use as resources to help you get the latest information as well as advice on how to better secure hosts and networks. Keep in mind that those who would do harm to your network are usually one step ahead of you. It's a continual catch-up game. The quicker you find out about a problem, the quicker you can take precautions to protect your network.