When dealing with persistent Active Directory account lockouts caused by SQL Server authentication attempts, we need to approach this systematically. The issue typically occurs when service accounts or user credentials are cached by applications trying to maintain database connections.

Start by examining these key sources:

# Check DC security logs (Event ID 4740 for lockouts)
Get-WinEvent -LogName Security -FilterXPath "*[System[EventID=4740]]" | 
Where-Object {$_.Properties[0].Value -eq "locked_user"} |
Format-List *

Use Microsoft's Account Lockout Status tool (ALockout.dll) to identify the originating device:

# PowerShell alternative to ALockout
$user = "DOMAIN\username"
$dc = "yourdomaincontroller"
Invoke-Command -ComputerName $dc -ScriptBlock {
    Get-ADUser $using:user -Properties LockedOut,LastBadPasswordAttempt,
    BadPwdCount | Select-Object Name,LockedOut,LastBadPasswordAttempt,BadPwdCount
}

For applications using Windows authentication with SQL Server, enable login auditing:

-- SQL Server audit configuration
USE [master]
GO
CREATE SERVER AUDIT SQL_Logins_Audit
TO FILE (FILEPATH = 'C:\Audits\')
GO
CREATE DATABASE AUDIT SPECIFICATION Database_Logins_Spec
FOR SERVER AUDIT SQL_Logins_Audit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
GO
ALTER SERVER AUDIT SQL_Logins_Audit WITH (STATE = ON)
GO

Frequent offenders include:

• Legacy apps with hardcoded credentials
• SQL-linked servers with expired passwords
• Scheduled tasks running under locked accounts
• Mobile devices with cached corporate credentials

Create a monitoring script to catch lockouts in real-time:

# PowerShell lockout monitor
$query = @"
<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
      *[System[EventID=4740]] 
      and 
      *[EventData[Data[@Name='TargetUserName']='target_user']]
    </Select>
  </Query>
</QueryList>
"@
Register-WinEvent -Query $query -Action {
    $event = $EventArgs.NewEvent
    Write-Host "Lockout detected at $($event.TimeCreated)"
    # Add notification logic here
}

Implement these best practices:

# Group Policy settings to adjust
Set-GPRegistryValue -Name "Default Domain Policy" -Key 
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" 
-ValueName "LockoutDuration" -Value 30 -Type DWord

Consider implementing account lockout exemptions for service accounts using Protected Users group or fine-grained password policies.

---

When dealing with Active Directory account lockouts caused by SQL Server authentication attempts, we need to approach this systematically. The most common pattern is:

User → Application → SQL Server → Domain Controller (failed authentication)

First, we need to collect lockout information from the Domain Controller's security logs:

# PowerShell to query security events
Get-WinEvent -LogName Security -FilterXPath 
'*[System[EventID=4740] and EventData[Data[@Name="TargetUserName"]="username"]]' |
Format-List

Key fields to examine:

• Caller Computer Name (source machine)
• Process Name (if available)
• Network Information (IP address)

When the lockout source is identified as a server running SQL applications:

-- SQL query to check active connections
SELECT 
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_end_time,
    c.client_net_address
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.login_name = 'DOMAIN\username'

Here are typical scenarios we encounter:

1. Stored Credentials in Applications

Check configuration files for hardcoded credentials:

// Example of problematic connection string
string connString = "Server=sqlserver;Database=db;User ID=DOMAIN\\user;Password=oldpass;";

Solution: Move to Windows Authentication or implement proper credential rotation.

2. SQL Agent Jobs with Expired Passwords

-- Check for SQL Agent jobs using the account
USE msdb
SELECT name, enabled, last_run_outcome
FROM sysjobs
WHERE owner_sid = SUSER_SID('DOMAIN\username')

3. Connection Pooling Issues

Applications might be holding stale connections in the pool. Add this to connection strings:

Pooling=false;  // Temporary test setting

For stubborn cases, enable detailed logging:

Domain Controller Audit Logging

# Group Policy setting to enable:
Audit Account Logon Events: Success, Failure
Audit Logon Events: Success, Failure

SQL Server Login Auditing

-- Enable failed login attempts logging
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'AuditLevel',
REG_DWORD,
2

Create a monitoring script to catch lockouts in real-time:

# PowerShell lockout monitor
$query = @"
<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
      *[System[(EventID=4740)]]
      and
      *[EventData[Data[@Name="TargetUserName"] and (Data="username")]]
    </Select>
  </Query>
</QueryList>
"@

Register-WinEvent -Query $query -Action {
    param($event)
    # Send alert or take action
    Write-Host "Lockout detected at $($event.TimeCreated)"
}

• Implement account lockout threshold policies
• Use Group Managed Service Accounts (gMSA) where possible
• Configure applications to use least-privilege accounts
• Regularly audit service accounts and their usage