| Author |
Topic |
|
mattb
Starting Member
5 Posts |
 Posted - 2005-11-30 : 16:19:48
|
Not that this would ever happen :) but let's imagine that you inherit a SQL 7.0 SP4 Server that has a bunch of jobs that kick off DTS packages with DTSRun calls. All the DTSRun calls are encrypted - some with SQL 7.0 encryption level and some with SQL 2000 encryption level (/~Z). There's no documentation showing which packages are being run by each call and the only info at your disposal is the encrypted DTSRun command.So, how would you go about determining the relationship between the jobs and the DTS Packages? Any thoughts are welcome... from how to decrypt the DTSRun strings to digging through logs to find a correlation. Of course, I'm lazy all about efficiency so the less effort the better.One other note... I don't know the specific properties (servername, user credentials, etc) used when the encrypted DTSRun commands were created so simply generating new encrypted command for each package and comparing to the strings in the jobs hasn't proven fruitful. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-30 : 16:27:31
|
| There is no way to decrypt this information. If there were a way, then what would be the point of encrypting it in the first place?I'd suggest not using the DTS wizard scheduler to create your jobs so that the package names aren't encrypted. Instead, create the job on your own and then in the job step use dtsrun.exeTara Kizeraka tduggan |
 |
|
|
mattb
Starting Member
5 Posts |
Posted - 2005-11-30 : 18:18:57
|
| DTSRun decrypts that string in order to extract the params so, purely academically I can guarantee that the string is decryptable. Does that means that it's weak, maybe so or maybe not - who knows. I shouldn't haven't posted that suggestion in the first place, though, as it really isn't the approach I expected anyone to recommend. I'm more interested in some creative approaches to working out of the messy situation that the example presents.I agree with you on how it *should* be done but that doesn't solve the current problem - if you have a server that's already configured as I describe then how would you go about untangling things. It's a bit of a challenge... just looking for outside input before I take the brute force approach and update every DTS package to log out to an external source that I can use to correlate b/t DTS package runs and job runs. Yes, of course, if this were SQL 2000 I could use package logs but there's no such bird in 7.0 (unless it's something I'm not familiar with).Matt |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-30 : 18:44:29
|
| I'm very surprised to see that SQL Security site has provided this utility. I wonder if it works. It kinda defeats the purpose of encrypting if the encryption scheme is easy to crack.Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-30 : 19:09:39
|
There's stuff there that could be used for a lot more evil than cracking a DTSRun command:sqllhf.zip Description: SQL Server Brute Forcing tool featuring a scriptable command-line interface, scans networks larger than class C, and IP list support. by Matthew Wagenknecht sqlpoke.zip Description: Used to scan a range of IP addresses for SQL Servers and then execute a predefined script. Could be used to track down SQL Servers in your own organization and ensure they stay locked down. - by xaphansqldict.zip Description: Brute-force SQL Server password utility. Good for auditing SQL Server passwords in your organization. Don't use this power for evil - by Arne Vidstrom. quote:
Originally posted by tkizer
I'm very surprised to see that SQL Security site has provided this utility. I wonder if it works. It kinda defeats the purpose of encrypting if the encryption scheme is easy to crack.Tara Kizeraka tduggan
CODO ERGO SUM |
|
|
|
mattb
Starting Member
5 Posts |
Posted - 2005-11-30 : 19:28:44
|
| What's Google? :)Downloaded that tool and explored; it extracts passwords from DTS Packages that have been saved off as files. Doesn't look to me like I can use it to decrypt DTSRun commands. I'll qualify that by saying that I'm not a C++ programmer so perhaps there's something in there I could put to use if I knew was I was looking for but that one doesn't appear to do it. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-30 : 22:00:27
|
I think you downloaded the wrong one.It sounds like you got "DTS Password Decryptor", not "DTSRunDec"Comments from DTSRunDec.c:"DTSRun Parameters Decryption UtilityVersion for decryption of single data block (i.e. data after /~Z"quote:
Originally posted by mattb
What's Google? :)Downloaded that tool and explored; it extracts passwords from DTS Packages that have been saved off as files. Doesn't look to me like I can use it to decrypt DTSRun commands. I'll qualify that by saying that I'm not a C++ programmer so perhaps there's something in there I could put to use if I knew was I was looking for but that one doesn't appear to do it.
CODO ERGO SUM |
|
|
|
annasree
Starting Member
1 Post |
Posted - 2006-02-10 : 10:14:43
|
| Hi,I downloaded the DTSRunDec.zip but, the files have .c extensions, whereas the instructions asks us to run the .exe.Help please |
|
|
|
SqueakyPete
Starting Member
2 Posts |
Posted - 2006-03-22 : 05:39:56
|
| This tool does exactly what it says on the tin.I've inherited a bunch of SQL Servers with hundreds of jobs which perform DTSRuns. Job/jobsteps have subsequentially had their names changed. This tool is great and has saved loads of time, so much so that I can afford to sit here typing this reply.Had to get one of our dev guys to compile it though. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-22 : 05:58:08
|
| If its anything like the encryption on Sprocs / Triggers / etc, "WITH ENCRYPTION", it will be only a matter of minutes to decrypt - and not worth the magnetic media the flux is written on ... which, IMHO, is a crying shame.Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-02 : 08:52:31
|
quote:
Originally posted by tkizer
I'm very surprised to see that SQL Security site has provided this utility.
Well, there are two built-in command line switches in Microsoft DTSRUN to decrypt the package name.The question is if that is public knowledge?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-02 : 08:57:25
|
quote:
Originally posted by tkizer
There is no way to decrypt this information. If there were a way, then what would be the point of encrypting it in the first place?
After some Googling I found these three pages at Microsoft.com that describes how you can decrypt the package name.So I consider this "public" knowledge.http://msdn.microsoft.com/en-us/library/ms345282.aspxhttp://msdn.microsoft.com/en-us/library/aa224467(SQL.80).aspxhttp://msdn.microsoft.com/en-us/library/ms345282(SQL.90).aspxIf you use two of the available swtiches together, you will get the package name in clear text copied to the clipboard without even running the package.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-02 : 14:10:08
|
True.But the answer was available in 2005 with Books Online for SQL Server 2000.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Decrypting DTSRun Commands