Changing sa password

Question

Changing sa password

SQLRO

SSCrazy

Points: 2923
More actions
July 14, 2010 at 6:48 am

#74300

Hello,
We have changed sa password for one of the servers. The DTS packages which were scheduled to run on a specific time are unable to run the schedule. The jobs which are scheduled to execute the packages are failing and we have to reschedule the packages after dropping the failing jobs.
Any idea why this should happen? Can we avoid this happening?
Thanks in advance.
Regards,
Rohit Chitre

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

Obviously something is trying to log in to sa with the old password. Is SQL Agent able to run? What login is it configured to use? Does the package itself have the login and password hard-coded anywhere?

_________________________________
seth delconte
http://sqlkeys.com

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 2

Seth has some good ideas about where to look. This is one reason you should not use sa in your packages or connections. The pwd should be changed periodically for security reasons and it can be an issue with lots of failures.

Create new logins, setup rights, use those in packages.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Hi Rohit,

1. Find the DTS pkgs that are using sa user account for the connection managers

2. Open the Pkg and change the sa password. Test the connection. Save the changes.

3. Schedule the DTS packages as SQL Server Agent Job

Thank You,

Best Regards,

SQLBuddy

SQLRO SSCrazy Points: 2923 More actions · Answer 4

Thanks for the reply.But none of the packages are using sa login for connection. The SQL Agent is set with sa login and the same is modified with the new password.

Also one more thing that we came accross in discussions within the team is, owner of the job should be the same as the connection login used for SQL Agent. Any comments on this?

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 5

I have typically had jobs owned by sa, but not used sa for the Agent connection. That shouldn't be needed.

I don't know if the owner needs to be the same, but the owner credentials are checked, if I remember, in SQL 2000.