| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2004-09-09 : 15:29:43
|
| A client changed the User Account and password SQL runs on to standardise it with other machines, and now they discover that there were some DTS packages running which depended on the previous User Account and Password. The DTS packages no longer work ... what a surprise!They don't know the original password for the original account, and the DTS stuff is encrypted and the source is lost (the laptop the guy programmed it on on the train broke a while ago, and that had the only copy of the source).You think I make this stuff up don't you?? <bg>So ... are they hosed or can I help them recover it all somehow?Elwoos ... I think I've found alternative employment for you, it will be just like your existing job!Kristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-09 : 16:40:52
|
| I think they are screwed. When you change the password of the SQL account that owns the DTS package, then you have to first open up the DTS package while the password hasn't been changed, then change the password, then go back to the opened DTS package (it's still in memory on that machine), then save as the package with the new password.If you aren't talking about the saving of DTS packages, but rather about the connections inside the packages, then you should already be able to modify those.Tara |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-09 : 22:50:41
|
| not sure but an idea, if they have backups of the msdb and master, can they restore before the incident happened? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-09 : 22:56:43
|
| Thanks Tara, useful to know about changing the password in the DTS packages first.I don't have any useful knowledge of DTS - the user says the DTS packages are encrypted - is that true, or is it that they've just forgotten the password for the user that "owned" the package? (this relates to before they started messing with the passwords; they say they tried to modify a package and couldn't "because it was encrypted")It doesn't make much sense to me that the original creator would have deliberately encrypted anything - its on a LAN, he was a senior person, so no "vendetta" agenda, etc. etc. But he is no longer working for the company, and the laptop with the source code is gone, etc. etc.Couple of other thoughts:Can we restore an old backup to somehow try to get at the packages?Any 3rd party hacks to get around the password issue?ThanksKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-10 : 12:15:24
|
| I don't believe that you can encrypt DTS packages. I've never seen that option. But I could be wrong. You certainly could restore the master and msdb databases, but that means you'd lose anything that has occurred since then in those dbs. Not much should in those, but you do have to consider the possibility of data loss. How complicated are these DTS packages that they can't just recreate them? Don't they have a copy of them in development or somewhere else?Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-09-10 : 15:44:38
|
| What if you restore the MSDB and MASTER to another server, and then export the packages to the production server with the new UN and pwd? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-12 : 05:11:05
|
| Turns out that the deal is that the DTS package calls an SProc, and THAT is encrypted. All starts to make more sense now ...Any hacks out there to reverse encryption?(I hope not, 'coz we rely on that to protect our source code, but it would help this particular customer just now!)Kristen |
|
|
|
n/a
deleted
35 Posts |
Posted - 2004-09-13 : 10:24:15
|
| If I am reading the posts correctly your problem is that you have a n encrypted stored procedure on your SQL Server and you do not have the original code for the procedure. tsk tsk tskUse the following to recreate the procedure without encryption. WARNING. this will remove all security so make sure you know who has what security on the object before continuing.<edit> code example removed </edit>the results of this will be your procedure unencrypted.A few notes;- Make sure you have a backup before doing this.- make sure you save a copy of your proc in a source control libraray.HTHPaulPS - this is not my code. I have had this in my libraray for years and I do not remember where I got it. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-09-13 : 10:29:21
|
| plivengood-I have removed the code you posted above. Sorry, but on SQL Team we do not post methods on cracking, defeating, or circumventing SQL Server security or encryption. Regardless of how weak it may be, or even with the goal of making people aware of security issues, it is not acceptable to provide details of how to actually do it. If you have seen this information published elsewhere, even on SQL Team, please do not post it here, nor post any links to such information. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-13 : 13:32:20
|
| "If I am reading the posts correctly your problem is that you have a n encrypted stored procedure on your SQL Server and you do not have the original code for the procedure. tsk tsk tsk"You are almost reading it correctly, only difference being its a client who has developed some SQL SProcs, not me! They encrypted them on their sever (goodness knows why, its on a LAN and sufficiently secure in its own right), and then lost the SQL - stored on a laptop, written on a train, not copied to the LAN from where it would have been backed up, and then the laptop broke. In fact I reckon this could happen to me ;-)robvolk: Fully sympathise with your view, and indeed agree with it, however I am keen to help out my client - any suggestions? (In practical terms I thought that SQL 6.5 through 7 were easily un-encryptable, but SQL2K used much more robust encryption. If this is not the case I can go on a hunt for unencryption, but I could do with knowing whether it is actually, and EASILY, possibly to unencrypt an SProc in SQL2k. Thanks.)Kristen |
|
|
|
n/a
deleted
35 Posts |
Posted - 2004-09-13 : 14:39:49
|
| my bad, sorry |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-13 : 14:52:00
|
| That's as maybe, plivengood, but somehow I'd like to know what your advice was. Don;t know how to read it though!Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-09-13 : 20:08:13
|
| Don't take it personally, I'm not trying to pick on anyone but the last thing we need is for SQL Team to become a resource for script kiddies and 1337 h4X0rz. Not to mention targeted by them. It's happened before.As far as resources, the most I'll say is Google. |
|
|
|
|
Lost password