Agent Job fails on "sa" account login

Question

Agent Job fails on "sa" account login

hemantkchitale

Old Hand

Points: 369
More actions
December 4, 2017 at 11:46 pm

#333884

I have a couple of SQL Server Agent jobs configured to run as "sa"
However, they fail with the error "Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'sa'.)."
Does SQL Server save a password with a job (e.g. if it is using an old password for the "sa" account) ? I can't find any setting in the Job Properties about the password for the account.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 1

Hi,
our job-owner is the SA account, but the account is disabled. So you need no active sa account, or the password of the sa account to run jobs.
Can you script some of the failed jobs, are there proxy or something like that, where the ower tries to leave the sql-server?
Kind regards,
Andreas

DimUser Ten Centuries Points: 1043 More actions · Answer 2

does the sa account still exist?
Does it have a password on the account? is password expiration enabled?

hemantkchitale Old Hand Points: 369 More actions · Answer 3

DimPerson - Tuesday, December 5, 2017 1:17 AM
does the sa account still exist?
Does it have a password on the account? is password expiration enabled?

Yes, the account exists and I can login to the account with a valid password.

However, as soon as I run the SQL Server Agent Job (or even as a Maintenance Plan), the account gets locked.

Lempster SSCoach Points: 15651 More actions · Answer 4

First off; what do the jobs do? Do they execute T-SQL queries, stored procedures, SSIS packages, PowerShell scripts or something else?
Regards
Lempster

Sue_H SSC Guru Points: 90860 More actions · Answer 5

The error 0x80131904 is not a pure login failure - it's usually associated more often with a connection failure.
What is in the SQL Server error log and Is this job running a Maintenance Plan?

Sue

hemantkchitale Old Hand Points: 369 More actions · Answer 6

andreas.kreuzberg - Tuesday, December 5, 2017 12:41 AM
Hi,
our job-owner is the SA account, but the account is disabled. So you need no active sa account, or the password of the sa account to run jobs.
Can you script some of the failed jobs, are there proxy or something like that, where the ower tries to leave the sql-server?
Kind regards,
Andreas

At least one of the failing jobs is a TSQL script.

UPDATE : I've found the error in the TSQL script and have fixed it. Thanks for the hint to look at Scripts versus Maintenance Plans separately.

hemantkchitale Old Hand Points: 369 More actions · Answer 7

Lempster - Tuesday, December 5, 2017 2:55 AM
First off; what do the jobs do? Do they execute T-SQL queries, stored procedures, SSIS packages, PowerShell scripts or something else?
Regards
Lempster

One of them is TSQL script to Backup Database. The TSQL script has been fixed, there was a syntax error.
The others are built from Maintenance Plans to Backup Database or Logs.
So, now, the issue is with jobs built from Maintenance Plans.

hemantkchitale Old Hand Points: 369 More actions · Answer 8

Sue_H - Tuesday, December 5, 2017 8:40 AM
The error 0x80131904 is not a pure login failure - it's usually associated more often with a connection failure.
What is in the SQL Server error log and Is this job running a Maintenance Plan?
Sue

Both Maintenance Plan and TSQL script are failing. UPDATE : The TSQL script had a syntax error and I have fixed that now.
So the errors below relate to Jobs built from Maintenance Plans.
The errors in the SQL Server log are
Login failed for user 'sa'. Reason : Password did not match that for the login provided [Client : <local machine>]
Error : 18456. Severity 14. State 8

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 9

Hi,
please check your connection settings in the maintenance plan.
Maybe you try to connect to a "foreign" sql server with your sa account.
Kind regards,
Andreas

garry.lovesey SSC Veteran Points: 289 More actions · Answer 10

I believe that the maintenance plans will actually run under the service account for the SQL Server Agent.

This account must have read/write access to the drive where the backups are being put.

Sue_H SSC Guru Points: 90860 More actions · Answer 11

I think this is in issue with the connection settings for the maintenance plans as Andreas has posted.
In the maintenance plans, go up to Manage Connections, try setting it to Local server connection and Use Windows NT Authentication.

Sue

hemantkchitale Old Hand Points: 369 More actions · Answer 12

thanks everyone.
It does look like the Maintenance Plan based job has an issue with connection settings.

In the meantime, I have re-written everything to run as T-SQL jobs. These run fine.

Hemant

Lempster SSCoach Points: 15651 More actions · Answer 13

Well done for solving it. I stopped using Maintenance Plans long time ago; it's relatively simple to 'roll your own' or use something like Ola Hallengren's maintenance scripts.

Regards
Lempster