Intrusion Detection FAQ: An analysis of SQL.Spider-B (Digispid.B.Worm, Spida, MSSQL Worm and SQLSnake)

This write-up gives an analysis of SQL.Spider-B (a.k.a. Digispid.B.Worm, Spida, MSSQL Worm and SQLSnake); the Java variant of the worm targeting hosts running Microsoft SQL Server with a blank sa account password.

Destination and reports for port 1433/TCP (Source:

This graph illustrates the ongoing scans for hosts listening on port 1433/TCP (Microsoft-SQL-Server). Blue represents the number of destinations and red represents the number of reports for each day of November 2002. This worm was first reported on May 20, 2002. At the time of this write-up, more than six months later its presence is still demonstrated. With 13581267 reports in November 2002, port 1433/TCP attacks rank third on the Internet Storm Center Top 10 Ports list.

First a description of the worm code, or better codes. The worm includes a total of ten files, all with a specific function.

This diagram shows the major three parts of the worm. The main code (Red), co-ordinates the various functions of the worm: gathering information (Yellow) and propagation to other hosts (Green).

Main code
sqlprocess.js / timer.dll:
When the worm infects the victim, sqlprocess.js is executed with a eight character password as argument. This password is random generated by the attacker and signals the worm to enter a 'setup' phase:

To start the worm after the victim reboots, properties in the registry are changed to start sqlprocess.js with the init argument via the NetDDE service. By default NetDDE starts manually, hence the startup type is set to automatic (DWord: 2). The third line of code registers timer.dll silently. Timer.dll is a library used by sqlprocess.js for sleep functions.

By default the MS SQL Server 7.0 client uses Named Pipes for communication, which fails any attempt to infect a victim. When MS SQL Server 7.0 is installed, the registry is changed to force the client to use TCP/IP sockets. The MS SQL Server 2000 client is able to change protocols automatically and does not require changes to the registry.

The Registry Editor (regedt32.exe) is copied to %SystemRoot%. This is a secondary mechanism to prevent re-infection of the victim: sqlinstall.bat quits when it finds regedt32.exe at this location. Note: re-infection is already unlikely since the sa account password is changed as part of the infection.

To gather information the worm uses:
  • ipconfig /all to gather information about active network interfaces. Information includes NIC description and MAC address, IP address, subnet mask and gateway address, WINS and DNS server addresses.
  • sqldir.js to extract database names, table names (with row count) and column names from the local MS SQL Server databases.
  • pwdump2 to extract password hashes from the local SAM database. The word local is relative here. When MS SQL Server is installed on a domain controller, the 'local' SAM database will contain account information for the complete domain.
This information is redirected to the temporary file send.txt. Contents of this file and the assigned sa password are send to (See: Appendix A). To hide traces send.txt is deleted by the 'destroy' function described later in this section.

When the victim reboots, the NetDDE service starts sqlprocess.js with the init argument:

This signals the worm to start sqlprocess.js in a new shell without any arguments, the original sqlprocess.js quits after a timeout of 60 seconds. Started without any arguments, sqlprocess.js skips setup and information gathering and starts scanning for victims without delay.
Most for the remaining code is used for a mechanism to generate a weighted list of numbers:

The numbers in array sdataip are used as first octet in the IP range to scan. Each of these numbers is added to statarray as many times as the corresponding number in sdataf dictates (See: Appendix B).

The first octet is chosen from a statarray entry. The entry is chosen at random between 0 and 1235. With only 1035 entries in the array, there is a 16.26% chance that the first octet is chosen directly from a random number between 1 and 223. When the first octet turns out to be from the loopback address range or private ranges, the worm skips scanning and re-enters the scan procedure.

The second octet is randomly chosen between 0 and 255. Third and fourth octets are in the range 1.1 to 255.254. Services.exe shuffles the fourth octet to give the scan a more random look. The worm uses services.exe with the following options:

The remaining code checks output file rdata.txt for occurrences of 1433/TCP, indicating a listening MS SQL Server. When found, the IP address is derived from rdata.txt and sqlinstall.bat is started in attempt to infect the victim. A random password is generated to change the sa and Guest account password by sqlinstall.bat.

Function destroy deletes a file, creates a null file with the same name and deletes the null file again. This makes it hard to recover the temporary file. This function is used on send.txt (gathered information), rdata.txt (Fscan output) and .ok / .fail (status files generated by sqlinstall.bat).

Function password is a simple loop appending a random lower-case character and a random number to a string. After four loops, an eight-character string is returned to the caller. The string is used as password for the sa and Guest account. Some sample passwords: g0b6x2f1, u2q8k3i5, a4j1s4c4, f4n8h2y8 and x2n9k5n2.

Function random returns a random number in the range min_number to max_number.

This is the 30-day evaluation version of Command Line E-mailer by SureShot. This is no limitation, since information is gathered and sent only at the initial infection.

pwdump2.exe / samdump.dll:
The worm uses the updated version of Pwddump2 by Todd Sabin. This version is able to extract hashes from 40-bit and 128-bit encrypted SAM databases and the Active Directory. Another new feature is that it is able to lookup the process id of LSAss (Local Security Authority sub-system), making it ideal for use in scripted environments. Sample output from Pwdump2:

An explanation by Todd Sabin:
'It uses a technique known as DLL injection. In general, one process (pwdump2.exe) forces another process (lsass.exe) to load a DLL (samdump.dll) and execute some code from the DLL in the other process's (lsass.exe's) address space and user context. In this specific case, once samdump.dll is loaded into lsass, it uses the same internal API that msv1_0.dll uses to access the password hashes. This means it can get the hashes without doing any of the 'hard' work of pulling them out of the registry and decrypting them. The program neither knows nor cares what the encryption algorithms or keys are.'

This is a renamed copy of Fscan by Foundstone, inc. With the options used by the worm Fscan takes up to 100% processor time. During the network scan a three-way handshake sets up and gracefully tears down TCP session. Snort's portscan pre-processors and other IDSs will detect this scan from this worm. Sample output from Fscan:

Echo's database names, table names with a row count and column names from the local MS SQL Server databases. Default databases 'Northwind' and 'pubs' are excluded. Sample output from sqldir.js (using /r3s argument):

Used to start sqlprocess.js in a new shell with the random password as argument.

Used to execute a shell command on the victim using the xp_cmdshell extended stored procedure. When xp_cmdshell is executed, it runs on the victim in the context of the MS SQL Server service account, by default the local or the domain administrator account.

Sqlinstall.bat starts with two arguments: the victims IP address (%1) and password (%2).

Sqlinstall.bat checks if the password for the sa account is blank and the victim supports the xp_cmdshell extended stored procedure. It instructs the victim to echo %1 (victim's IP address) and see if the IP address is returned. If errorlevel returned by find "%1" is not 0, sa account password is not blank or the xp_cmdshell extended stored procedure is not supported and the batch file quits (See: Network trace).

The Guest account is activated (not added!) and the password is changed to the password generated by sqlprocess.js. The worm then tries to add to Guest account to local group 'Administrators' and global group 'Domain Admins'. Adding to the last group is only possible when the victim is a domain controller. Finally the worm sets up an IPC connection to the victim using the Guest account, but in the security context of the Administrators/Domain Admins group.

A second check is performed to find out if the remaining batch file has to be executed. Csript.exe (Command shell part of Windows Script Host) must exist and regedt32.exe (Registry Editor) may not exist in admin$.

Unhide local worm files and copy them to the victim. When done remote and local file attributes are set to 'hidden' to provide some cover. This procedure is executed for each of the ten worm files:

After the files are copied to the victim, the Guest account is disabled and removed from local group 'Administrators' and global group 'Domain Admins'. The password for the sa account is changed to the password generated by sqlprocess.js. Sqlprocess.js is started by run.js with the new password as argument, using the isql client tool and the sp_password stored procedure. Finally, the worm disconnects from the victim. The victim becomes attacker and the process starts all over again.

Network trace:
This trace is captured on an isolated network, using Snort 1.9.0 in packet logger mode. Attacker ( and victim ( are running MS Windows 2000 SP3 and MS SQL Server 7.0 in default setup. The password for the Administrator account is dissimilar on both hosts to rule out influence of this account. The Guest account is deactivated and password for the sa account is left blank.

As mentioned in the code description, the worm excludes the public network range Sqlprocess.js is changed to scan only the network:

A full trace generates a file over 4.000 lines in length. To keep the length of this write-up within proportion, only output from the first and most significant command is included: cscript sqlexec.js %1 sa "" echo %1|find "%1"

The victim in the case is vulnerable. The attacker can login using the sa account and is able to start the xp_cmdshell extended stored procedure of the victim:

The three-way handshake as seen in any TCP session setup.

The first byte in the datagram is the Tabular Data Stream (TDS) packet type 0x10, indicating a MS SQL Server 7.0 client login packet. The next three bytes tell this the last packet (0x01) with a size of 0x0139 (313 bytes). Packets larger than 512 bytes are split-up and have the value of the second byte set to 0x0. Next is off-set and length information for client name (W2KWORKSTATION), username (sa), encrypted password (), application name (Microsoft Windows Script Host), server name ( and library name (OLEDB).

The victim responds with packet type 0x04, indicating a server response. Besides changing environment settings for database, language, sort order, Unicode locale ID, Unicode comparison style and blocksize, it sends an acknowledgement (0xad) indicating a successful login.

Now that the attacker is logged in to MS SQL Server with the sa account, its sends a query (packet type 0x01). The query reads: xp_cmdshell 'echo'. This is correct, since the %1 variable represents the victim's IP address.

Again the victim responds with a packet type 0x04. The results send back are preceded by 0xfe for process done, a normal response when the query contained a stored procedure. Output returned to the attacker is, equal to what the echo command in a DOS shell would do.

Finally, the attacker gracefully tears down the TCP session. This procedure is repeated for each command that is using the xp_cmdshell extended stored procedure. Once Guest account privileges are elevated to Administrators/Domain Admins level, the attacker switches to the 'regular' server message block (SMB) protocol for file transfers. By the time the Guest account is to be restored to its default configuration, the attacker uses the xp_cmdshell extended stored procedure again.

Snort 1.9.0 in its default configuration and rule set is able to detect this traffic:

This rule is triggered by the occurrence of a Unicode xp_cmdshell string inside the datagram (third packet in the trace). The network trace shows the attacker has to complete the three-way handshake and MS SQL Server login before the xp_cmdshell query is sent. The three-way handshake makes it hard (not impossible) to spoof the source address, while the MS SQL Server login implies that the attacker at least has a valid account. Together they make the alert more than reliable to detect an attack from the SQL.Spider worm. For each successful infection Snort 1.9.0 logs ten alerts like this example:

Potential victims:
In all tests the attacker is an infected host running MS SQL Server 7.0. The victims are running MS SQL Server 7.0, MS SQL Server 2000 or their derivatives MSDE 1.0 and MSDE 2000. Again, the Guest account is deactivated and the sa account password is left blank.

Microsoft SQL Server 7.0
Installation of MS SQL Server 7.0 does not require a password to be set for the sa account. During infection, the worm files are copied and executed within seconds.

Microsoft SQL Server 2000
By default MS SQL Server 2000 uses Windows Authentication Mode and is not susceptible to this worm. However, it is possible to change to Mixed Mode during or after installation (This behavior was changed in Service Pack 1). With a blank password for the sa account and running in Mixed Mode, MS SQL Server 2000 is susceptible to the worm.

Microsoft Data Engine 1.0
Installs with:
  • Microsoft Visio 2000 Enterprise Edition
  • Microsoft Project Central Server
  • Add-on for Microsoft Visual Studio 6.0
MSDE 1.0 installs with a blank sa account password by default, listens at port 1433/TCP and supports the xp_cmdshell extended stored procedures. The worm files are copied without problems. MSDE 1.0 does not include the isql client tool. Hence, the password for the sa account is not changed, nor is sqlprocess.js executed on the victim. When this part is removed from the code, the worm would be able to infect MSDE 1.0.

Microsoft SQL Server 2000 Desktop Engine
Installs with:
  • Microsoft Visio Enterprise Network Tools (VENT)
  • Microsoft Office XP versions that include Microsoft Access 2002
  • Add-on for Microsoft Visual Studio .NET
MSDE 2000 uses Windows Authentication Mode by default, but authentication mode can be changed after installation. Like with MSDE 1.0, the isql client tool is not included.

A potential victim must comply with the following factors. Any failure will prevent at least parts of the worm from being executed successfully.

MS Windows operating system
  • MS Windows NT 4.0, MS Windows 2000 or MS Windows XP (MS Windows .NET?).
  • Existing SQLAgentCmdExec (SQL.Spider-A) or Guest (SQL.Spider-B) account.
  • Windows Script Host (WSH) installed.
MS SQL Server
  • MS SQL Server 7.0 or MS SQL Server 2000 running Mixed Mode authentication.
  • sa account with a blank password.
  • isql client tool available.
  • sp_password stored procedure and xp_cmdshell extended stored procedure available.
  • TCP/IP Net Library available (MS SQL Server 7.0 client only).
  • Connection to port 1433/TCP allowed (inbound for infection, outbound for propagation).
  • Depending on the victim's operating system, connection to port 139 or 445/TCP allowed (inbound for infection, outbound for propagation).
Worm removal: To remove the worm from an infected host perform the steps in the order described below. Reboot host to remove remaining processes from memory.

  • Apply ingress and egress filtering for ports 139, 445 and 1433/TCP.
  • Always set a password on an account. Enforce a strong password policy and implement SYSKEY to provide additional security. SYSKEY is used to enable 128-bit encryption of the SAM database in Windows NT4.0 and was first distributed with Service Pack 3. Although SYSKEY does not prevent the worm from stealing password hashes, it will make password hashes much harder to decrypt.
  • Rename built-in accounts like Administrator and Guest. Although names for the accounts can be traced by their RIDs, it stops simple scripted attacks like this one.
  • Only run services that are required to run and apply security patches when they become available.
  • Install anti virus software and keep signature files up-to-date.
  • Scan the local network to reveal 'hidden' MS SQL Server and MSDE installations.
  • As a precaution, block all email to

Appendix A:
Name lookup (Type=MX):   MX preference = 10, mail exchanger =   MX preference = 10, mail exchanger =   nameserver =   nameserver =   internet address =   internet address =   internet address =   internet address =

Whois (APNIC Whois Database)
inetnum: -
netname:   NETBLK-SPHNET
descr:   Singapore Press Holdings Ltd./Information Technology Division
descr:   Singapore Press Holdings Ltd.
descr:   Level 5, News Centre,
descr:    82, Genting Lane,
country:    SG
admin-c:    WC13
tech-c:   WC13
mnt-by:    MAINT-NULL
changed: 930929
source:    APNIC
person:   Wing-Kheong Choo
address:   Singapore Press Holdings Ltd
address:    Information Technology Division
address:   Level 5, News Centre,
address:    82, Genting Lane
address:    SG
phone:   +657401695
e-mail:    teohh@SOLOMAN.TECHNET.SG
nic-hdl:    WC13
remarks:   invalid e-mail address
mnt-by:    MAINT-NULL
changed: 19960129
source:    APNIC

Appendix B:

Ronny Rietveld