We've always used MSX\TSX to distribute maint plans across our fleet of over 100 SQL servers, firstly with SQL 2000 then SQL 2005. For some reason, I cannot get the maint plans to work on SQL 2008, even though I'm following the same process I use for 2005.
Firstly, the TSX enlists into the MSX successfully. No problems there. Secondly, I am able to add the maint plan to the TSX. A few seconds later, it appears in Maint Plans on the TSX server. No problems there.
But when I execute the maint plan, either manually or through scheduled time, it always fails. The error in SQL logs is:
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ]
There's also a great long error message in Job History, the most important part is:
Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
We have about 15 servers now running SQL 2008 and this occurs on all of them. I have set up a test environment with 2 2008 servers, and it occurs on both these as well. The maint plan does check database integrity, backup database full, and maintenance cleanup task, all in a single step. It's exactly the same as the one we use for SQL 2005 except for the path that it backs up to.
I have tried using multiple different MSX servers, all result in the same error. I even enlisted the 2008 TSX's into our production 2005 MSX, created the maint plan there, and it still fails with same result.
On my test server, whenever I execute the maint plan, it now locks out the sa account. I have to log in with windows account, unlock sa account and reset the password for it.
I've been trying to resolve this issue for months now. I've set up local maint plans on all the servers as a workaround, but we are aquiring more SQL 2008 servers and this is becoming difficult to manage. It's like something is different with the sa account on our 2008 servers, but I don't know what.
Any help is greatly appreciated.
