3

I recently upgraded to SQL Server 2016. I was looking through the SQL Server logs and noticed the following error.

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ]

I have figured out that the running a transaction log backup triggers the error. The weird thing is that the maintenance job is successful. I can't figure out how the job can be successful with the failed login attempts. Another thing is that every time the job is ran, it logs exactly 30 failed attempts. What is failing to log in during the execution of the transaction log backup and how do you fix it?

Any ideas or solutions would be appreciated. Thanks

Improve this question
1
  • Yes. The 'sa' password has been set. That was set during installation and plus I have logged on the instance with 'sa' account.
    – Jason
    Mar 17, 2017 at 19:26

2 Answers 2

Reset to default
2

After a few days of research, it looks like this issue is caused by a bug in SQL Server 2016. In the following thread that describes this issue, other DBAs are experiencing the same thing. One person troubleshooted the issue with a Microsoft support engineer who was confident it was a bug. Maintenance plan - Login failed for user 'sa' on master database

BUG: Use SQL authentication when creating a maintenance plan through the maintenance plan wizard. Run the maintenance plan. It will succeed, but after it succeeds, it will log a number of failed logins.

WORKAROUND: Use Windows authentication instead of SQL authentication. If you have already created the maintenance plan, you will need to right click on it and select modify. Look for "manage connections" at the top window. You will be able to modify the local connection here. You will need to be logged on your machine with that domain account and that domain account will need the proper SQL privileges.

Improve this answer
1

I was having the same problem with a backup task, I disabled the option to "Enforce password policy" for the sa account and it started working Using SQL 2016

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.