June 10, 2015 at 5:04 am
Jeff Moden (6/10/2015)
ericpap (6/8/2015)
I'm facing a very complex situation with my software databases on a specific server. This is the second time this problem happend on this server. Basically while user where working on the system, with no apparently reason, all the data from three user databases where lost.The 3 databases where put on a previuos state from 1.5 month ago where all the databases changes made to this databases are lost, yes LOST! I had to restore a backup for all the databases but the data from passed two dates after the last backup where lost. The first time this happend, was with SQL Server Express 2008. Now happends on SQL Server 2008 R2 Standard.
I suspect a hardware / OS (Hard drive, RAM, etc) problem with the server itself, but no other data where lost (data files, etc) on SQL Server data.
The only lead i have to discover what happend is the Sql Server LOG, where i see some strange entrys:
1) All the log previous to 06/08/2015 11:55 (the exact moment where the problem apear) is lost.
2) An error during decryption, which don't know what it means.
3) A "Recovery complete" message
4) After that I see on the log constant message from CHECKDB over my users database (a lot of them) wich no one execute.
Does anyone have any idea of what could be hapenning here?
Is this a Hardware issue like I suspect?
Thanks!
Is this a Production Server?
yes
June 10, 2015 at 9:57 am
ericpap (6/10/2015)
Jeff Moden (6/10/2015)
Is this a Production Server?yes
Sorry to hear that.
If you can't get someone to admit they did something, I'm not sure you'll find much forensic evidence. You can comb the Windows event logs, but likely something happened you can't catch. My guess is either of these happened.
- bare metal restore of Windows from a previous time
- restore of SQL Server folders (binaries + data/log) from a previous time
- stop of SQL, copy/restore old versions of mdf/ldf, restart SQL
- restored SQL Server, stopped and restarted twice, deleted old logs.
June 10, 2015 at 10:26 am
If i was you and this was production, i would either call Microsoft or one of sql experts here. There could be many things going wrong, hard to explain everything in blog.
June 10, 2015 at 2:18 pm
ok. The more deep I digg, the worst it smells... This starts to feel like a CSI show...
In the session on the user that stopped the service, i found some very big zip password protected wich name is "delete_something" on a temporary folder. Also find on the recicled byn a txt file. Inside that where path to the master database folder on SQL Server. From what i see master or model db could be replaced or their log altered. Also find that the user that did all this is curiously the owner of the software that it is been replaced by mine. What is the chance of that?
I don't want to accuse anyone, but this start to look a lot as a concient attack on the server data and an intent to "delete" the evidence. Also find no way that the SQL Log could be lost. The only way i can think of is that someone is trying to cover his footprints. Of course all his privileges where removed on this server, but I can't believe that someone could do something like this on purpose. Thanks averyone for your help. I learn a lot dealing with this problem.
June 10, 2015 at 2:20 pm
Good luck. If you need more help or get more info, post back. We're happy to do what we can.
June 10, 2015 at 2:22 pm
Time to schedule a meeting with management and IT security people (and/or HR).
btw, zip passwords are trivial to crack, a quick google search should turn up a pile of tools that can do the job.
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 10, 2015 at 2:23 pm
ericpap (6/10/2015)
ok. The more deep I digg, the worst it smells... This starts to feel like a CSI show...In the session on the user that stopped the service, i found some very big zip password protected wich name is "delete_something" on a temporary folder. Also find on the recicled byn a txt file. Inside that where path to the master database folder on SQL Server. From what i see master or model db could be replaced or their log altered. Also find that the user that did all this is curiously the owner of the software that it is been replaced by mine. What is the chance of that?
I don't want to accuse anyone, but this start to look a lot as a concient attack on the server data and an intent to "delete" the evidence. Also find no way that the SQL Log could be lost. The only way i can think of is that someone is trying to cover his footprints. Of course all his privileges where removed on this server, but I can't believe that someone could do something like this on purpose. Thanks averyone for your help. I learn a lot dealing with this problem.
No need to accuse, just ask what those files are and what they're doing there. 😎
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 10, 2015 at 2:33 pm
So at this point, has anyone in the IT department confessed to monkeying around on the production server?
Also, what does executive managment think about all this?
From what you describe, this whole scenario sounds like something that would happen to a startup in someone's garage or dorm room. If this is an actual production server belonging to a corporation or other organization, then it can't all fall on you.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 10, 2015 at 2:33 pm
GilaMonster (6/10/2015)
Time to schedule a meeting with management and IT security people (and/or HR).btw, zip passwords are trivial to crack, a quick google search should turn up a pile of tools that can do the job.
Good point! Isn't the only option a brute force attack? I will try to crack it. Thanks!
June 10, 2015 at 5:26 pm
ericpap (6/10/2015)
Jeff Moden (6/10/2015)
ericpap (6/8/2015)
I'm facing a very complex situation with my software databases on a specific server. This is the second time this problem happend on this server. Basically while user where working on the system, with no apparently reason, all the data from three user databases where lost.The 3 databases where put on a previuos state from 1.5 month ago where all the databases changes made to this databases are lost, yes LOST! I had to restore a backup for all the databases but the data from passed two dates after the last backup where lost. The first time this happend, was with SQL Server Express 2008. Now happends on SQL Server 2008 R2 Standard.
I suspect a hardware / OS (Hard drive, RAM, etc) problem with the server itself, but no other data where lost (data files, etc) on SQL Server data.
The only lead i have to discover what happend is the Sql Server LOG, where i see some strange entrys:
1) All the log previous to 06/08/2015 11:55 (the exact moment where the problem apear) is lost.
2) An error during decryption, which don't know what it means.
3) A "Recovery complete" message
4) After that I see on the log constant message from CHECKDB over my users database (a lot of them) wich no one execute.
Does anyone have any idea of what could be hapenning here?
Is this a Hardware issue like I suspect?
Thanks!
Is this a Production Server?
yes
Then you also have another major problem. If you look back at the server startup in the log you posted, it says that it's the Developers Edition. A disgruntled employee that knows about these things could turn your company in, get paid a shedload of money for doing so, and have you guys pay a hefty fine or even be shut down.
With that in mind, I strongly recommend that your company get current on licensing across the board right now.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2015 at 7:19 am
One avenue to investigate, both as a possible root cause and a possible reovery solution, is Volume Shadow Copy, which is a feature of NTFS, applying to all versions of Windows since XP, including server editions. My theory is that maybe your database files were overwritten as a result of a VCS recovery operation, or even if that's not the case, then maybe you can still recover a recent version of your files from VCS.
This article was written for MySQL, but the concept could just as easily apply to SQL Server. It explains what the Volume Shadow Copy feature is, how it can be potentially used to recover prior versions of files, and the dangers of promoting a file or folder from VSC archive on a production database server.
http://blog.webyog.com/2012/10/25/danger-zone-mysql-and-windowsntfs-volume-shadow-copy-technology/
Here is a link to a free tool for exploring shadow copy archives.
http://www.shadowexplorer.com/
But really, if this database server is important to your organization, you need to have someone who specializes in forensics and data recovery come on site and take a hands on look at it. Dicking around with it yourself may actually cause more harm. For example, copying files around on the server's storage drives or re-intalling SQL Server could overwrite and permanently destroy latent data that might otherwise be recoverable.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 11, 2015 at 7:30 am
Ok guys. So this is the end of the road for this history.
I manage to crack the password from ZIP files I found on user profile recycled bin. Inside them I found not only copies of all my software SQL databases, but also copies of master and model, and an entire copy of Program files included my software folder.
There is no doubt know in my mind that this was a concient attack not only to my software files (I supposse for reverse enginery purpose), but also to the company stolen data, with the intention of doing harm. And also found proof that the user try to delete his action footprints.
Today I will talk to the owners and make sure they are aware of all the security issues found. That's all I can do rigth now, and this security issues are not my responsability. Unfortunally I was involved in this problem indirectly by somebody else bad work.
Also will consult legal department for futher actions.
I whant to thank again everybody for your help and the effort in trying to help me find where the real problem was.
Eric
June 11, 2015 at 7:36 am
Thanks for posting back what you ultimately found. So sorry you're going through this nightmare. It can't be fun. Actual malicious actions against a server is not one I've personally run into yet. Horrible to think about.
Best of luck!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2015 at 8:44 am
Thanks for the final note and good luck with all of this.
June 11, 2015 at 9:14 am
Somehow I feel we're missing a lot of the back story; does this level of anarchy really happen in the IT departments of corporate or government world? I'm guessing it's a startup.
They need a dedicated sysadmin for both the server box and the database. Even if it's only one person; that's better than allowing everybody admin access. This really should be a watershed moment for the organization, and the next move for this guy should really hinge around how management chooses to respond. Personally, at this point I'd document everything I found, hand my findings over to management, and then walk away from this train wreck before it explodes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply