Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hvdtol
Yak Posting Veteran
50 Posts |
 Posted - 2007-02-07 : 06:04:14
|
| Hello,It is our yearly procedure to change the sa password.This time we do it for the first time in ss2005.After changing the sa password, all jobs fail with the following error:Message Login failed for user 'sa'. [CLIENT: <local machine>]Message Error: 18456, Severity: 14, State: 8.When i check the properties of the job i can see (in the general tab) that all jobs are running through Windows authentication.Any newly created jobs run successfull.What do i do wrong?Any help is appreciated.Best regards,Harry |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-02-07 : 09:32:40
|
| Somewhere along the line you have SQL authentication in place or being attempted. In windows authentication, you won't have "sa" coming through as a signon. Can you trace the event via Profiler and try to see which machine is at fault. Also when you say "all jobs fail"...do you mean all "user-facing code", all "SQL admin jobs" or all "jobs run off the schedule"? |
 |
|
|
hvdtol
Yak Posting Veteran
50 Posts |
Posted - 2007-02-07 : 12:32:53
|
| I mean all the jobs i have created thru maintenance plans, like backups etcIt is my suspicion that somewhere the sa password is kept in this job.Harry |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-07 : 14:28:45
|
| It is possible that you have the SQL Server Agent configured to connect using the SA password.In Enterprise Manager, check the Properties of the SQL Server Agent, and look at the Connection tab. If you do, you will have to enter the new password, and restart the Agent.CODO ERGO SUM |
|
|
|
hvdtol
Yak Posting Veteran
50 Posts |
Posted - 2007-02-07 : 16:10:36
|
| When i select connection after properties on the Agent, the options are grayed out. I can see the bullet before Windows authentication, so i suppose this is selected. On 2005 i think the Agent only runs on Windows authentication....... |
|
|
|
|
|
|
|
|
Jobs fail after sa password change