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

  • Have you set a password for the 'sa' account? There is no usable password unless it is set for the 'sa' account. Under some circumstances the SQL Server will generate a 'password' but it is not revealed to anyone.. The 30 failed login attempts is probably related to the default timeout of 30 seconds. – RLF Mar 17 '17 at 19:14
  • 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 '17 at 19:26
  • Run a trace to figure it out. We can only do guess work here. – Kin Shah Mar 17 '17 at 19:47
  • You might also double-check the maintenance plan, and see if it includes any steps beyond the t-log backup itself. – RDFozz Mar 17 '17 at 20:03
  • I ended up creating another admin user to specifically run the maintenance jobs. I changed the connection properties in the maintenance plan to connect as the new user. Then I went through each job and changed the owner also. It works for the transaction log backups. I am going to double check the logs tomorrow after some of the other maintenance jobs run tonight to see if that fixes my issue. – Jason Mar 17 '17 at 21:28
3

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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