June 6, 2006 at 6:45 am
I have a DTS package that is used to perform DR processes of FTP tlogs and restoring them to DR databases. The package is scheduled to run daily and was running as expected until I changed the 'sa' password to comply with SOX audit. After changing the password the next run of the package failed, this was understandable as I did not change the password in the SQL Server connection in the package. I did that, then resaved the package and successfully ran it manually. I thought that was that, but then the next night the package did not run at all even though it was scheduled to run. So, I again ran it manually. Next I recreated the package with another name, dropped the other package and scheduled the new package to run. The new package failed to run last night. I don't understand why the job to run the package is not executing. Any ideas?
June 6, 2006 at 7:39 am
When scheduled to run, the package runds under the account that the sql server agent is logged in as. I suspect you have not chaged the password for that user.
June 6, 2006 at 7:54 am
No, I haven't changed the password for the SQL Agent account.
June 6, 2006 at 9:26 am
First thing is you need to check the Job Owner. If it is 'sa' then you need to change the job command. I mean, browse to Job Properties by right click on Job and select Properties and then select Steps tabular, probably you see the job and click Edit button... and you see the Command box:- something like this... DTSRun /~xxxxxxxx.... you need to change that.
If you don't know how to encrypt the key and change that, please post me back and I will give you the steps.
Thanks - Mubeen Mohammed
June 6, 2006 at 9:36 am
The job is owned by 'sa', but that is why I removed the package and job, then recreated it from scratch after I changed the password. Shouldn't that have resolved any issue?
June 6, 2006 at 10:36 am
Then you have to check SQLAgent Connection Properties. Is that using 'sa' or any Windows Service Account? and also, if you have already changed 'sa' password in SQLAgent, you need to stop and restart the Instance. Then only the new password settings updated to Systables.
June 6, 2006 at 10:43 am
I had this very problem in 2004 and I have steps to reproduce it. Read below. I even reproduced the problem without any SA just with any sysadmin login. The bottom line that the package somehow remembers who was connected in EM when the package was created even if there is NO traces of this login anywhere in the package or in the job or in the startup accounts. It was only entered in the process of connecting to SQL Server when saving a package but not as an package owner or package user login/password. read the steps to reproduce.
Fix: add a space and /E at the end the package number in the job Run the job again, refresh EM, it runs successfully.
It runs or does not run based on /E thing (integrated authentication parameter)
STEPS TO REPRODUCE
I created an SQL Server standard sysadmin login DTSuser with the password dtsuserpw and used DTSuser for server registration. Created a simple DTS package DTSbugTest. The package gets 1 value from Northwind connected as someotheruser_login and copies the value to E:\DTS_Bug_Test.txt. When saving the package it asked: "To save to Microsoft SQL Server (Local), you must enter the server, user name, password. SQL Authentication was selected and DTSuser credentials with password were already there. This is EM server registration credentials. It was in a separate frame below the frame with normal Owner Password and User Password text boxes. When saved, this package shows as an owner mydomainlogin. There is no DTS user credentials in the package properties. I scheduled the package and executed the job. It rans. I changed DTSuser password to dtsuserpwnew. Now I can execute the package manually but I can not run the job. The error message is:" Error string: Login failed for user 'DTSuser'. "
SQL Server and Agent are started on Local System, Agent logs in to SQL server as Windows authentication. "Only sysadmins can execute CmdExec jobs" is checked. The owner of the job is SA (I changed it to SA). There is NO DTSuser in any of the package or job properties. There is mydomainlogin in the Owner field of sysdtspackages table
Regards,Yelena Varsha
June 6, 2006 at 11:10 pm
Since you state that the problem began after changing the sa password:
In EM choose SQL Server Agent, right-click and choose Properties, Connection tab
Is the SQL Server connection using sa? If so change the password here as well. Caught me a couple of times.
Andy
June 7, 2006 at 6:17 am
The SQL Agent is not using 'sa' it uses a windows domain account. Yesterday I decided to try stopping and restarting the SQL Agent service. After doing that my job ran successfully as scheduled last night, problem solved. Thanks for everyone's help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply