Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSSQL Table disappear

Posted on 2009-07-15
16
424 Views
Last Modified: 2013-12-13
HELLO,
A MSSQL Server that handles several queries daily,
last night on of my tables just got empty.... I have no stored procedure that could do that or what ever,
What could it be ?
I am thinking of injection, somehow, i dont really know because all users that access the sql via website are restricted and do not have delete operation enabled
0
Comment
Question by:netwhw
  • 8
  • 5
  • 2
  • +1
16 Comments
 

Author Comment

by:netwhw
ID: 24857037
Its a MSSQL 2000 Website
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24857066
there is a free log analyzer for sql server 2000
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

use it and find out which statement got your table empty
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857095
well, every transaction is written in transaction log file, if you are talking backup of transaction log than there is a chance to restore transaction log backup until the exact time you doubts for deleting records. one easy method is provided above, use red-gate tool which is free for SQL Server 2000. other than these, there is no way to do so.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:netwhw
ID: 24857169
But have you ever seem that ?
0
 

Author Comment

by:netwhw
ID: 24857182
A table complelty wiped ?
How can i check if my server has been compromised ?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857184
>>But have you ever seem that ?<<

didn't get you...
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857196
red-gate can help you to recover data but can't help you to check whether you are injection affected or not, that you will have to find out with few different ways.

--check you logs (SQL Server log and OS log)
--keep watch on your transaction log file
0
 

Author Comment

by:netwhw
ID: 24857220
Can you guide me on that ?
--check you logs (SQL Server log and OS log)
--keep watch on your transaction log file

BEcause, i had a autobackup at 2 am, and at 2:07 the table was wiped.
I dont know, could it be a malfuncition ?
0
 

Author Comment

by:netwhw
ID: 24857226
I already recovered the data, but my website is offline for now
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857244
go to your log in control panel and check whether any failed attempt to login was happened or not.
0
 

Author Comment

by:netwhw
ID: 24857267
No, it doesnt show any failed logins on my logs
0
 

Author Comment

by:netwhw
ID: 24857284
Do you can think of any other place to search ?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857366
I always used to see logs in any doubts, nowhere else.
0
 
LVL 34

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 24864257
My guess is that you got hit by SQL injection or by a bad query that deleted too many rows.

If you have an auto-incrementing primary key, then try inserting a new row in the table. See if the ID starts at 1 or if it's a larger number.

If it's 1, then someone probably ran a TRUNCATE query. If it's greater than 1, then you might have had a DELETE FROM ... query that didn't have enough limits or was done in a way that all records matched the criteria.

Red Gate's tools should be able to help you search through your transaction logs. Search for TRUNCATE or DELETE FROM queries. You may be able to trace the offending query back to a malformed page request (SQL injection or just data that wasn't properly escaped or something).

Suggestion: Use a tool like ParosProxy to scan your application for vulnerabilities after it comes back up.
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 24864298
While my guess is that it's an application-related problem, you never know about server security. I know MSSQL has some EXEC privileges that are often left wide open and can be exploited to gain access to a server. Some things to look for:

1. See if there are any unexpected tasks running in Task Manager.

2. Download and install Security Task Manager from Neuber - it can sometimes see tasks that are hidden from the regular Windows Task Manager, and if your server's been compromised by a rootkit or anything, then it's probably going to be a hidden process.

3. Look for any unexpected differences on the system. For example, sudden decrease in available disk space, new/unexpected services (run "services.msc" to see the services on the system), decrease in general speed, new tray icons, etc...
0
 

Author Closing Comment

by:netwhw
ID: 31603617
It was a TRUNCATE Command, we fixed the problem ! Thanks!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Ajax and PHP 4 29
query linked sql table field from access 4 19
SQL Recursion schedule 13 13
MS SQL Delete Duplicate Rows Only 2 13
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question