June 14, 2010 at 7:18 am
Hi guys, this is my first post, sorry for being so direct, but I'm in a real hurry. Please let's leave presentations for later (please). I'm not a very experienced dba btw. I tried the search and google but I can't find exactly what I need. Also I don't know if this is the correct subforum... maybe security...
This weekend some information disappeared from the database (Sql Server 2005). I have a backup from before of the incident an I have a backup from after the incident. I restored and unfortunately I lost a couple of days of info but it could have been worst (I have to learn how to backup automatically btw). Two tables were emptied completely (two many-to-many tables) so the rest of the info was not available.
Now in my local I have both backups so maybe I can know what happened. It can't be a failure of the server, it must be a hack but it's strange that only two tables were deleted (not droped) as far as I know.
I have to find what happened to try to prevent another attack(?). For the momment I deactivated some parts of the web in case there is a potential sql-injection there (I didn't create this web, I'm replacing the developer that left the job).
Is it possible to consult when and how some records were deleted having the pre and post backup?
Thanks in advance, meanwhile I continue to dig in google about my question.
Alfonso
June 14, 2010 at 8:40 am
Unless you were running some form of trace or audit at the time, it's highly unlikely you'll be able to trace what happened. SQL doesn't run data audits by default.
It was either a hack or someone ran a delete from on the wrong server. SQL won't just empty tables by itself.
If the front end is web, there may be some logging done by IIS or the web application. Have you checked there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2010 at 8:56 am
As Gail mentioned, without logging hard to know what happened.
One thing I'd check is if you have triggers or cascading deletes enabled to see if this was someone trying to remove some data and accidentally deleting more than expected.
In terms of preventing it, you can take frequent log backups, and learn how to restore them. That would narrow down "when" this happened.
It could be SQL Injection, could be maliciousness from the last developer or someone else. When the developer left, did you change passwords? I'd do that first.
June 14, 2010 at 9:05 am
Hi thanks for the answers.
GilaMonster (6/14/2010)
It was either a hack or someone ran a delete from on the wrong server. SQL won't just empty tables by itself.If the front end is web, there may be some logging done by IIS or the web application. Have you checked there?
I think it was sql injection because we've got some spam comments in some part of the web. I could't find any sql statements in the comments but the id of the spam comments have gaps so maybe they were deleted after the incident. But this is just a supposition, I'm having a look at the forms and at the stored procedures.
I'm the only one who has access to the Sql Server Management Studio and I didn't do anything with the tables that were deleted.
Steve Jones - Editor (6/14/2010)
One thing I'd check is if you have triggers or cascading deletes enabled to see if this was someone trying to remove some data and accidentally deleting more than expected.In terms of preventing it, you can take frequent log backups, and learn how to restore them. That would narrow down "when" this happened.
It could be SQL Injection, could be maliciousness from the last developer or someone else. When the developer left, did you change passwords? I'd do that first.
The passwords are new, I changed all of them when the other guy left the company. You are right about learning how to backup and restore. When I tried I wasn't able because the db was in use, I switched teh db to single user and again I wasn't able to restore and also I wasn'e able to switch again to multiuser... I almost got crazy. Needless to say that I'm reading about how to do this kind of stuff at this moment.
I have to learn how to read those logs in the IIS and the Sql Server... Thanks for the help
June 14, 2010 at 9:09 am
If you track down when the issue occurred, I'd look through IIS logs and try to find the pages that are vulnerable. Fi that code ASAP. Likely you have someone using dynamic SQL or you are building code inline and submitting it.
In terms of access, you shouldn't be the only one. If you get sick or quit, is the company stuck? Normally Windows admins have access as well, or you should give the SA password to a CFO or someone else.
If others have access to the SQL Server, keep in mind they can submit queries through Excel, Access, etc. They don't need SSMS.
Good luck. Practice those restores, run log backups often and hopefully hackers have moved on.
June 14, 2010 at 9:32 am
Steve Jones - Editor (6/14/2010)
If you track down when the issue occurred, I'd look through IIS logs and try to find the pages that are vulnerable. Fi that code ASAP. Likely you have someone using dynamic SQL or you are building code inline and submitting it.
I have the access logs, now I have to learn how to read them... do you know any trick to idenfify possible vulnerable pages?
Steve Jones - Editor (6/14/2010)
In terms of access, you shouldn't be the only one. If you get sick or quit, is the company stuck? Normally Windows admins have access as well, or you should give the SA password to a CFO or someone else.If others have access to the SQL Server, keep in mind they can submit queries through Excel, Access, etc. They don't need SSMS.
Well, I'm not the only one who has the password but I'm the only one who knows how to access the data and run the querys (in fact to open the programs)... I'm the dba, the developer, the marketer... just me, now I know why the other worker runaway lol ... As far as I know the data is only accesible directly or by the web.
June 14, 2010 at 9:36 am
Read about how it's done. here's a start: http://www.sqlservercentral.com/articles/1269/
If someone calls a stored procedure with the query object and passes in something like:
"mystoredproc " + @param1
That's a good way to get SQL Injection. Instead you ought to be using a stored procedure type, and setting the parameters to specific calls, not concatenating them in.
June 14, 2010 at 9:53 am
Thanks Steve, I'm going to read it right now. I use stored procedures and parameters always, and I think I do things properly but I don't know what is inside al the usp's the last developer wrote.
BTW, I'll try to install AWStats to have a view at the logs, it's a pity that there is not a desktop app in wich you can import the files and get the information.
June 14, 2010 at 10:05 am
Is your database in FULL recovery mode ? It should be. If not, set to FULL recovery, take a FULL backup right away, then schedule transaction log backups to run every 15 minutes or so. That will go a long way to preventing future data loss.
You said you're having trouble restoring. Are you trying to restore over the entire live database ? If you just lost 2 tables, maybe it would be better to restore your database as "MyDatabase_Temp" and then copy the 2 tables from "MyDatabase_Temp" to "MyDatabase". That way, the rest of your database will still be current and not replaced with older data. It depends on how inter-related the various tables are.
June 14, 2010 at 10:06 am
I believe its C:/IISlogs/HTTPERR/HTTPERR/*.log
Check the log files in the above folder (probably, or the ones under IISlogs) in the web server and see if you can find any information on that.
The default trace of SQL captures the object level changes
Pavan.
June 14, 2010 at 10:41 am
Guys, it's confirmed, the site was hacked. I found a new user in the admin tables. The problem of this site is that I don't have any "manage users" section and I have to go directly to the SSVS to check some things, I don't even know how long that user has been there. I switched off all the pages in wich anonimous users can leave information, only people from the company can write information in the site now until I find the possible bugs...
homebrew01 (6/14/2010)
Is your database in FULL recovery mode ? It should be. If not, set to FULL recovery, take a FULL backup right away, then schedule transaction log backups to run every 15 minutes or so. That will go a long way to preventing future data loss.
I have to learn how to do that, I've got a book and I'm reading.
homebrew01 (6/14/2010)
You said you're having trouble restoring. Are you trying to restore over the entire live database ? If you just lost 2 tables, maybe it would be better to restore your database as "MyDatabase_Temp" and then copy the 2 tables from "MyDatabase_Temp" to "MyDatabase". That way, the rest of your database will still be current and not replaced with older data. It depends on how inter-related the various tables are.
Yep...I'm quite rookie as dba, I tried the first things I could think of...
dba_pkashyap (6/14/2010)
I believe its C:/IISlogs/HTTPERR/HTTPERR/*.logCheck the log files in the above folder (probably, or the ones under IISlogs) in the web server and see if you can find any information on that.
The default trace of SQL captures the object level changes
Pavan.
I've got some logs, now I have to learn how to read them and with which tool I have to use.
Thanks to all, guys for all the answers, I have a lot to learn...
June 14, 2010 at 4:17 pm
If you have before and after images of the table(s) in question, there are 3rd party tools, e.g. from RedGate, that can compare them and show you the data differences.
Or, if it's just a table or two, you could do a full outer join and compare them yourself.
Scott Pletcher, SQL Server MVP 2008-2010
June 15, 2010 at 3:22 am
Recovery Model: Database -> Properties -> Options -> Recovery Model
Backups: Management -> Maintenance Plans -> New Maint Plan ->
- Give it a name ie "SQL FULL Backups"
- Drag in "Backup Database Task"
- Select databases, file location etc
- Drag in Maint Cleanup task to delete old backups
Repeat for Transaction Log Backups
I'm sure you can find more details online. Shouldn't take more than 30 minutes each to set up your first time.
June 15, 2010 at 8:26 am
I created maintenance backups to save the databases daily, before this I was doing it manually... I lost some information but luckily I had a backup almost up to date. Now I have to learn how to restore a db that is in use. I'm using a book about Sql Server.
Now I'm more concerned about avoiding this to happen again than recovering the (small) data loss.
Thank you guys
June 15, 2010 at 10:59 am
bigfonx (6/15/2010)
I created maintenance backups to save the databases daily, before this I was doing it manually... I lost some information but luckily I had a backup almost up to date. Now I have to learn how to restore a db that is in use. I'm using a book about Sql Server.Now I'm more concerned about avoiding this to happen again than recovering the (small) data loss.
Thank you guys
Are you backing up transaction logs now ?
The database needs to be not in use. Why not just restore the tables ???
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply