Failed Login Attempts from MSSQL, assistance tracing root cause

Question

Short background, we audit our Active Directory for any failed user logon attempts, and one of our devs changed his password recently. This change is triggering thousands of attempts against his account, and they're coming from our main SQL servers. The SQL Agent process in the event details is running with Log On As set to the domain administrator account. It appears that SQL is trying to login to the dev's account using the SQL Agent, so how can I check in SQL for such a setting? These are the event details for the attempts on one of the SQL servers:

Log Name:

Security Source: Microsoft-Windows-Security-Auditing

Date: 1/2/2022 10:03:59 PM

Event ID: 4625

Task Category:

Logon Level:

Information Keywords:

Audit Failure User: N/A

Computer: <FQDN redacted>

Description: An account failed to log on.

Subject:

Security ID: <Domain redacted>\Administrator

Account Name: Administrator

Account Domain: <redacted>

Logon ID: 0x111A9F

Logon Type: 8

Account For Which Logon Failed:

Security ID: NULL SID

Account Name: <username redacted>

Account Domain: <same domain>

Failure Information:

Failure Reason: Unknown user name or bad password.

Status: 0xC000006D

Sub Status: 0xC000006A

Process Information:

Caller Process ID: 0x2910

Caller Process Name: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE

Network Information:

Workstation Name: <name redacted>

Source Network Address: -

Source Port: -

Detailed Authentication Information:

Logon Process: Advapi

Authentication Package: Negotiate

Transited Services: -

Package Name (NTLM only): -

Key Length: 0

This event is generated when a logon request fails. It is generated on the computer where access was attempted. The Subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe. The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network). The Process Information fields indicate which account and process on the system requested the logon. The Network Information fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases. The authentication information fields provide detailed information about this specific logon request.

• Transited services indicate which intermediate services have participated in this logon request.
• Package name indicates which sub-protocol was used among the NTLM protocols.
• Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

Event Xml: http://schemas.microsoft.com/win/2004/08/events/event"> 4625 0 0 12544 0 0x8010000000000000 75641580 Security bonzi.ntjohnson.com S-1-5-21-1368744804-1704687866-142223018-500 Administrator <domain> 0x111a9f S-1-0-0 <username> <domain> 0xc000006d %%2313 0xc000006a 8 Advapi Negotiate <workstation name> - - 0 0x2910 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE - -

Answer 1

I agree with Tom, having SQL Server Agent running under a domain admin account sounds like a very bad idea. However, that may be the reason for the login failures.

It seems that somewhere there is a job with this dev's username and password - one more exquisitely bad idea! That could be a CmdExec job or similar that has a RunAS.

It could also be something that uses a proxy. In the case a look in sys.credentials may reveal something.

Answer 2

You should not ever run a SQL Server service under a domain Administrator account. This should be changed immediately.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15

Answer 3

The dev found the cause of the logins. His credentials needed to be updated in PowerBI.