changed password and the job won't run any more

[19902003]

Hi Can anyone please tell me how do I fix the following issues?

I took one of the database offline and the sever got disconnect after an hour later, then I put the database back; but don't know the original sql user login information, so I went ahead use the Windows Authen and it works; then I change back to SQL login and reset the sql user login password; now one of the job runs and the other won't even I changed to use windows authentication; here is the error I have got:

Executed as user: mydomain\Administrator. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'mysql'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.

Please help.

Thanks a lot!

-Steve

 

[mrdenny]

You need to edit the DTS package that the job is running and change the password within the database connection within the DTS package. I'd recommend setting the DTS package to connect to the SQL Server via Windows Auth, so this doesn't happen in the future.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19902003]

Thanks a lot Denny; I did that and still can't get the job run; I went to Local Package and select the package and select design the package choose the Window Authen, then I run the package by right clik it and say "execut package; but if try to run it from the job (EM\Management\Sql Agents\Job\choose the job that is associated with the package and run it, I still got the messages "
Executed as user: mydomain\Administrator. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'mysql'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed." I look at the steps and it seems like the command is encryted with the password "DTSRun /~Z0x0E16B101E0F7D0C7D538D642632640D23B" is there a way to decrypt it? I found some tools and was not able to follow the instruction; please help.

This is the instruction that I couldn't follow:
DTSRun command line decryption utility
======================================

Sometimes people ask how to decrypt some sort of data stored in Microsoft SQL Server 2000.
One case is decryption of DTSRun command line parameters that can be found in sysjobsteps
table:

SELECT command
FROM msdb.dbo.sysjobsteps

---------------------------
DTSRun /~Z0xF2E216E36948A6C83AC888EA05C93E2CACB07C0B59649CE75654E3FDAC6CCE4ED5F72528FC6458D14F566A164C9F31C4D18CA65E9E244994C4DF0ABE475C42D18B31DC84FB51E22B6DF34778D5159106B3D727B6CD576891E34C7D

Value after /~Z can be decrypted using DTSRunDec.exe: just run it and supply the value.

DTSRun also accepts separate parameters in encrypted form:

DTSRun /~N0x80E843677D7F44392E6B61D89EB9553C ...

To decrypt separate parameters use DTSRunDec2.exe.

Many thanks,

Steve

 

[mrdenny]

I would recommend changing the job command to use the english version of the DTSRUN command.

Use something like this.

dtsrun /S ServerName -E -N PackageName

For a list of paramaters open a command line and type:

dtsrun /?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19902003]

Thank a lot Denny, do I need to know the original password to run the job or I can just create a new job?

Many thanks,

-Steve

 

[19902003]

Denny,

How will I know which one is the one that I suppose to use? Is there a way to tell whether my package is under metabase or SQL;


o execute a DTS package saved as a COM-structured storage file, use:

dtsrun /Ffilename /Npackage_name /Mpackage_password

To execute a DTS package saved in the SQL Server msdb database, use:

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password

To execute a DTS package saved in Meta Data Services, use:

dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Rrepository_name


Many thanks,

Steve

 

[mrdenny]

Yes there is. Which folder do you see it in the Enterprise Manager. That will tell you which one it's stored in.

The package_password isn't needed unless your package has a password on it (which I don't recommend).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19902003]

Is there a tool out there I can decrypt the file like this:

/Z

Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.

I am not sure how to create the package; is there a way to get the command from the actual package?

when I use DTSrun /s

not sure how to create it.

many thanks,
Steve

 

[mrdenny]

I'm sure there is, but I don't know of one. You'd need to hit up google and see what you can find.

You have to manually create the command line to run the package.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com