John contacted me and told that he is not able to save the maintenance plan after Selecting All Databases while create a backup plan using maintenance plan wizard. Here was the error:
The SaveToSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user ‘sa’.). The SQL statement that was issued has failed.
I searched for SaveToSQLServer but found nothing interesting. Then I looked at the second part of the error and we can see “Login failed for user ‘sa’”. When I asked about it, John informed me that it works after 10 minutes and we should use windows authentication. Interestingly, when we used windows authentication, it was working fine.
I took a step back and told John to explain this behavior and he was not aware of the cause of this auto login failure.
We checked ERRORLOG and found that login for ‘sa’ was failing because it was getting locked out. We looked into login properties and found that the ‘sa’ login has enforce password policy enabled. Now next task was to find out why the account was getting locked.
We used profiler and found that there was another maintenance plan which was connected with an old ‘sa’ password and causing login failures. Due to password policy, it was getting locked out. The domain was set to unlock password after 10 minutes, which explained why it was working for some time.
Reference: Pinal Dave (https://blog.sqlauthority.com)