As you probably remember all too well, on January 25, 2003, servers around the world running Microsoft SQL Server 2000 were the targets of an insidious virus known as SQL Slammer. The SQL Slammer virus is a self-propagating worm that exploits a buffer-overrun condition on SQL Server machines. A compromised server can potentially run arbitrary code on behalf of the attacker.
Unfortunately, the attack wasn't limited to servers running SQL Server 2000. Computers running the Microsoft SQL Server Desktop Engine (MSDE)—a scaled-down, redistributable version of the SQL Server 2000 database engine—were also vulnerable to the attack. What appeared to be a reasonably isolated and easy-to-identify attack became a widespread disaster because MSDE is an optional component that's included with a variety of Microsoft and third-party products, such as Microsoft Office XP Professional and Microsoft Visio 2002 Professional.
MSDE isn't installed by default on these desktop products, but how would you know if it was? One answer is scripting. When administrators think about scripting, they often think in proactive terms. For example, they think of scripting as a tool for automating common systems administration tasks, such as managing user accounts, asset tracking, and configuration management. However, scripting can play an equally important role when you need to react to an unforeseen crisis like SQL Slammer. To demonstrate, I'm going to use the SQL Slammer incident to show you how to use scripting to assess and mitigate damage.
Understanding a Crisis
Before you can write a script to deal with the SQL Slammer worm or any disaster, you have to know something about the problem. You can't write a script to shut down ports, kill processes, disable services, replace files, or perform some other task without some knowledge of the disaster with which you're dealing. Any information you can gather from the CERT Coordination Center (CERT/CC—http://www.cert.org), the vendor, network traces, and other sources can help you determine where to focus your efforts as you try to identify and contain the problem. For example, consider what administrators have learned about the SQL Slammer worm:
- The worm can potentially infect all unpatched versions of SQL Server 2000 Service Pack 2 (SP2) and earlier, including all versions of MSDE SP2 and earlier.
- The worm exploits a known buffer-overrun condition that was discovered and reported to CERT in July 2002. Although Microsoft released a patch that addressed the problem in July 2002, many systems (primarily computers running MSDE) went unpatched for two primary reasons. First, the steps required to apply the patch were far too difficult in some cases (e.g., the older patch required too many manual steps). Second, many administrators were unaware of the scope of the problem as it relates to the number of computers running MSDE in their environments.
- The worm targets the SQL Server Resolution Service, which listens on UDP port 1434 (i.e., port ms-sql-m in netstat.exe's output).
- After a computer becomes infected with the SQL Slammer virus, the memory-resident worm tries to propagate itself by sending 376-byte payloads to UDP port 1434 at random IP addresses.
Identifying the scope of the problem was one of the most challenging aspects of the SQL Slammer virus, which explains why Microsoft quickly provided tools to help identify computers running SQL Server 2000 and MSDE. Could you have used a script instead? You bet! You can use a script such as IdentifySQLComputers.vbs to identify the at-risk computers, then contain the crisis with a script such as DisableSQLService.vbs.
Assessing the Crisis
IdentifySQLComputers.vbs, which Listing 1 shows, is largely based on the series of scripts I presented in "Remote Administration with WMI," February 2003, http://www.winnetmag.com, InstantDoc ID 37596. This script's purpose is straightforward. Based on a list of computer names you store in a text-based input file, the script uses Windows Management Instrumentation (WMI) to determine whether the target computers have SQL Server 2000 or MSDE installed. Because IdentifySQLComputers.vbs uses WMI, the target computers must be running WMI for the script to work. In Windows 2000 and later, WMI is a core component in the OS, so WMI is already installed and enabled. However, if you have computers running Windows NT 4.0 or Windows 98, you need to install and configure WMI on those computers to use IdentifySQLComputers.vbs.
The input file, Computers.txt, contains one computer name per line. For each computer in the file, the script displays one of the following three message types:
- GOOD—indicates that neither SQL Server 2000 nor MSDE are installed on the target machine.
- BAD—indicates that SQL Server 2000 or MSDE is installed on the target machine.
- ERROR—indicates that the target computer couldn't be reached. Possible causes include an invalid computer name in the input file, network connectivity problems (e.g., the computer didn't reply to a ping), or the computer isn't WMI-enabled.
Let's look at how IdentifySQLComputers.vbs works. As Listing 1 shows, I begin the script by defining the ForReading constant and initializing two variables: strInputFile and strServiceName. The strInputFile variable's value points to the Computers.txt input file. The strServiceName variable's value is the internal service name (i.e., the name the OS uses internally) of the service for which you're checking. In this case, I want to check the PCs for the SQL Server service because it starts the SQL Server Resolution Service, which is the SQL Slammer worm's target. The internal service name of the SQL Server service is MSSQLSERVER. (You can find a service's internal service name in the Service name field of the service's General Properties page in the Microsoft Management Console—MMC—Services snap-in.)
Next, I use the FileSystemObject's OpenTextFile method to open Computers.txt. OpenTextFile returns a reference to a TextStream object, which I assign to the variable named objTextStream. To retrieve this file's contents, I use the TextStream object's ReadAll method to read the entire input file into memory. I then call the VBScript's Split function and use the file's carriage return/line feed (which vbCrLf represents) as the delimiter. The Split function chops up the in-memory file and returns an array that contains the computer names. I assign the array to the arrComputers array variable, then close the input file.
Next, I set a reference to the WScript object's StdOut stream. This code is optional; I use it only for convenience. By setting a reference in this way, I can use the shorter StdOut.WriteLine notation instead of the more verbose WScript.StdOut.WriteLine notation to send text to the console. If you use the StdOut property, you must run the script with the cscript.exe host. You can't use wscript.exe because it doesn't understand STDIO streams (i.e., STDIN, STDOUT, and STDERR).
After setting the reference to the StdOut variable, I create a WshShell object. Later, I use this object's Exec method to ping each computer before trying to connect to it. The use of the Exec method highlights another script dependency. Microsoft added the Exec method to Windows Script Host (WSH) 5.6. Thus, you need to run the script on a computer that has WSH 5.6 installed. Windows Server 2003 and Windows XP include WSH 5.6 by default. If you're running the script on a Win2K, NT 4.0, or Win98 computer, you need to upgrade to WSH 5.6.
Next, I use a For Each...Next statement (aka For Each loop) to perform the bulk of the work. Before entering the loop, I enable VBScript's error-handling mechanism: the On Error Resume Next statement. This statement does exactly what it says—that is, if the script encounters a runtime error (the On Error part), the script continues to the next statement (the Resume Next part) instead of aborting. Using On Error Resume Next lets me use VBScript's Err object to check for and handle errors gracefully.
The For Each loop iterates through the arrComputers array I created earlier. For each computer listed in the input file, the script performs the following three steps:
Step 1: Determine computer availability. To determine the target computer's availability, the script uses WshShell object's Exec method to ping the computer, as callout A in Listing 1 shows. Exec returns a reference to a WshScriptExec object, which the script uses to read and capture the Ping command's output. I use the VBScript LCase function to convert Ping's output to lowercase and store the output in the variable named strPingResults.
To evaluate output in strPingResults, the script uses an If...Then...Else statement and VBScript's InStr function. If the output contains the substring reply from, Ping succeeded, and the script proceeds to the code at callout B in Listing 1. If strPingResults doesn't contain the substring reply from, Ping failed, and the script jumps to the Else clause that callout C in Listing 1 highlights. The code in the Else clause sends the \[Ping failed\] error message to the console.
Step 2: Connect to the computer. To connect to WMI on the target computer, the script uses VBScript's GetObject function combined with a standard WMI connection string (also called a moniker), as the code at callout B shows. If WMI connection strings look foreign to you, I encourage you to read "WMI Monikers," May 2001, http://www.winnetmag.com, InstantDoc ID 20401. GetObject returns a reference to the WMI Scripting Library's SWbemServices object, which represents an authenticated connection to WMI on a local or remote computer. The variable named objWMIService is initialized with the SWBemServices reference that GetObject returns.
Following the connection attempt, the script uses a second If...Then...Else statement and the Err object's Number property to determine whether the connection succeeded. If Err.Number is anything other than zero, the WMI connection failed and the script sends an appropriate error message to the console. If Err.Number is zero, the connection succeeded and the script proceeds to the Else clause in the second If...Then...Else statement.
Step 3: Retrieve the service. The script uses the WMI Scripting Library's SWbemServices object reference (objWMIService) to retrieve the target service. More to the point, the script uses the SWbemServices object's Get method, which accepts three optional parameters: an object path, some flags, and a named value set. In this script, only the first parameter is necessary. The object path parameter that the script passes to Get takes the form
where Class refers to the WMI class that models the target resource, Key is the class property that identifies unique instances of the managed resource, and 'Value' is the value that uniquely identifies the instance of the resource you want WMI to retrieve. For example, consider the following object path:
Win32_Service is the class, Name is the Key property for the Win32_Service class, and 'MSSQLSERVER' is the unique instance of Win32_Service you want Get to retrieve. The only difference between this sample object path and the object path in IdentifySQLComputers.vbs is that, in IdentifySQLComputers.vbs, the script stores the name of the target instance in the strServiceName variable. Storing the target instance in a variable makes the script easy to adapt if you want to use the script for different services.
In these sample object paths, you might have noticed that I've enclosed the value in single quotes. You must enclose the value in either single or double quotes. In VBScript code, I tend to use single quotes because they're easier to work with. For example, you don't have to escape embedded single quotes as you do double quotes.
Following the call to the Get method, the script uses a third If...Then...Else statement and the Err object's Number property to determine the method's outcome. If Get fails, which is a good outcome in this case, Err.Number will be a non-zero value, which means that the MSSQLSERVER service isn't installed on the target computer. If Get succeeds, the MS-SQLSERVER service is installed on the target computer. In both cases, the script sends an appropriate message to the console.
Figure 1 shows the results from running IdentifySQLComputers.vbs in my lab. Of the seven computers listed in the output, only one computer (highlighted) is suspect. Had I not known the computers named dellsx260a and wdywtgt were offline and that dellxps is an XP Home Edition computer that doesn't permit remote WMI connections, I would have needed to investigate why these computers reported an error. In this case, the errors are intentional.
To use IdentifySQLComputers.vbs in your environment, you simply need to create a text file that contains the list of target computers in your environment. If you want to avoid modifying the script altogether, name the text file Computers.txt and save it in C:\Scripts. Otherwise you'll need to change the value assigned to the strInputFile variable.
Here's an extra-credit question for you: In a script, can you use Active Directory (AD) as a source for computer names instead of a text-based input file? Absolutely! You might be surprised at how trivial the changes are. To learn about those changes, read the Web-exclusive sidebar "Using AD to Retrieve Computer Names," InstantDoc ID 39765.
Containing the Crisis
Now that you've determined which computers are running SQL Server 2000 or MSDE, the next step is to contain the crisis. To that end, you want to stop and disable the MSSQLSERVER service on those computers that IdentifySQLComputers
.vbs reports as BAD. Disabling the service helps limit the worm's propagation and buys you time to figure out how to eradicate the virus and patch infected machines.
At this point, you've already written a large part of the containment script DisableSQLService.vbs, which Listing 2 shows, because DisableSQLService.vbs uses a lot of the same code as IdentifySQLComputers.vbs. You simply need to add the logic to stop the service and set the service's startup type to Manual or Disabled.
Callout A in Listing 2 highlights the code that stops the service. I first use the Win32_Service AcceptStop property to determine whether the target service is running. If AcceptStop returns the TRUE value, I use the Win32_Service's StopService method to stop the service. Next, I use this method's return value to determine whether StopService succeeded and send an appropriate message to the console.
As callout B in Listing 2 shows, I use the Win32_Service StartMode property to determine whether the target service's startup type is set to Disabled. If not, I use the Win32_Service ChangeStartMode method to do so. I use the method's return value to determine whether ChangeStartMode succeeded and send an appropriate message to the console.
I ran DisableSQLService.vbs against the same set of computers as IdentifySQLComputers.vbs. As Figure 2 shows, you can see that DisableSQLService.vbs successfully stopped and disabled the MSSQLSERVER service on the tmtowtdi computer.
It's Not If, But When
Unfortunately, it's only a matter of time before some yo-yo with too much time on his or her hands releases the next bad virus. As IdentifySQLComputers.vbs and DisableSQLService.vbs demonstrate, scripting is a powerful tool you can use to assess and contain the crisis while you wait for a vendor's response to that virus. By investing the time to learn how to write these types of scripts, you'll be in a much better position to react quickly and effectively.