In our server fleet we use a mixture of SQL 2005 and SQL 2008. All our SQL 2005 servers have always used maintenance plans from an MSX server and they run fine. We are now trying to set up a SQL 2008 MSX for the SQL 2008 servers to connect to.
Rather than set up a new server for this, we installed an additional SQL 2008 instance on the same server that has the 2005 MSX instance and set it up as an MSX.
I can join the 2008 servers to the 2008 MSX instance with no problems. I have created maintenance plans on the 2008 MSX and pushed them to the 2008 servers. However, when I try to execute any maintenance plan, I get:
Job 'xxxxx Primary Backup.Subplan_1 (Multi-Server)' failed. (SqlManagerUI)
I checked the SQL Server Logs and I have the following:
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: local machine] Error: 18456, Severity: 14, State: 8.
State 8 means a password mismatch, but I don't see how this is possible. We use the same sa password for our entire server fleet. In fact, I don't even know where in the maintenance plan you would type the sa password. I've created numerous maintenance plans before both using the wizard and manually and have never had to enter the sa password. I'm also able to log into SSMS using the sa login with no problems. Just to double check, I have retyped the sa password on both MSX and TSX.
The MSX server instances are 32-bit, whilst the TSX is 64-bit. All SQL databases are installed using Mixed Mode. We use the same domain service account for all 'SQL Server' and 'SQL Server Agent' services.
Any help with this one would be greatly appreciated.