March 10, 2009 at 5:05 pm
We had to fire our DBA and he set up some business critical DTS packages to expire upon his leaving. Now he's trying to extort money out of us to release them.
I've located the .dts files, but they're all password protected. Does anyone know of a tool or a way to get around this?
Any suggestions would be greatly appreciated.
Thanks,
March 11, 2009 at 9:25 am
The only way I can think of depends on there being an encrypted DTSRUN command somewhere in a scheduled job or a batch file. If you can find that, do this to decrypt the command and see the package password:
Copy the command to a cmd prompt, append /!X /!C to the end, and run it.
The !X argument blocks execution of the package and the !C argument copies the unencrypted command to the Windows clipboard.
Just open Notepad and paste to see the unencrypted command.
Greg
March 14, 2009 at 10:14 pm
Thanks Greg, but I tried that option and couldn't get it to work ... probably just wasn't using the right syntax, but I was able to find a way to at least get the user password and get most of the packages running. I wanted to put a post on here to summarize in case anyone else was running into the same issue.
The easiest thing to do to restore the packages would be to restore a previous version of the msdb database prior to the date the packages were deleted. This guy fully deleted every msdb.dbo.sysdtspackages reference, as well as any msdb backups, so I wasn't so lucky as to have this option. Thankfully, what he did leave behind were structured storage file (.dts) backups of the packages.
I also called into Microsoft Support, opened a critical case with them, and they were unable to help. The tech I got was actually very helpful; she walked through all the options we could think of and followed-up the next day to see how I was making out. They also will refund the fee for the call if they cannot resolve your issue.
IF you have the .dts files and there were SQL jobs scheduled to run the packages, you can do the following:
I downloaded a program called RunDTSDec.exe from http://jimmers.info/software.html. Download it from that site specifically as it contains the pre-compiled version. Open the SQL job, edit the jobstep set to run the package, then copy the hex following the /Z. Open an instance of command prompt and navigate to the directory where you've saved the dtsrundec.exe file. Type dtsrundec.exe followed by pasting the hex from the SQL job statement. Once you hit enter, it displays the dtsrun statement unencrypted. This is how I got the user password for the package, as it's denoted by the /m switch. Also make note of the package name after the /n switch.
You can then modify the scheduled SQL job to run a new DTSrun statement, which would reference the .dts file as follows: dtsrun /f "c:\filename.dts" /n "packagename" /m "password"
I still can't modify the design of the packages, but this at least buys me some more time to research and recreate the packages from scratch. In the interim the business processes are back up and working.
The new issue (newly discovered) I'm having is that this guy modified some registry key or something so that the DTS designer won't open on a couple of the SQL servers, and none of them will display anything if I try to open or design a table from Enterprise Manager. I'm sure that's probably a quick change, but it's just a matter of finding it ...
March 16, 2009 at 7:45 am
I hope you called a lawyer to see if you could charge this guy with something. That's just wrong. :angry:
March 16, 2009 at 9:50 am
musicman_7 (3/14/2009)
Thanks Greg, but I tried that option and couldn't get it to work ... probably just wasn't using the right syntax, but I was able to find a way to at least get the user password and get most of the packages running. I wanted to put a post on here to summarize in case anyone else was running into the same issue.
I'm glad you were able to find the password. I used to recommend using RunDTSDec, but I've found using the !X and !C arguments is easier and faster. Too bad it didn't work for you.
Regarding your problem with Enterprise Manager, you can reinstall just the client tools from the installation disks.
Greg
March 17, 2009 at 8:19 am
So the guy, on the employer's tab and trust managed to destroy work he was paid for and on top of that he wants money to fix his sabotage.
This goes well beyond unethical behavior. The guy is a rat. By filing a complaint for willful damage to the employer's operation and(likely) loss of money for investigating the cause of the failure, if not outright loss of money due to improper functioning of the database, this guy would be faced with the choice of either ending up named in a lawsuit or back down and provide the information. Of course lawsuits are very expensive, but presumably a first step of opening a case file for the purpose of having the rat sent a process server at home might be done for a reasonable amount of money. For some money, your employer could get a sense of satisfaction.
The action taken by this rat damages the entire community of SQL'ers and this provides the excuse for the humiliating experience of being escorted off the premises without any notice which is routinely imposed on individuals without reproach who are fired due to the economic downturn.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply