Decrypting the encrypted DTSRun /~Z command-line parameters

by Joe Toscano, Senior Software Engineer, RDA Corporation

Many of us have created Data Transformation Services (DTS) packages and then right-clicked on them to schedule their execution via a SQL Server 2000 Job. When you do this, a job is created whose name matches the DTS package you right-clicked on from Enterprise Manager. This job will be a single-step job that contains an Operating System Command to execute DTSRun which essentially kick-starts the package. The potential problem here is the command line parameters to DTSRun in the job step are actually encrypted.

To illustrate this I created and saved a package appropriately named Very simple DTS package. Then I right-clicked on the package from Enterprise Manager and choose Schedule Package from the context menu. Once this is done, if you look at actual job created you will find that the job name actually matches the package name. Below we see our single-step job that uses the DTSRun command with a very long encrypted string after the /~Z option.

While some folks may be fine with this and view the encryption as an extra layer of security, others may want to see the DTSRun parameters that provide both the server name that houses the DTS package along with the actual package name being executed. (Keep in mind your jobs can reference packages that reside on other instances of SQL Server 2000!) I’d like to focus on the latter audience for reasons stated below.

Why would you need to see the DTSRun cleartext parameters?

I’ve worked on several SQL Server 2000 to 2005 migration projects in which we needed to unravel or decrypt the string to see exactly what package is being executed and what server that package is located. (Specifically, we wanted to see the \S Server Name and \N Package Name DTSRun options) At one site, we notice that several copies of our DTS packages were made over the years whose names seemed very close to the originals. Frankly, it made us nervous that we saw the original DTS package saved with the ‘Original’ tag as part of the package name along with a modified copy of this DTS package saved with the ‘New’ tag as part of its name. Given this, how could be we 100% confident that the job name actually told us the exact package that was executed? Another possibility we had to consider what that over the years administrators could have changed the name of jobs! Finally, how could we even be sure that our jobs executed packages on the same server as our jobs were created and not reference packages on some other server? For all of these reasons (and our peace of mind) we decided to decrypt the command line parameters. After doing a bit of research, we found two methods to accomplish this:

1. Find the decrypt code

This shouldn’t come as a surprise, but if you search in the right places you can find the decrypt C-code that accepts the encrypted string (the stuff AFTER the /~Z) as a parameter and spits out the DTSRun parameters in cleartext. I’m not going down this road and I would certainly NOT recommend anyone else does so.

2. Using DTSRun options to produce the cleartext parameters.

It turns out there are command line options of DTSRun that don’t actually execute the DTS Package, but instead produce the cleartext parameters. Below are the steps to accomplish this for our sample package and sample job described above:

1. Copy the DTSRun command line from the job step (including the very long encrypted string!)

2. Open a Windows Command Line Window and paste the command into it.

3. Add /!X and /!C to the end of the DTSRun command. (/!X says do not run and /!C says copy results into clipboard)

4. Execute the command. (You should see DTSRun: Loading … and DTSRun: Executing …. as is displayed below and the cleartext parameters should now be in your paste buffer)

5. Paste the cleartext parameter string into notepad.

When I performed these steps for my sample package and sample job the following was returned:

DTSRun /S "JOETSRDAPC\SQL2K_DE_I1" /N "Very simple DTS package" /E /!X /!C

This tells me exactly what I was looking for! The Server Name that houses the package and the package name that is being executed. (/E says use trusted authentication) Using this method we were able to migrate both our DTS packages and jobs to our new server knowing for sure which jobs executed which packages. In fact, our client preferred to substitute the /~Z encrypted string in each job with the cleartext parameters to avoid future problems.