August 5, 2004 at 11:00 am
I have about 100 scheduled dts jobs. I inherited a mess with this SQL server. The sa user was used for everything. I went through and methodically cleaned up all dts sql connections to use a different account. I also went through each scheduled job and changed the owner from sa to an NT user with appropriate rights. Everything was working just fine. Until......I changed the sa password. Nearly every scheduled job the next day failed. Even though there wasn't any visible evidence of the sa user in either the dts package or the scheduled job, it was still using the sa user and password. It gave me an "authentication error message" and specificially wrote that "user sa account failed".
The only thing I could do to fix it was to delete the job and recreate the schedule from the dts package. Then it worked fine. But what happens the next time I changed a password of a user who owns a job? I want to know what is going on here.
Does anybody have any ideas?
August 6, 2004 at 1:45 am
Hi Perry,
just an idea... I know it's obvious, but anyway I'll try. Besides the job owner, there is also "Run as user" setting in each step of the job (on Advanced tab). Maybe there still is "sa" in some steps? Or maybe even the "Self" setting could retain the info, that it was originally owned by sa - I couldn't find any info about that.
HTH, Vladan
August 6, 2004 at 3:13 am
Check the connection objects to the server who's sa-pwd you've changed in your dts-packages !
After you altered the sa-password, did you stop/start that server's sqlagent ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 6, 2004 at 3:34 am
Isn't this expected behaviour?
You changed the sql connections in your dts packages (from sa) and so created a new version of the DTS package. The scheduled jobs are still running the old DTS package (DTSrun /Z~xA1234...) which will have the encoded sa user and old sa password, but the sa password has changed so the package will fail.
Or I missing something here, again!
August 6, 2004 at 4:19 am
If you change the DTS version the job will still run the package with the new vesion. I guess the answer with setps run as maybe a better one to check.
August 6, 2004 at 6:52 am
In Enterprise Manager, drill down to SQL Server Agent under the Management folder. Right click on it and select properties. Reset you password here as well. I typically recommend that you don't run jobs under the SA account, but that's just me.
Darin Ries
August 6, 2004 at 8:22 am
I went though the same headache two years ago. The problem was the encrypted DTSrun command. I now write my own dtsrun statement and use trusted authentication. The only thing I use sa for now is to be the owner of the jobs.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 6, 2004 at 11:23 am
I agree that the job fails due to the DTSrun command string. Try this. Make a change to a package and create a second job to schedule it. Compare the DTSrun command in both jobs and you'll see that they are different. If you copy the new command string and paste it to the old job, it will run.
Linda
August 6, 2004 at 12:55 pm
Thanks for everybody's ideas.
Linda, I think you are right on. I assume, then that the dts command encrypts the user and password within the command string. Knowing this fact, can I make the assumption, then, if the job is created using a Windows network id, that the password is NOT saved within the dts run command? Otherwise the job would fail after the network user password changed. In our network, that would mean the jobs failing every 30 days.
One other question. I saw references to: "Run as user" setting in each step of the job (on Advanced tab). I have SQL 2000 Standard Edition, and I went to the step of each job, found the advanced tab, but did not see a "Run as User". Am I missing something, or am I blind?
August 6, 2004 at 1:23 pm
At the bottom of the Advanced Tab you will see a drop down box with "Run as user" to the left of it. Just select a user account to run the step.
Linda
August 6, 2004 at 2:37 pm
At least I know I'm not crazy or blind. The jobs I create from the dts package (right-click -- create schedule) don't have that (run as user) dropdown box.
However, when I go to one of my Replication jobs, it does indeed have that dropdown box. And, if I create a job by hand, then it also has the dropdown box. Interesting behavior.
I must ask the question. WHY?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply