Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

TSQL Virus or Bomb?

By Joseph Gama,

Protecting against TSQL virii, worms and time bombs

Yes, the first virus made in TSQL has been created! But even more dangerous, worms can be made applying similar but simpler techniques. What could be worse than that? Time bombs hidden somewhere in the code, waiting…

Screenshots of the TSQL virus in action


Before we get into the facts, some definitions:


Definition of virus

"A computer virus is a self-replicating program that invades and attaches itself to computer programs. Virii can interfere with the operations of their host program or alter operations of their host computer."

Definition of worm

"A worm is a program whose primary task is to move copies of itself between computers connected by network. Though worms do not try to cause damage to a computer, by causing copies of itself to be made a worm can disrupt the operation of computers and computer networks."

Definition of time bomb

"A time or logic bomb is a specific program feature. A program with a time bomb will "explode" upon the occurrence of a certain event - often the occurrence of a predetermined date. The explosion can be anything from a display of messages on the computer screen to the complete wipe of the computer's system."

The most complex of those three entities is the virus, which requires intrusion, execution and replication of its code. The intrusion is theoretically impossible in a SQL Server database properly secured. As TSQL has no "low level" features, port scanning and intrusion are not possible.

How can the virus infect a database?

It will have to be executed by a user. There are three possible scenarios:

  • An unhappy user deliberately executes the code (probably before being laid off).
  • A user will execute some code of uncertain origin containing the virus.
  • An intruder gained access to the database and executed the viral code.

TSQL virii are not a threat

This is very clear from the above scenarios. Scenario a) requires more effort and is more likely to be detected than a time bomb. It makes no sense to do an inside job that is so visible and complicated. Scenario b) would be possible if the user had permissions to run the code, knowing enough TSQL as to create a stored procedure but not enough as to understand what the code really does. This is very unlikely to happen. Scenario c) is obviously very far from reality. An intruder would go through a lot of work to gain access to the database and dropping some tables or the entire database could be done immediately, why wait?
But there's more: TSQL data types used in stored procedures can't go over 8 Kb. This is a great obstacle because the virus code takes some room and so, the virus can only replicate to "small" stored procedures, which makes it more visible and easier to detect.

TSQL worms are not a threat

A worm would face the same problems that a virus would but it would be detected much faster and easily stopped. The "standard" worm that replicates constantly would be simply coded as a stored procedure that makes copies of itself with a random name. That is easy to create and easy to remove. The best approach would be a stored procedure that consumes resources by creating, filling with data from system tables and then dropping lots of temporary tables constantly. Why bother with a stored procedure that lays among others but with code that wouldn't be so easy to disguise when this code could be hidden in some other stored procedure?

Conclusion: time bombs are the most real and dangerous threat

The three scenarios for delivering a virus are perfectly possible and quite easy and effective for a time bomb. Let's rewrite them for this situation:

  • An unhappy user deliberately hides the time bomb code in a section of a big stored procedure.
  • A careless user copies code from an uncertain origin that has the time bomb hidden.
  • An intruder was able to gain access to the database and, instead of causing an immediate destruction, the intruder decided to place a time bomb that would slowly and randomly corrupt data so that even the backups would be storing corrupted versions of the database.

This is the most dangerous and most realistic attack that I can think of, after all bad coding can have an impact on the server as negative as a sneaky and pernicious worm.

How to prevent TSQL virii, worms and time bombs' attacks

  • No guest accounts and no accounts with null passwords.
  • Make sure all user passwords are safe.
  • User permissions and roles are very effective when used wisely.
  • Check database objects regularly.
  • Do not allow user passwords that are not encrypted.
  • Check the system databases and objects not only for changes but also for the inclusion of new objects that could have dangerous code. A user could create a system stored procedure by knowing of an exploit before it was patched and obtaining the necessary rights with privilege escalation. Later the user could run it from any database. Another possibility would be to use the tempdb to store or execute malicious code.

Practical solutions for each of the above ideas

1) Carefully examine user permissions and roles. Restrict access to everything but the necessary for the user to work. Look for null or non encrypted passwords: SELECT [name], dbname, [password],CONVERT(VARBINARY(256), password) FROM syslogins
2) Look for size, complexity and effectiveness of user passwords. The best solution is to create random passwords for the users but forcing the users to have long passwords is fine too. You can use some free tools from to assist you.
3) Create a huge and unreadable SA password. Make sure that your application is safe from SQL injection and be careful with granting permissions and managing roles. Carefully attribute roles and permissions. Even the "public role" can be dangerous.
4) Check stored procedures, UDF's and triggers for changes using CRC32 or checksum.
Or changes in size:
5) See 1)
6) See 4)

How to detect data corruption

1) Use the Database Consistency Checker (dbcc).
3) Compare tables from backups with new ones if they seem to have changed drastically.
4) Create views to verify that the numeric data is within "normal parameters"; look for max and min values and sums of values to find possibly corrupted data.
5) If the data is alphanumeric look for ASCII or UNICODE codes that should not be there, empty records or oversized ones'.
6) Look for Nulls, zeros and repeated values.
7) Use CRC32 to validate historic data.

How to detect intrusion

1) Enable login auditing in SQL Server.
2) Use triggers to track changes.
3) Explore the transaction log with a commercial tool.

The softest spot of SQL Server can be Windows

Windows NT/2000 login always have access granted to SQL Server. This is an extra security risk because breaking into Windows will provide access to SQL Server and it might be easier (in very particular situations) to crack Windows security than SQL Server's. Windows 2000 is safer than NT and even NT has very tight security if properly installed and with the latest service packs and patches applied. The problem arises from the fact that there might be one machine with SQL Server but dozens of machines in the network can reach it and the permissions are loose. It is easier to find out the password for one out of dozens of machines than the one for the machine with SQL Server. It is also possible to have one of the users download or receive by email a Trojan or to run ActiveX in a web page or any other technique to get access to a machine and, from there, attack SQL Server. Win 9x/ME is very unlikely to be used as a server but although it does not provide access granted to SQL Server it can be hacked and a brute force attack, sniffing or even key logging are al possible.

Avoid mixed mode and Windows 9x/ME

Usually that is not the case with most real life database implementations, having a certain number of users, databases and database objects related to each other in a way that requires careful management in order to allow access without compromising security. Windows authentication is the recommended security mode, not only because of Windows architecture but also because login names and passwords are not sent over the network. If the OS is not NT/2000 then mixed mode has to be used but Windows 9x/ME have some many security flaws that they should be avoided at all cost!

Do not be permissive with permissions

a) Each database has specific user accounts, do not let users access databases they really have no need for.
b) Do not provide users with permissions and ownership of objects in the database that they really have no need for.
c) Do not allow one login to have associated users in different databases if several people share that login, unless absolutely necessary. Splitting the group of users into smaller groups each with a different login would be safer and easier to manage in the future.

In case of doubt, search the code

It is very simple to create code to look for potentially dangerous keywords in stored procedures, triggers and UDF's. The following example code looks for "EXEC" in all stored procedures.

DECLARE @i int, @j int, @current_proc varchar(255),@current_text varchar(8000)
DECLARE _Cursor CURSOR FORSELECT,c.textFROM sysobjects o INNER JOIN syscomments c ON
WHERE o.type='p' and o.category=0 and encrypted=0
INTO @current_proc, @current_text
set @i=0
lblAgain: set @i=CHARINDEX('exec',@current_text,@i)
set @j=CHARINDEX(CHAR(13),@current_text,@i)-@i
IF @j<0
set @j=datalength(@current_text)-@i+1
IF @i>0
print @current_proc
print ' '+SUBSTRING(@current_text, @i, @j)
SET @i=@i+1
GOTO lblAgain
FETCH NEXT FROM _Cursor INTO @current_proc,@current_text


Total article views: 5499 | Views in the last 30 days: 1
Related Articles


Storing passwords securely


Setting Database password and permission

SQL Server Database password - Guru


Mask Password

Password stored in plain text


current sessions in database

current sessions in database


currently running stored procedure name

currently running stored procedure name

sql server 7