SQL Server Agent Failed ‘sa’ Login – Security Issue in SQL Server 2016

maintenanceSecuritysql-server-2016sql-server-agent

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

Best Answer

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.

Related Solutions

Sql-server – Make SQL Server Agent retry failed script with Try/Catch

Did you use RAISERROR with severity 16?
This is "user defined error" and should be picked up by SQL Server agent

Did you set the job step to retry (Retry Interval and Retry Attempts)

Finally, is the error terminating (severity 20) such that the CATCH block isn't hit?

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2, while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.

Best Answer

Related Solutions

Sql-server – Make SQL Server Agent retry failed script with Try/Catch

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

Related Question